C#操作excel.doc

C#操作excel(多种方法比较) 我们在做excel资料的时候，通常有以下方法。一.导入导出excel常用方法：1.用查询表的方式查询并show在数据集控件上。代码 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.一个一个单元格的进行插入代码 1 string str = Data Source=IT-428E4EA4B0C7SQLEXPRESS;Initial Catalog=TestBase;Integrated Security=True; 2 SqlConnection conn = new SqlConnection(str); 3 conn.Open(); 4 int n = 0; 5 for (int i = 1; i 3) 8 break; 9 else10 if (msheet.Cells.get_Range(A + i, Type.Missing).Text.ToString() = & n = 3)11 n+; 12 else13 14 /循环获取excel单元格的值一次一次的插入，excuteSql为执行的存储过程15 excuteSql(msheet.Cells.get_Range(B + i, Type.Missing).Text.ToString(),16 msheet.Cells.get_Range(B + (i + 1), Type.Missing).Text.ToString(),17 msheet.Cells.get_Range(B + (i + 2), Type.Missing).Text.ToString(),18 conn);19 i = i + 3;20 21 22 23 24 conn.Close();二快速导入导出1.我们都知道当向db里批量插入数据的时候我们会选择SqlBulkCopyif(dataTable!=null&dataTable.Rows.Count!=0)sqlBulkCopy.WriteToServer(dataTable);这个可以看深山老林新发的一篇SQLServer中批量插入数据方式的性能对比下面是SqlBulkCopy的方法，这个方法有一个弊端就是当excel某一列即有文字，还有日期的时候，会出现null值，我在网上查了一些资料说连接字串加上;HDR=YES;IMEX=1的时候会都当做字符处理，但是还是会出现一些bug，所以建议最好先把excel数据分析到datatable里然后再用SqlBulkCopy倒入数据库代码 1 / block copy to DB from Excel 2 /By xijun, 3 /step 1 create an excel file C:Inetpubwwwroottest.xls , fill cell(1,1) with Data,cell(1,2) with name 4 /step 2 create table named Data with 2 column (data,name) in your DB 5 /there the code below: 6 DateTime t1 = DateTime.Now; 7 Response.Write(start time: + t1.ToString(); 8 string ExcelFile = C:20090916_Hub_Report.xls; 9 string excelConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + ExcelFile + ;Extended Properties=Excel 8.0;HDR=YES;IMEX=1;10 11 using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString)12 13 14 excelConnection.Open();15 /Getting source data16 /非空讀入數據17 OleDbCommand command = new OleDbCommand(Select Region,CustomerPN,RMA,Date,QTY,Return/Pull FROM 20090916_Hub_Report$ , excelConnection);18 / Initialize SqlBulkCopy object19 20 using (OleDbDataReader dr = command.ExecuteReader()21 22 / Copy data to destination23 string sqlConnectionString = Data Source=MININT-G87PHNASQLEXPRESS;Initial Catalog=GDS_Service;Integrated Security=True;24 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString)25 26 bulkCopy.DestinationTableName = GDS_Hub_data;27 /加入只加入一個列的話,那么就會其他數據庫列都默認為空。28 bulkCopy.ColumnMappings.Add(Region, region);29 bulkCopy.ColumnMappings.Add(CustomerPN, customer_item_number);30 bulkCopy.ColumnMappings.Add(RMA, Rma);31 bulkCopy.ColumnMappings.Add(Date, date);32 bulkCopy.ColumnMappings.Add(QTY, Qty_1);33 bulkCopy.ColumnMappings.Add(Return/Pull, return_pull);34 /bcp.BatchSize = 100;/每次传输的行数35 /bcp.NotifyAfter = 100;/进度提示的行数36 bulkCopy.BatchSize = 100;37 bulkCopy.NotifyAfter = 100;38 bulkCopy.WriteToServer(IDataReader)dr);39 40 41 42 43 /Closing connection44 excelConnection.Close();45 46 47 DateTime t2 = DateTime.Now;48 Response.Write(End time: + t2.ToString();49 Response.Write(use time: + (TimeSpan)(t2 - t1).Milliseconds.ToString() + Milliseconds);50 Response.Write(inser record count :3307);2.快速导出db的数据到excel这种方法就是利用Excel.QueryTablesExcel.QueryTable Querytable把数据快速导入excel里。我们在做复杂报表的时候，这个用的是比较多了，但是单单会这个没有用，它只是快速的把db里的数据放放到excel里，在做大量数据而且需要设定excel样式的时候我们会选择先用这种方法把数据导入excel一个临时sheet，再利sheet复制，sheet移动，和一些excel样式设定，以及excel一个强大的自动填充的功能，那么这些就可以让我们快速的做出花样多试的excel报表，当然这个要求我们比较熟练office的操作，包括宏的操作。代码 1 public string query_table_getdata(string sourpath) 2 3 string str_path = sourpath.Substring(0, sourpath.Length - 22); 4 str_path = str_path + basic.xls; 5 6 Excel.QueryTables m_objQryTables = null; 7 Excel.QueryTable m_objQryTable = null; 8 Excel.Application m_objExcel = null; 9 Excel.Workbooks m_objBooks = null;10 Excel.Workbook m_objBook = null;11 Excel.Sheets m_objSheets = null;12 Excel.Worksheet m_objSheet = null;13 Excel.Range m_objRange = null;14 m_objExcel = new Excel.Application();15 16 /try17 /18 m_objBooks = m_objExcel.Workbooks;19 m_objBooks.Open(sourpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,20 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);21 22 m_objBook = (Excel.Workbook)m_objBooks.get_Item(1);23 24 25 m_objSheets = (Excel.Sheets)m_objBook.Worksheets;26 m_objSheet = (Excel.Worksheet)m_objSheets.get_Item(1);27 m_objRange = m_objSheet.get_Range(A2, Type.Missing);28 m_objQryTables = m_objSheet.QueryTables;29 string sqlstr = SELECT day01,day02,day03,day04,day05,day06,day07,day08,day09,day10,day11,day12,day13,day14;30 sqlstr += ,week01 ,week02,week03,week04,week05,week06,week07,week08,week09,week10,week11,week12,week13,week14;31 sqlstr += ,week15,week16,week17,week18,week19,week20,week21,week22,week23,week24;32 sqlstr += FROM GDS_Service.dbo.GDS_Service_Report_Base order by groupID ,id;33 /可以放在配置文件里34 string conn = Provider=SQLOLEDB.1;Data Source=MININT-G87PHNASQLEXPRESS;uid=xijun_ke;Password=12345678; Initial Catalog=GDS_Service;Persist Security Info=False;35 36 m_objQryTable = (Excel.QueryTable)m_objQryTables.Add(OLEDB; + conn, m_objRange, sqlstr);37 38 m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;39 40 m_objQryTable.Refresh(false);41 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);42 m_objBook.Close(false, Type.Missing, Type.Missing);43 /44 /catch (Exception ee)45 /46 / mp.WriteLog(ee.ToString();47 /48 /finally49 /50 m_objExcel.Quit();51 GC.Collect();52 /53 return str_path;54 55 com操作excel的一些特性操作：range.NumberFormatLocal=;/设置单元格格式为文本 range=(Range)worksheet.get_Range(A1,E1);/获取Excel多个单元格区域：本例做为Excel表头range.Merge(0);/单元格合并动作worksheet.Cells1,1=Excel单元格赋值;/Excel单元格赋值range.Font.Size=15;/设置字体大小range.Font.Underline=true;/设置字体是否有下划线range.Font.Name=黑体;设置字体的种类range.HorizontalAlignment=XlHAlign.xlHAlignCenter;/设置字体在单元格内的对其方式range.ColumnWidth=15;/设置单元格的宽度range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();/设置单元格的背景色range.Borders.LineStyle=1;/设置单元格边框的粗细range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb();/给单元格加边框range.EntireColumn.AutoFit();/自动调整列宽Range.HorizontalAlignment=xlCenter;/文本水平居中方式Range.VerticalAlignment=xlCenter/文本垂直居中方式Range.WrapText=true;/文本自动换行Range.Interior.ColorIndex=39;/填充颜色为淡紫色Range.Font.Color=clBlue;/字体颜色xlsApp.DisplayAlerts=false;/保存Excel的时候，不弹出是否保存的窗口直接进行保存 workbook.SaveCopyAs(temp);/*/填入完信息之后另存到路径及文件名字excel宏操作，sheet和单元格操作：代码 1 / 2 / 讀取excel數據和插入公式 3 / 4 / 5 / 6 protected void Button2_Click(object sender, EventArgs e) 7 8 DateTime t1 = DateTime.Now; 9 Response.Write(start time: + t1.ToString();10 Excel.Application excelkk = new Excel.Application();11 12 excelkk.Workbooks.Add(true);13 int row = 2;14 DataTable myTable = ds.TablesSheet1$;15 for (int i = 0; i myTable.Columns.Count; i+)16 17 excelkk.Cells1, 1 + i = myTable.Columnsi.ColumnName.ToString();18 19 for (int i = 0; i myTable.Rows.Count; i+)20 21 for (int j = 0; j myTable.Columns.Count; j+)22 23 excelkk.Cellsrow, j + 1 = myTable.Rowsij.ToString();24 25 26 row+;27 28 29 30 /取得特定單元格的值31 excelkk.Visible = true;32 this.TextBoxChange.Text = excelkk.get_Range(A2, Type.Missing).Text.ToString();33 /表的單元格合并34 Excel.Range range1 = excelkk.get_Range(A2, D4);35 range1.Merge(Type.Missing);36 37 38 39 40 /想表格中插入求和的值41 Excel.Range range2 = excelkk.get_Range(B25, Type.Missing);42 range2.Formula = =SUM(B2:B24);43 range2.Calculate();44 45 /進行宏的循環應用與單元格的刪除和添加，多個單元格默認宏自動操作。46 Excel.Range range3 = excelkk.get_Range(B25,E25);47 range2.AutoFill(range3,Excel.XlAutoFillType.xlFillDefault);48 /刪除表的指定行數操作49 Excel.Range range7 = null;50 range7 = excelkk.get_Range(excelkk.Cells2, 2, excelkk.Cells4, 4);51 range7.Select();52 range7.EntireRow.Delete(Excel.XlDirection.xlUp);53 54 /獲取最大用過的行數55 Excel.Worksheet wsheet1 = (Excel.Worksheet)excelkk.Worksheets.get_Item(1);56 int n =wsheet1.UsedRange.Cells.Columns.Count;57 Response.Write(n.ToString() + );58 /MessageBox.Show(n.ToString();59 n = wsheet1.UsedRange.Cells.Rows.Count;60 Response.Write(n.ToString() + );61 /MessageBox.Show(n.ToString();62 /數據的複製63 Excel.Range range4 = excelkk.get_Range(A2, B25);64 Excel.Range range5 = excelkk.get_Range(E3, F25);65 /range4.get_Offset(1,4).Select();66 range4.Copy(range5);67 /停用警告信息68 excelkk.DisplayAlerts = false;69 GC.Collect(); 70 71 72 73 74 單個sheet里求和：75 Excel.Range range2 = excelkk.get_Range(B25, Type.Missing);76 range2.Formula = =SUM(B2:B24);77 range2.Calculate();78 79 跨sheet求和：80 Excel.Worksheet wsheet1 = (Excel.Worksheet)excelSql.Worksheets.get_Item(1);81 Excel.Range range3 =wsheet1.get_Range(A23, Type.Missing);82 range3.Formula = =Sheet3!B8+Sheet3!B12;83 range3.Calculate();84 虽然我们拥有强大的NPOI，不过我还是写出来，希望对大家理解office有一定的用处。有错误的地方欢迎大家拍砖,希望交流和共享。分类: C#操作excel绿色通道： 好文要顶 关注我 收藏该文与我联系 MR_ke关注 - 8粉丝 - 98 +加关注 13 0 (请您对文章做出评价) 博主前一篇：SQL进阶提升(xml合并拆分详细实例) 博主后一篇：C#操作excel(chart)posted on 2010-03-02 09:56 MR_ke 阅读(46863) 评论(42) 编辑 收藏 评论#1楼 2010-03-02 10:19xoyozo 留名收藏，EXCEL的导入导出绝对会遇到。另C#网站开发QQ群：105325933支持(0)反对(0)#2楼 2010-03-02 10:25Jack Fan 支持楼主！支持(0)反对(0)#3楼楼主 2010-03-02 10:49MR_ke Jack Fan谢谢。支持(0)反对(0)#4楼楼主 2010-03-02 10:50MR_ke xoyozo谢谢。支持(0)反对(0)#5楼 2010-03-02 11:32zantesu 用查询表的方式查询并show在数据集控件上这个方法中string sql = select * from Sheet1$;如果表的名字不是Sheet1怎么办呢支持(0)反对(0)#6楼楼主 2010-03-02 11:50MR_ke zantesu把sheet1改成你本来的表名，如果不知道但是知道那个sheet是第几个可以通过 Excel.Worksheet wsheet1 = (Excel.Worksheet)excelSql.Worksheets.get_Item(1);wsheet1.Name获取sheet名称支持(0)反对(0)#7楼 2010-03-02 14:03Console.C# 有没有 SQL Access直接快速数据导出的内容?支持(0)反对(0)#8楼 2010-03-02 14:20晃来晃去 、mvc、ajax、jquery、javascript技术交流上QQ群33989004支持(0)反对(0)#9楼楼主 2010-03-02 14:29MR_ke Console.C#这个没有，为什么要acces和sql互导啊？有sql的db之间互相导入导出。不过可以看看这个地址，这些可以问问百度和Google老师/article/47/47316.shtm支持(0)反对(0)#10楼 2010-03-02 15:08Jack Niu 还有一种是基于OpenXML的实现，比较喜欢这个-Jack支持(0)反对(0)#11楼楼主 2010-03-02 16:51MR_ke Jack Niu谢谢指教。支持(0)反对(0)#12楼 2010-03-02 17:25兽王俾斯麦 最末尾调用GC执行垃圾回收我觉得不必要，这样会打乱CLR自身的垃圾回收进程。支持(0)反对(0)#13楼 2010-03-02 17:32木乃伊 不错。excel操作常遇到。支持(0)反对(0)#14楼楼主 2010-03-02 18:49MR_ke 兽王俾斯麦gc唤醒操作，但是它不影响CLR自身的垃圾回收进程，gc本来就是堆栈满时候进行回收。其实这里也是没有必要用，但是excel的com对象申明太多，所以手动回收了而已。支持(0)反对(0)#15楼楼主 2010-03-02 18:49MR_ke 兽王俾斯麦欢迎指教。支持(0)反对(0)#16楼 2010-03-21 04:56zantesu 引用MR_ke：zantesu把sheet1改成你本来的表名，如果不知道但是知道那个sheet是第几个可以通过 Excel.Worksheet wsheet1 = (Excel.W