C#实现几十万级数据导出Excel及Excel各种操作实例

  /// <summary> 
      /// 导出速度最快 
      /// </summary> 
      /// <param name="list"><列名，数据></param> 
      /// <param name="filepath"></param> 
      /// <returns></returns> 
      public bool newexport(list<dictionaryentry> list, string filepath) 
      { 
        bool bsuccess = true; 
        microsoft.office.interop.excel.application appexcel = new microsoft.office.interop.excel.application(); 
        system.reflection.missing miss = system.reflection.missing.value; 
        appexcel = new microsoft.office.interop.excel.application(); 
        microsoft.office.interop.excel.workbook workbookdata = null; 
        microsoft.office.interop.excel.worksheet worksheetdata = null; 
        microsoft.office.interop.excel.range rangedata; 
   
        workbookdata = appexcel.workbooks.add(); 
   
        //设置对象不可见 
        appexcel.visible = false; 
        appexcel.displayalerts = false; 
        try 
        { 
          foreach (var lv in list) 
          { 
            var keys = lv.key as list<string>; 
            var values = lv.value as list<ilist<object>>; 
            worksheetdata = (microsoft.office.interop.excel.worksheet)workbookdata.worksheets.add(miss, workbookdata.activesheet); 
   
            for (int i = 0; i < keys.count-1; i++) 
            { 
              //给工作表赋名称 
              worksheetdata.name = keys[0];//列名的第一个数据位表名 
              worksheetdata.cells[1, i + 1] = keys[i+1]; 
            } 
   
            //因为第一行已经写了表头，所以所有数据都应该从a2开始 
            rangedata = worksheetdata.get_range("a2", miss); 
            microsoft.office.interop.excel.range xlrang = null; 
   
            //irowcount为实际行数，最大行 
            int irowcount = values.count; 
            int iparstedrow = 0, icurrsize = 0; 
   
            //ieachsize为每次写行的数值，可以自己设置 
            int ieachsize = 10000; 
   
            //icolumnaccount为实际列数，最大列数 
            int icolumnaccount = keys.count-1; 
   
            //在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数 
            object[,] objval = new object[ieachsize, icolumnaccount]; 
            icurrsize = ieachsize; 
   
            while (iparstedrow < irowcount) 
            { 
              if ((irowcount - iparstedrow) < ieachsize) 
                icurrsize = irowcount - iparstedrow; 
   
              //用for循环给数组赋值 
              for (int i = 0; i < icurrsize; i++) 
              { 
                for (int j = 0; j < icolumnaccount; j++) 
                { 
                  var v = values[i + iparstedrow][j]; 
                  objval[i, j] = v != null ? v.tostring() : ""; 
                } 
              } 
              string x = "a" + ((int)(iparstedrow + 2)).tostring(); 
              string col = ""; 
              if (icolumnaccount <= 26) 
              { 
                col = ((char)('a' + icolumnaccount - 1)).tostring() + ((int)(iparstedrow + icurrsize + 1)).tostring(); 
              } 
              else 
              { 
                col = ((char)('a' + (icolumnaccount / 26 - 1))).tostring() + ((char)('a' + (icolumnaccount % 26 - 1))).tostring() + ((int)(iparstedrow + icurrsize + 1)).tostring(); 
              } 
              xlrang = worksheetdata.get_range(x, col); 
              xlrang.numberformat = "@"; 
              // 调用range的value2属性，把内存中的值赋给excel 
              xlrang.value2 = objval; 
              iparstedrow = iparstedrow + icurrsize; 
            } 
          } 
          ((microsoft.office.interop.excel.worksheet)workbookdata.worksheets["sheet1"]).delete(); 
          ((microsoft.office.interop.excel.worksheet)workbookdata.worksheets["sheet2"]).delete(); 
          ((microsoft.office.interop.excel.worksheet)workbookdata.worksheets["sheet3"]).delete(); 
          //保存工作表 
          workbookdata.saveas(filepath, miss, miss, miss, miss, miss, microsoft.office.interop.excel.xlsaveasaccessmode.xlnochange, miss, miss, miss); 
          workbookdata.close(false, miss, miss); 
          appexcel.workbooks.close(); 
          appexcel.quit(); 
   
          system.runtime.interopservices.marshal.releasecomobject(workbookdata); 
          system.runtime.interopservices.marshal.releasecomobject(appexcel.workbooks); 
          system.runtime.interopservices.marshal.releasecomobject(appexcel); 
          gc.collect(); 
        } 
        catch (exception ex) 
        { 
          errormsg = ex.message; 
          bsuccess = false; 
        } 
        finally 
        { 
          if (appexcel != null) 
          { 
            excelimporthelper.killspecialexcel(appexcel); 
          } 
        } 
        return bsuccess; 
      }

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.borders.get_item(microsoft.office.interop.excel.xlbordersindex.xledgetop).linestyle = microsoft.office.interop.excel.xllinestyle.xllinestylenone; //设置单元格上边框为无边框   
  
