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

先上导出代码  

  /// 
 
      /// 导出速度最快 
      /// 
 
      /// <列名，数据> 
      ///  
      ///  
      public bool NewExport(List 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; 
     var values = lv.Value as List>; 
     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;

      

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

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持考高分网。