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  }    1. 打开现有的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名称      2.复制sheet页    mySheet.Copy(Type.Missing, workbook.Sheets[1]); //复制mySheet成一个新的sheet页，复制完后的名称是mySheet页名称后加一个(2)，这里就是testsheet(2)，复制完后，Worksheet的数量增加一个      注意 这里Copy方法的两个参数，指是的复制出来新的sheet页是在指定sheet页的前面还是后面，上面的例子就是指复制的sheet页在第一个sheet页的后面。  3.删除sheet页        xlsApp.DisplayAlerts = false; //如果想删除某个sheet页，首先要将此项设为fasle。  (xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Delete();      4.选中sheet页        (xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Select(Type.Missing); //选中某个sheet页      5.另存excel文件    workbook.Saved = true;  workbook.SaveCopyAs(filepath);      6.释放excel资源        workbook.Close(true, Type.Missing, Type.Missing);  workbook = null;  xlsApp.Quit();  xlsApp = null;    

 

 

 

方法2：

 

 

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();//强行销毁             }    }}

 

 

方法3：

 

先去官网：http://npoi.codeplex.com/下载需要引入dll（可以选择.net2.0或者.net4.0的dll），然后在网站中添加引用。

导出代码：

 

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;

设置单元格宽高：

　　设置单元格的高度实际是设置其所在行高，所以要在单元格所在行上设置行高，行高设置数值好像是像素点的1/20，所以*20以便达到设置效果；

　　设置单元格的宽度实际上是设置其所在列宽，所以要在单元格所在列上设置（列的设置在工作表上），宽度数值好像是字符的1/256，所以*256以便达到设置效果。　

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

 

 合并单元格：合并单元格实际上是声明一个区域，该区域中的单元格将进行合并，合并后的内容与样式以该区域最左上角的单元格为准。 

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

 

添加公式：使用Cell的CellFormula来设置公式，是一个字符串，公式前不需要加=号。

//通过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);}