range.entirecolumn.autofit();   //自动调整列宽   
  
range.horizontalalignment= xlcenter;   // 文本水平居中方式   
  
range.verticalalignment= xlcenter   //文本垂直居中方式   
  
range.wraptext=true;   //文本自动换行   
  
range.interior.colorindex=39;   //填充颜色为淡紫色   
  
range.font.color=clblue;   //字体颜色   
  
xlsapp.displayalerts=false;  //对excel的操作 不弹出提示信息 
applicationclass xlsapp = new applicationclass(); // 1. 创建excel应用程序对象的一个实例，相当于我们从开始菜单打开excel应用程序。 
if (xlsapp == null) 
{ 
//对此实例进行验证，如果为null则表示运行此代码的机器可能未安装excel 
} 

workbook workbook = xlsapp.workbooks.open(excelfilepath, 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); 
worksheet mysheet = workbook.sheets[1] as worksheet; //第一个sheet页 
mysheet.name = "testsheet"; //这里修改sheet名称 

mysheet.copy(type.missing, workbook.sheets[1]);
//复制mysheet成一个新的sheet页，复制完后的名称是mysheet页名称后加一个(2)，这里就是testsheet(2)，复制完后，worksheet的数量增加一个

xlsapp.displayalerts = false; //如果想删除某个sheet页，首先要将此项设为fasle。 
(xlsapp.activeworkbook.sheets[1] as worksheet).delete(); 

workbook.saved = true; 
workbook.savecopyas(filepath); 

workbook.close(true, type.missing, type.missing); 
workbook = null; 
xlsapp.quit(); 
xlsapp = null;

using system;
using system.collections.generic;
using system.linq;
using system.text;
using microsoft.office.interop.excel;
using system.data;

namespace exceltest
{
  public class excelutil
  {
    system.data.datatable table11 = new system.data.datatable();

