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.一个一个单元格的进行插入

　　string str = @"Data Source=IT-428E4EA4B0C7SQLEXPRESS;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

　　{

　　//循环获取excel单元格的值一次一次的插入，excuteSql为执行的存储过程

　　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();

　　二快速导入导出

　　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倒入数据库

　　// block copy to DB from Excel

　　//By xijun,

　　//step create an excel file C:Inetpubwwwroot est.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("
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

　　//非空讀入數據

　　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-GPHNASQLEXPRESS;Initial Catalog=GDS_Service;Integrated Security=True";

　　using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))

　　{

　　bulkCopy.DestinationTableName = "GDS_Hub_data";

　　//加入只加入一個列的話,那么就會其他數據庫列都默認為空。

　　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 =;//每次传输的行数

　　//bcp.NotifyAfter =;//进度提示的行数

　　bulkCopy.BatchSize =;

　　bulkCopy.NotifyAfter =;

　　bulkCopy.WriteToServer((IDataReader)dr);

　　}

　　}

　　//Closing connection

　　excelConnection.Close();

　　}

　　DateTime t2 = DateTime.Now;

　　Response.Write("
End time:" + t2.ToString());

　　Response.Write("
use time:" + ((TimeSpan)(t2 - t1)).Milliseconds.ToString() + " Milliseconds");

　　Response.Write("
inser record count :");

　　2.快速导出db的数据到excel

　　这种方法就是利用

　　Excel.QueryTables

　　Excel.QueryTable

　　Querytable把数据快速导入excel里。我们在做复杂报表的时候，这个用的是比较多了，但是单单会这个没有用，它只是快速的把db里的数据放放到excel里，在做大量数据而且需要设定excel样式的时候我们会选择先用这种方法把数据导入excel一个临时sheet，再利sheet复制，sheet移动，和一些excel样式设定，以及excel一个强大的自动填充的功能，那么这些就可以让我们快速的做出花样多试的excel报表，当然这个要求我们比较熟练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-G87PHNASQLEXPRESS;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 = "@"; //设置单元格格式为文本

　　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和单元格操作：

　　///

　　/// 讀取excel數據和插入公式

　　///

　　///

　　///

　　protected void Button2_Click(object sender, EventArgs e)

　　{

　　DateTime t1 = DateTime.Now;

　　Response.Write("
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();

　　//進行宏的循環應用與單元格的刪除和添加，多個單元格默認宏自動操作。

　　Excel.Range range3 = excelkk.get_Range("B25","E25");

　　range2.AutoFill(range3,Excel.XlAutoFillType.xlFillDefault);

　　//刪除表的指定行數操作

　　Excel.Range range7 = null;

　　range7 = excelkk.get_Range(excelkk.Cells[2, 2], excelkk.Cells[4, 4]);

　　range7.Select();

　　range7.EntireRow.Delete(Excel.XlDirection.xlUp);

　　//獲取最大用過的行數

　　Excel.Worksheet wsheet1 = (Excel.Worksheet)excelkk.Worksheets.get_Item(1);

　　int n =wsheet1.UsedRange.Cells.Columns.Count;

　　Response.Write(n.ToString() + "
");

　　//MessageBox.Show(n.ToString());

　　n = wsheet1.UsedRange.Cells.Rows.Count;

　　Response.Write(n.ToString() + "
");

　　//MessageBox.Show(n.ToString());

　　//數據的複製

　　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();

　　}

　　單個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();

　　虽然我们拥有强大的NPOI，不过我还是写出来，希望对大家理解office有一定的用处。

　　以上所述是小编给大家介绍的C#多种操作excel的方法比较，希望对大家有所帮助。在此也非常感谢大家对脚本之家网站的支持！

　　您可能感兴趣的文章: