我们在做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-428E4EA4B0C7\SQLEXPRESS;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 < 20 ; i ++ ) 6 { 7 if (n > 3 ) 8 break ; 9 else 10 if (msheet.Cells.get_Range( " A " + i, Type.Missing).Text.ToString() == "" && n <= 3 ) 11 { n ++ ; } 12 else 13 { 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里批量插入数据的时候我们会选择SqlBulkCopy
if (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:\Inetpub\wwwroot\test.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( " <br>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 data 16 // 非空讀入數據 17 OleDbCommand command = new OleDbCommand( " Select [Region],[CustomerPN],[RMA],[Date],[QTY],[Return/Pull] FROM [20090916_Hub_Report$] " , excelConnection); 18 // Initialize SqlBulkCopy object 19 20 using (OleDbDataReader dr = command.ExecuteReader()) 21 { 22 // Copy data to destination 23 string sqlConnectionString = @" Data Source=MININT-G87PHNA\SQLEXPRESS;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 connection 44 excelConnection.Close(); 45 } 46 47 DateTime t2 = DateTime.Now; 48 Response.Write( " <br>End time: " + t2.ToString()); 49 Response.Write( " <br>use time: " + ((TimeSpan)(t2 - t1)).Milliseconds.ToString() + " Milliseconds " ); 50 Response.Write( " <br>inser record count :3307 " );
2.快速导出db的数据到excel
这种方法就是利用
Excel.QueryTables
Excel.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 //try 17 //{ 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-G87PHNA\SQLEXPRESS;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 //finally 49 //{ 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.Cells[ 1 , 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 /// <summary> 2 /// 讀取excel數據和插入公式 3 /// </summary> 4 /// <param name="sender"></param> 5 /// <param name="e"></param> 6 protected void Button2_Click( object sender, EventArgs e) 7 { 8 DateTime t1 = DateTime.Now; 9 Response.Write( " <br>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.Tables[ " [Sheet1$] " ]; 15 for ( int i = 0 ; i < myTable.Columns.Count; i ++ ) 16 { 17 excelkk.Cells[ 1 , 1 + i] = myTable.Columns[i].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.Cells[row, j + 1 ] = myTable.Rows[i][j].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.Cells[ 2 , 2 ], excelkk.Cells[ 4 , 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() + " <br> " ); 58 // MessageBox.Show(n.ToString()); 59 n = wsheet1.UsedRange.Cells.Rows.Count; 60 Response.Write(n.ToString() + " <br> " ); 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
4867
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
