C#多种操作excel的方法比较

我们在做excel资料的时候，通常有以下方法。

一.导入导出excel常用方法：

1.用查询表的方式查询并show在数据集控件上。

public static string strcon = " provider = microsoft.jet.oledb.4.0 ; data source =c:\.xls;extended properties=excel 8.0";    public static datat ds;    protected void page_load(object nder, eventargs e)    {                oledbconnection conn = new oledbconnection(strcon);        string sql = "lect * from [sheet1$]";        conn.open();        oledbdataadapter mycommand = new oledbdataadapter(sql, strcon);        ds = new datat();        mycommand.fill(ds, "[sheet1$]");        conn.clo();        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=testba;integrated curity=true";         sqlconnection conn = new sqlconnection(str);         conn.open();         int n = 0;         for (int i = 1; i < 20; i++)         {             if (n > 3)                 break;             el                 if (msheet.cells.get_range("a" + i, type.missing).text.tostring() == "" && n <= 3)                 { n++; }                 el                 {  //循环获取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_ran藤野先生教案ge("b" + (i + 2), type.missing).text.tostring(),                         conn);                     i = i + 3;       元旦黑板报内容           }         }          conn.clo();

二快速导入导出

1.我们都知道当向db里批量插入数据的时候我们会选择sqlbulkcopy

if (datatable!=null && datatable.rows.count!=0)            {                sqlbulkcopy.writetorver(datatable);            } 

这个可以看 深山老林新发的一篇sqlrver中批量插入数据方式的性能对比

下面是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;         respon.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("lect [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_rvice;integrated curity=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_pu朗朗上口的名字ll");                     //bcp.batchsize =;//每次传输的行数                     //bcp.notifyafter =;//进度提示的行数                     bulkcopy.batchsize =;                     bulkcopy.notifyafter =;                     bulkcopy.writetorver((idatareader)dr);                                       }             }             //closing connection             excelconnection.clo();         }          datetime t2 = datetime.now;         respon.write("<br>end time:" + t2.tostring());         respon.write("<br>u time:" + ((timespan)(t2 - t1)).milliconds.tostring() + " milliconds");         respon.write("<br>inr 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 = "lect [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_rvice].[dbo].[gds_rvice_report_ba] order by groupid ,id";                              //可以放在配置文件里                 string conn = @"provider=sqloledb.1;data source=minint-g87phna\sqlexpress;uid=xijun_ke;password=12345678;                   initial catalog=gds_rvice;persist curity info=fal;";                  m_objqrytable = (excel.querytable)m_objqrytables.add("oledb;" + conn, m_objrange, sqlstr);                  m_objqrytable.refreshstyle = excel.xlcellinrtionmode.xlinrtentirerows;                  m_objqrytable.refresh(fal);                 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.clo(fal, 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=fal;     //保存excel的时候，不弹出是否保存的窗口直接进行保存   workbook.savecopyas(temp);/**////填入完信息之后另存到路径及文件名字

excel宏操作，sheet和单元格操作：

     /// <summary>     /// 讀取excel數據和插入公式     /// </summary>     /// <param name="nder"></param>     /// <param name="e"></param>     protected void button2_click(object nder, eventargs e)     {         datetime t1 = datetime.now;         respon.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();          /write/進行宏的循環應用與單元格的刪除和添加，多個單元格默認宏自動操作。         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.lect();         range7.entirerow.delete(excel.xldirection.xlup);          //獲取最大用過的行數         excel.worksheet wsheet1 = (excel.worksheet)excelkk.worksheets.get_item(1);         int n =wsheet1.udrange.cells.columns.count;         respon.write(n.tostring() + "<br>");         //messagebox.show(n.tostring());         n = wsheet1.udrange.cells.rows.count;         respon.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_offt(1,4).lect();         range4.copy(range5);         //停用警告信息         excelkk.displayalerts = fal;         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有一定的用处。

以上所述是www.887551.com给大家介绍的c#多种操作excel的方法比较，希望对大家有所帮助。在此也非常感谢大家对www.887551.com网站的支持！