    public void exporttoexcel(system.data.datatable table, string savefilename)
    {

      bool filesaved = false;

      //excelapp xlapp = new excelapp();

      application xlapp = new application();

      if (xlapp == null)
      {
        return;
      }

      workbooks workbooks = xlapp.workbooks;
      workbook workbook = workbooks.add(xlwbatemplate.xlwbatworksheet);
      worksheet worksheet = (worksheet)workbook.worksheets[1];//取得sheet1

      long rows = table.rows.count;

      /*下边注释的两行代码当数据行数超过行时，出现异常：异常来自hresult:0x800a03ec。因为：excel 2003每个sheet只支持最大行数据

      //range fchr = worksheet.get_range(worksheet.cells[1, 1], worksheet.cells[table.rows.count+2, gridview.columns.view.visiblecolumns.count+1]);

      //fchr.value2 = datas;*/

      if (rows > 65535)
      {

        long pagerows = 60000;//定义每页显示的行数,行数必须小于

        int scount = (int)(rows / pagerows);

        if (scount * pagerows < table.rows.count)//当总行数不被pagerows整除时，经过四舍五入可能页数不准
        {
          scount = scount + 1;
        }

        for (int sc = 1; sc <= scount; sc++)
        {
          if (sc > 1)
          {

            object missing = system.reflection.missing.value;

            worksheet = (microsoft.office.interop.excel.worksheet)workbook.worksheets.add(

            missing, missing, missing, missing);//添加一个sheet

          }

          else
          {
            worksheet = (worksheet)workbook.worksheets[sc];//取得sheet1
          }

          string[,] datas = new string[pagerows + 1, table.columns.count+ 1];

for (int i = 0; i < table.columns.count; i++) //写入字段
          {
            datas[0, i] = table.columns[i].caption;
          }

          range range = worksheet.get_range(worksheet.cells[1, 1], worksheet.cells[1, table.columns.count]);
          range.interior.colorindex = 15;//15代表灰色
          range.font.bold = true;
          range.font.size = 9;

          int init = int.parse(((sc - 1) * pagerows).tostring());
          int r = 0;
          int index = 0;
          int result;

          if (pagerows * sc >= table.rows.count)
          {
            result = table.rows.count;
          }
          else
          {
            result = int.parse((pagerows * sc).tostring());
          }
          for (r = init; r < result; r++)
          {
            index = index + 1;
            for (int i = 0; i < table.columns.count; i++)
            {
              if (table.columns[i].datatype == typeof(string) || table.columns[i].datatype == typeof(decimal) || table.columns[i].datatype == typeof(datetime))
              {
                object obj = table.rows[r][table.columns[i].columnname];
                datas[index, i] = obj == null ? "" : "'" + obj.tostring().trim();//在obj.tostring()前加单引号是为了防止自动转化格式

              }

            }
          }

          range fchr = worksheet.get_range(worksheet.cells[1, 1], worksheet.cells[index + 2, table.columns.count + 1]);

          fchr.value2 = datas;
          worksheet.columns.entirecolumn.autofit();//列宽自适应。

          range = worksheet.get_range(worksheet.cells[1, 1], worksheet.cells[index + 1, table.columns.count]);

          //15代表灰色

          range.font.size = 9;
          range.rowheight = 14.25;
          range.borders.linestyle = 1;
          range.horizontalalignment = 1;

        }

      }

      else
      {

        string[,] datas = new string[table.rows.count + 2, table.columns.count + 1];
        for (int i = 0; i < table.columns.count; i++) //写入字段     
        {
          datas[0, i] = table.columns[i].caption;
        }

        range range = worksheet.get_range(worksheet.cells[1, 1], worksheet.cells[1, table.columns.count]);
        range.interior.colorindex = 15;//15代表灰色
        range.font.bold = true;
        range.font.size = 9;

        int r = 0;
        for (r = 0; r < table.rows.count; r++)
        {
          for (int i = 0; i < table.columns.count; i++)
          {
            if (table.columns[i].datatype == typeof(string) || table.columns[i].datatype == typeof(decimal) || table.columns[i].datatype == typeof(datetime))
            {
              object obj = table.rows[r][table.columns[i].columnname];
              datas[r + 1, i] = obj == null ? "" : "'" + obj.tostring().trim();//在obj.tostring()前加单引号是为了防止自动转化格式

            }

          }

          //system.windows.forms.application.doevents();

}

        range fchr = worksheet.get_range(worksheet.cells[1, 1], worksheet.cells[table.rows.count + 2, table.columns.count + 1]);

        fchr.value2 = datas;
        
        worksheet.columns.entirecolumn.autofit();//列宽自适应。

        range = worksheet.get_range(worksheet.cells[1, 1], worksheet.cells[table.rows.count + 1, table.columns.count]);

        //15代表灰色

        range.font.size = 9;
        range.rowheight = 14.25;
        range.borders.linestyle = 1;
        range.horizontalalignment = 1;
      }

      if (savefilename != "")
      {
        try
        {
          workbook.saved = true;
          workbook.savecopyas(savefilename);
          filesaved = true;

        }

        catch (exception ex)
        {
          filesaved = false;
        }

      }

      else
      {

        filesaved = false;

      }

      xlapp.quit();

      gc.collect();//强行销毁 
  
    }
  }
}

