正文

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

︶ㄣ速达网 メ
米米素材网V管理员 / 阅读
0708

本篇文章主要介绍了C#实现几十万级数据导出Excel及Excel各种操作实例，这里整理了详细的代码,有需要的小伙伴可以参考下。

先上导出代码  

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
/// <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;
    }
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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文件  

?
1
2
3
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页  

?
1
2
mySheet.Copy(Type.Missing, workbook.Sheets[1]);
//复制mySheet成一个新的sheet页，复制完后的名称是mySheet页名称后加一个(2)，这里就是testsheet(2)，复制完后，Worksheet的数量增加一个

注意 这里Copy方法的两个参数，指是的复制出来新的sheet页是在指定sheet页的前面还是后面，上面的例子就是指复制的sheet页在第一个sheet页的后面。 

3.删除sheet页  

?
1
2
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文件  

?
1
2
workbook.Saved = true;
workbook.SaveCopyAs(filepath);

6.释放excel资源  

?
1
2
3
4
workbook.Close(true, Type.Missing, Type.Missing);
workbook = null;
xlsApp.Quit();
xlsApp = null;

方法2：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
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），然后在网站中添加引用。

导出代码：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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();

导入代码：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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

用法：

首先建立一个空白的工作簿用作测试，并在其中建立空白工作表，在表中建立空白行，在行中建立单元格，并填入内容：

?
1
2
3
4
5
6
7
8
9
10
//建立空白工作簿
IWorkbook workbook = new HSSFWorkbook();
//在工作簿中：建立空白工作表
ISheet sheet = workbook.CreateSheet();
//在工作表中：建立行，参数为行号，从0计
IRow row = sheet.CreateRow(0);
//在行中：建立单元格，参数为列号，从0计
ICell cell = row.CreateCell(0);
//设置单元格内容
cell.SetCellValue("实习鉴定表");

设置单元格样式：设置单元格样式时需要注意，务必创建一个新的样式对象进行设置，否则会将工作表所有单元格的样式一同设置，它们应该共享的是一个样式对象：

?
1
2
3
4
5
6
7
8
9
10
11
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以便达到设置效果。

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

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

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

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

?
1
2
3
4
//通过Cell的CellFormula向单元格中写入公式
//注：直接写公式内容即可，不需要在最前加'='
ICell cell2 = sheet.CreateRow(1).CreateCell(0);
cell2.CellFormula = "HYPERLINK(\"测试图片.jpg\",\"测试图片.jpg\")";

 将工作簿写入文件查看效果：

?
1
2
3
4
5
//将工作簿写入文件
using (FileStream fs = new FileStream("生成效果.xls", FileMode.Create, FileAccess.Write))
{
 workbook.Write(fs);
}

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

原文链接：http://www.cnblogs.com/ShoneH/p/5587358.html