C#多種操作excel的方法比較
更新時(shí)間：2021年12月19日 10:58:36 作者：MR_ke
本文詳細(xì)講解了C#多種操作excel的方法，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下
我們?cè)谧鰁xcel資料的時(shí)候，通常有以下方法。
一.導(dǎo)入導(dǎo)出excel常用方法：
1.用查詢表的方式查詢并show在數(shù)據(jù)集控件上。
public static string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =C:\\08.xls;Extended Properties=Excel 8.0";
public static DataSet ds;
protected void Page_Load(object sender, EventArgs e)
{
OleDbConnection conn = new OleDbConnection(strCon);
string sql = "select * from [Sheet1$]";
conn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(sql, strCon);
ds = new DataSet();
myCommand.Fill(ds, "[Sheet1$]");
conn.Close();
datagrid1.DataMember = "[Sheet1$]";
datagrid1.DataSource = ds;
datagrid1.DataBind();
//Excel.Application excel = new Excel.Application();
//excel.Application.Workbooks.Add(true);
//excel.Visible = true;
}
2.一個(gè)一個(gè)單元格的進(jìn)行插入
string str = @"Data Source=IT-428E4EA4B0C7\SQLEXPRESS;Initial Catalog=TestBase;Integrated Security=True";
SqlConnection conn = new SqlConnection(str);
conn.Open();
int n = 0;
for (int i = 1; i < 20; i++)
{
if (n > 3)
break;
else
if (msheet.Cells.get_Range("A" + i, Type.Missing).Text.ToString() == "" && n <= 3)
{ n++; }
else
{
//循環(huán)獲取excel單元格的值一次一次的插入，excuteSql為執(zhí)行的存儲(chǔ)過程
excuteSql(msheet.Cells.get_Range("B" + i, Type.Missing).Text.ToString(),
msheet.Cells.get_Range("B" + (i + 1), Type.Missing).Text.ToString(),
msheet.Cells.get_Range("B" + (i + 2), Type.Missing).Text.ToString(),
conn);
i = i + 3;
}
}
conn.Close();
二快速導(dǎo)入導(dǎo)出
1.我們都知道當(dāng)向db里批量插入數(shù)據(jù)的時(shí)候我們會(huì)選擇SqlBulkCopy
if (dataTable!=null && dataTable.Rows.Count!=0)
{
sqlBulkCopy.WriteToServer(dataTable);
}
這個(gè)可以看 深山老林新發(fā)的一篇SQLServer中批量插入數(shù)據(jù)方式的性能對(duì)比
下面是SqlBulkCopy的方法，這個(gè)方法有一個(gè)弊端就是當(dāng)excel某一列即有文字，還有日期的時(shí)候，會(huì)出現(xiàn)null值，我在網(wǎng)上查了一些資料說連接字串加上;HDR=YES;IMEX=1'的時(shí)候會(huì)都當(dāng)做字符處理，但是還是會(huì)出現(xiàn)一些bug，所以建議最好先把excel數(shù)據(jù)分析到datatable里然后再用SqlBulkCopy倒入數(shù)據(jù)庫
// block copy to DB from Excel
//By xijun,
//step create an excel file C:\Inetpub\wwwroot\test.xls , fill cell(1,1) with "Data",cell(1,2) with "name"
//step create table named "Data" with column ("data","name") in your DB
//there the code below:
DateTime t1 = DateTime.Now;
Response.Write("<br>start time:" + t1.ToString());
string ExcelFile = @"C:\\_Hub_Report.xls";
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFile + ";Extended Properties='Excel.0;HDR=YES;IMEX=1'";
using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
{
excelConnection.Open();
//Getting source data
//非空讀入數(shù)據(jù)
OleDbCommand command = new OleDbCommand("Select [Region],[CustomerPN],[RMA],[Date],[QTY],[Return/Pull] FROM [_Hub_Report$] ", excelConnection);
// Initialize SqlBulkCopy object
using (OleDbDataReader dr = command.ExecuteReader())
{
// Copy data to destination
string sqlConnectionString = @"Data Source=MININT-GPHNA\SQLEXPRESS;Initial Catalog=GDS_Service;Integrated Security=True";
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "GDS_Hub_data";
//加入只加入一個(gè)列的話,那么就會(huì)其他數(shù)據(jù)庫列都默認(rèn)為空。
bulkCopy.ColumnMappings.Add("Region", "region");
bulkCopy.ColumnMappings.Add("CustomerPN", "customer_item_number");
bulkCopy.ColumnMappings.Add("RMA", "Rma");
bulkCopy.ColumnMappings.Add("Date", "date");
bulkCopy.ColumnMappings.Add("QTY", "Qty_1");
bulkCopy.ColumnMappings.Add("Return/Pull", "return_pull");
//bcp.BatchSize =;//每次傳輸?shù)男袛?shù)
//bcp.NotifyAfter =;//進(jìn)度提示的行數(shù)
bulkCopy.BatchSize =;
bulkCopy.NotifyAfter =;
bulkCopy.WriteToServer((IDataReader)dr);
}
}
//Closing connection
excelConnection.Close();
}
DateTime t2 = DateTime.Now;
Response.Write("<br>End time:" + t2.ToString());
Response.Write("<br>use time:" + ((TimeSpan)(t2 - t1)).Milliseconds.ToString() + " Milliseconds");
Response.Write("<br>inser record count :");
?2.快速導(dǎo)出db的數(shù)據(jù)到excel
這種方法就是利用
Excel.QueryTables?
Excel.QueryTable
Querytable把數(shù)據(jù)快速導(dǎo)入excel里。我們?cè)谧鰪?fù)雜報(bào)表的時(shí)候，這個(gè)用的是比較多了，但是單單會(huì)這個(gè)沒有用，它只是快速的把db里的數(shù)據(jù)放放到excel里，在做大量數(shù)據(jù)而且需要設(shè)定excel樣式的時(shí)候我們會(huì)選擇先用這種方法把數(shù)據(jù)導(dǎo)入excel一個(gè)臨時(shí)sheet，再利sheet復(fù)制，sheet移動(dòng)，和一些excel樣式設(shè)定，以及excel一個(gè)強(qiáng)大的自動(dòng)填充的功能，那么這些就可以讓我們快速的做出花樣多試的excel報(bào)表，當(dāng)然這個(gè)要求我們比較熟練office的操作，包括宏的操作。
public string query_table_getdata(string sourpath)
{
string str_path = sourpath.Substring(0, sourpath.Length - 22);
str_path = str_path + "basic.xls";
Excel.QueryTables m_objQryTables = null;
Excel.QueryTable m_objQryTable = null;
Excel.Application m_objExcel = null;
Excel.Workbooks m_objBooks = null;
Excel.Workbook m_objBook = null;
Excel.Sheets m_objSheets = null;
Excel.Worksheet m_objSheet = null;
Excel.Range m_objRange = null;
m_objExcel = new Excel.Application();
//try
//{
m_objBooks = m_objExcel.Workbooks;
m_objBooks.Open(sourpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
m_objBook = (Excel.Workbook)m_objBooks.get_Item(1);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel.Worksheet)m_objSheets.get_Item(1);
m_objRange = m_objSheet.get_Range("A2", Type.Missing);
m_objQryTables = m_objSheet.QueryTables;
string sqlstr = "SELECT [day01],[day02],[day03],[day04],[day05],[day06],[day07],[day08],[day09],[day10],[day11],[day12],[day13],[day14]";
sqlstr += ",[week01] ,[week02],[week03],[week04],[week05],[week06],[week07],[week08],[week09],[week10],[week11],[week12],[week13],[week14]";
sqlstr += ",[week15],[week16],[week17],[week18],[week19],[week20],[week21],[week22],[week23],[week24]";
sqlstr += " FROM [GDS_Service].[dbo].[GDS_Service_Report_Base] order by groupID ,id";
//可以放在配置文件里
string conn = @"Provider=SQLOLEDB.1;Data Source=MININT-G87PHNA\SQLEXPRESS;uid=xijun_ke;Password=12345678;
Initial Catalog=GDS_Service;Persist Security Info=False;";
m_objQryTable = (Excel.QueryTable)m_objQryTables.Add("OLEDB;" + conn, m_objRange, sqlstr);
m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
m_objQryTable.Refresh(false);
m_objBook.SaveAs(str_path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
m_objBook.Close(false, Type.Missing, Type.Missing);
//}
//catch (Exception ee)
//{
// mp.WriteLog(ee.ToString());
//}
//finally
//{
m_objExcel.Quit();
GC.Collect();
//}
return str_path;
}
com操作excel的一些特性操作：
range.NumberFormatLocal = "@"; //設(shè)置單元格格式為文本
range = (Range)worksheet.get_Range("A1", "E1"); //獲取Excel多個(gè)單元格區(qū)域：本例做為Excel表頭
range.Merge(0); //單元格合并動(dòng)作
worksheet.Cells[1, 1] = "Excel單元格賦值"; //Excel單元格賦值
range.Font.Size = 15; //設(shè)置字體大小
range.Font.Underline=true; //設(shè)置字體是否有下劃線
range.Font.Name="黑體"; 設(shè)置字體的種類
range.HorizontalAlignment=XlHAlign.xlHAlignCenter; //設(shè)置字體在單元格內(nèi)的對(duì)其方式
range.ColumnWidth=15; //設(shè)置單元格的寬度
range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb(); //設(shè)置單元格的背景色
range.Borders.LineStyle=1; //設(shè)置單元格邊框的粗細(xì)
range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb()); //給單元格加邊框
range.EntireColumn.AutoFit(); //自動(dòng)調(diào)整列寬
Range.HorizontalAlignment= xlCenter; // 文本水平居中方式
Range.VerticalAlignment= xlCenter //文本垂直居中方式
Range.WrapText=true; //文本自動(dòng)換行
Range.Interior.ColorIndex=39; //填充顏色為淡紫色
Range.Font.Color=clBlue; //字體顏色
xlsApp.DisplayAlerts=false; //保存Excel的時(shí)候，不彈出是否保存的窗口直接進(jìn)行保存
workbook.SaveCopyAs(temp);/**////填入完信息之后另存到路徑及文件名字
?excel宏操作，sheet和單元格操作：
/// <summary>
/// 讀取excel數(shù)據(jù)和插入公式
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button2_Click(object sender, EventArgs e)
{
DateTime t1 = DateTime.Now;
Response.Write("<br>start time:" + t1.ToString());
Excel.Application excelkk = new Excel.Application();
excelkk.Workbooks.Add(true);
int row = 2;
DataTable myTable = ds.Tables["[Sheet1$]"];
for (int i = 0; i < myTable.Columns.Count; i++)
{
excelkk.Cells[1, 1 + i] = myTable.Columns[i].ColumnName.ToString();
}
for (int i = 0; i < myTable.Rows.Count; i++)
{
for (int j = 0; j < myTable.Columns.Count; j++)
{
excelkk.Cells[row, j + 1] = myTable.Rows[i][j].ToString();
}
row++;
}
//取得特定單元格的值
excelkk.Visible = true;
this.TextBoxChange.Text = excelkk.get_Range("A2", Type.Missing).Text.ToString();
//表的單元格合并
Excel.Range range1 = excelkk.get_Range("A2", "D4");
range1.Merge(Type.Missing);
//想表格中插入求和的值
Excel.Range range2 = excelkk.get_Range("B25", Type.Missing);
range2.Formula = "=SUM(B2:B24)";
range2.Calculate();
//進(jìn)行宏的循環(huán)應(yīng)用與單元格的刪除和添加，多個(gè)單元格默認(rèn)宏自動(dòng)操作。
Excel.Range range3 = excelkk.get_Range("B25","E25");
range2.AutoFill(range3,Excel.XlAutoFillType.xlFillDefault);
//刪除表的指定行數(shù)操作
Excel.Range range7 = null;
range7 = excelkk.get_Range(excelkk.Cells[2, 2], excelkk.Cells[4, 4]);
range7.Select();
range7.EntireRow.Delete(Excel.XlDirection.xlUp);
//獲取最大用過的行數(shù)
Excel.Worksheet wsheet1 = (Excel.Worksheet)excelkk.Worksheets.get_Item(1);
int n =wsheet1.UsedRange.Cells.Columns.Count;
Response.Write(n.ToString() + "<br>");
//MessageBox.Show(n.ToString());
n = wsheet1.UsedRange.Cells.Rows.Count;
Response.Write(n.ToString() + "<br>");
//MessageBox.Show(n.ToString());
//數(shù)據(jù)的複製
Excel.Range range4 = excelkk.get_Range("A2", "B25");
Excel.Range range5 = excelkk.get_Range("E3", "F25");
//range4.get_Offset(1,4).Select();
range4.Copy(range5);
//停用警告信息
excelkk.DisplayAlerts = false;
GC.Collect();
}
單個(gè)sheet里求和：
Excel.Range range2 = excelkk.get_Range("B25", Type.Missing);
range2.Formula = "=SUM(B2:B24)";
range2.Calculate();
跨sheet求和：
Excel.Worksheet wsheet1 = (Excel.Worksheet)excelSql.Worksheets.get_Item(1);
Excel.Range range3 =wsheet1.get_Range("A23", Type.Missing);
range3.Formula = "=Sheet3!B8+Sheet3!B12";
range3.Calculate();
雖然我們擁有強(qiáng)大的NPOI，不過我還是寫出來，希望對(duì)大家理解office有一定的用處。
以上所述是小編給大家介紹的C#多種操作excel的方法比較，希望對(duì)大家有所幫助。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！
相關(guān)文章
WinForm實(shí)現(xiàn)為TextBox設(shè)置水印文字功能
這篇文章主要介紹了WinForm實(shí)現(xiàn)為TextBox設(shè)置水印文字功能,很實(shí)用的一個(gè)技巧,需要的朋友可以參考下2014-08-08
C#實(shí)現(xiàn)子窗體與父窗體通信方法實(shí)例總結(jié)
這篇文章主要介紹了C#實(shí)現(xiàn)子窗體與父窗體通信方法,實(shí)例總結(jié)了常用的四種窗體通信方法,具有一定參考借鑒價(jià)值,需要的朋友可以參考下2015-09-09
這篇文章主要介紹了C#委托現(xiàn)實(shí),實(shí)例分析了C#委托的使用技巧,具有一定參考借鑒價(jià)值,需要的朋友可以參考下2015-04-04
本文詳細(xì)講解了C#多種操作excel的方法，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-12-12
這篇文章主要為大家詳細(xì)介紹了C#.NET 圖片水印添加代碼，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2016-07-07
C# IDE VS2005中的Hosting Process (vshost.exe)作用介紹
這篇文章主要介紹了C# IDE VS2005中的Hosting Process (vshost.exe)作用介紹,vshost.exe是一個(gè)宿主進(jìn)程,主要用來提高調(diào)試效率,需要的朋友可以參考下2015-01-01
C#使用Shader實(shí)現(xiàn)夜幕降臨倒計(jì)時(shí)的效果
這篇文章主要介紹了C#使用Shader實(shí)現(xiàn)夜幕降臨倒計(jì)時(shí)的效果，非常不錯(cuò)具有參考借鑒價(jià)值，需要的朋友可以參考下2016-10-10
這篇文章主要介紹了C#實(shí)現(xiàn)獲取磁盤空間大小的方法,分別基于System.IO.DriveInfo.GetDrives方法與ManagementClass("Win32_LogicalDisk")來實(shí)現(xiàn)這一功能,是非常實(shí)用的技巧,需要的朋友可以參考下2014-12-12
Unity5.6大規(guī)模地形資源創(chuàng)建方法
這篇文章主要為大家詳細(xì)介紹了Unity5.6大規(guī)模地形資源創(chuàng)建方法，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-02-02
這篇文章主要介紹了C#獲取哈希加密生成隨機(jī)安全碼的類,涉及C#哈希加密及字符串操作的相關(guān)技巧,具有一定參考借鑒價(jià)值,需要的朋友可以參考下2015-03-03