npoi.hssf.usermodel.hssfworkbook book = new npoi.hssf.usermodel.hssfworkbook();
npoi.ss.usermodel.isheet sheet = book.createsheet("test_01");

// 第一列
npoi.ss.usermodel.irow row = sheet.createrow(0);
row.createcell(0).setcellvalue("第一列第一行");

// 第二列
npoi.ss.usermodel.irow row2 = sheet.createrow(1);
row2.createcell(0).setcellvalue("第二列第一行");

// ...

// 写入到客户端 
system.io.memorystream ms = new system.io.memorystream();
book.write(ms);
response.addheader("content-disposition", string.format("attachment; filename={0}.xls", datetime.now.tostring("yyyymmddhhmmssfff")));
response.binarywrite(ms.toarray());
book = null;
ms.close();
ms.dispose();

hssfworkbook hssfworkbook; 
#region 
public datatable importexcelfile(string filepath) 
{ 
  #region//初始化信息 
  try 
  { 
    using (filestream file = new filestream(filepath, filemode.open, fileaccess.read)) 
    { 
      hssfworkbook = new hssfworkbook(file); 
    } 
  } 
  catch (exception e) 
  { 
    throw e; 
  } 
  #endregion 
 
  npoi.ss.usermodel.sheet sheet = hssfworkbook.getsheetat(0); 
  system.collections.ienumerator rows = sheet.getrowenumerator(); 
  datatable dt = new datatable(); 
  for (int j = 0; j < (sheet.getrow(0).lastcellnum); j++) 
  { 
    dt.columns.add(convert.tochar(((int)'a') + j).tostring()); 
  } 
  while (rows.movenext()) 
  { 
    hssfrow row = (hssfrow)rows.current; 
    datarow dr = dt.newrow(); 
    for (int i = 0; i < row.lastcellnum; i++) 
    { 
      npoi.ss.usermodel.cell cell = row.getcell(i); 
      if (cell == null) 
      { 
        dr[i] = null; 
      } 
      else 
      { 
        dr[i] = cell.tostring(); 
      } 
    } 
    dt.rows.add(dr); 
  } 
  return dt; 
} 
#endregion

//建立空白工作簿
iworkbook workbook = new hssfworkbook();
//在工作簿中：建立空白工作表
isheet sheet = workbook.createsheet();
//在工作表中：建立行，参数为行号，从0计
irow row = sheet.createrow(0);
//在行中：建立单元格，参数为列号，从0计
icell cell = row.createcell(0);
//设置单元格内容
cell.setcellvalue("实习鉴定表");

icellstyle style = workbook.createcellstyle();
//设置单元格的样式：水平对齐居中
style.alignment = horizontalalignment.center;
//新建一个字体样式对象
ifont font = workbook.createfont();
//设置字体加粗样式
font.boldweight = short.maxvalue;
//使用setfont方法将字体样式添加到单元格样式中 
style.setfont(font);
//将新的样式赋给单元格
cell.cellstyle = style;

//设置单元格的高度
row.height = 30 * 20;
//设置单元格的宽度
sheet.setcolumnwidth(0, 30 * 256);

//设置一个合并单元格区域，使用上下左右定义cellrangeaddress区域
//cellrangeaddress四个参数为：起始行，结束行，起始列，结束列
sheet.addmergedregion(new cellrangeaddress(0, 0, 0, 10));

//通过cell的cellformula向单元格中写入公式
//注：直接写公式内容即可，不需要在最前加'='
icell cell2 = sheet.createrow(1).createcell(0);
cell2.cellformula = "hyperlink(\"测试图片.jpg\",\"测试图片.jpg\")";

//将工作簿写入文件
using (filestream fs = new filestream("生成效果.xls", filemode.create, fileaccess.write))
{
 workbook.write(fs);
}