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-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
                 {
  //循环获取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:\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
             //非空讀入數據
             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";
                     //加入只加入一個列的話,那么就會其他數據庫列都默認為空。
                     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("<br>end time:" + t2.tostring());
         response.write("<br>use time:" + ((timespan)(t2 - t1)).milliseconds.tostring() + " milliseconds");
         response.write("<br>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-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 = "@";     //设置单元格格式为文本 
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和单元格操作：

     /// <summary>
     /// 讀取excel數據和插入公式
     /// </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();
 
         //進行宏的循環應用與單元格的刪除和添加，多個單元格默認宏自動操作。
         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() + "<br>");
         //messagebox.show(n.tostring());
         n = wsheet1.usedrange.cells.rows.count;
         response.write(n.tostring() + "<br>");
         //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的方法比较，希望对大家有所帮助。在此也非常感谢大家对网站的支持！