Winform .NET 利用NPOI导出大数据量的Excel
前言：公司让做一个导出数据到Excel的小工具，要求是用户前端输入sql语句，点击导出按钮之后，将数据导出到Excel，界面如图所示：文件下端显示导出的进度
遇到的问题：
1、使用NPOI进行Excel 的导出，使用XSSFWorkbook导出，这个类可以使每sheet条数超过十万条，使用HSSFWorkbook每个sheet只可以导出65535条数据。
2、导出数据时，创建新的线程进行导出，防止页面卡死不动。
3、在使用线程时，如果给线程方法传递参数，此示例中主要是通过
Thread t = new Thread(new ParameterizedThreadStart(T));
t.Start(filePath);
其中，T为线程方法；filePath为传递给T方法的参数。
T方法的声明：private void T(object o){}
由此可看出，T 的方法参数必须声明为object类型
4、在导出的过程中，如果一共有130万的数据，那么需要分n个Excel导出，一个Excel放130万的数据，并不是每台电脑都可以打开。
5、比如说一个Excel放30万数据，每次查询30万的数据，查询完之后，就生成一个Excel，然后再查询第二个30万，导出第二个Excel，读取完成一个，变量马上释放，防止内存溢出。
6、使用变量时，应及时释放，而且循环中最好不要声明变量，因为循环中声明变量的话，会导致不断的给变量分配内存，如果不及时释放的话，会导致内存溢出。
7、导出数据时，另起了一个线程（方法名称为T），这个线程不能直接获得页面上的控件，原因是页面上的控件和导数据的线程不是同一个，所以无法直接获取。可以通过线程SynchronizationContext实现：
SynchronizationContext m_SyncContext = null; //声明
public Form1()
{
InitializeComponent();
//获取UI线程同步上下文
m_SyncContext = SynchronizationContext.Current;
}
导出数据的线程方法T：
private void T(object o)
{
m_SyncContext.Post(SetTextSafePost, String.Format("{0:N0}", rate * 100) + "%");
}
其中，Post方法的第一个参数为声明的方法名，第二个参数为SetTextSafePost方法的参数
SetTextSafePost方法：
private void SetTextSafePost(object o)
{
label1.Text = "文件下载进度：" + o.ToString();
}
SetTextSafePost的方法的参数也是必须为Object类型
7、在循环写入的Excel文档中时，将查询的结果集放大DataTable里不是一个好的解决方案，因为将所有的数据都放到DataTable里面是所有的数据都压到内存中，如果数据量过大的话，容易导致内存溢出。可以使用MySqlDataReader，while循环的过程中，将数据写入到Excel中。
代码示例：
using MySql.Data.MySqlClient; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.IO; using System.Linq; using System.Text; using System.Threading; using System.Windows.Forms; namespace ExportDataBySQLToExcel { public partial class Form1 : Form { private string sql = ""; SynchronizationContext m_SyncContext = null; public Form1() { InitializeComponent(); //获取UI线程同步上下文 m_SyncContext = SynchronizationContext.Current; } /// <summary> /// 打开配置连接信息页面 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button2_Click(object sender, EventArgs e) { ConfigForm cf = new ConfigForm(); cf.ShowDialog(); } /// <summary> /// 导出按钮 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button1_Click(object sender, EventArgs e) { sql = txtSql.Text; if (sql == "") { MessageBox.Show("sql文本不允许为空，请确认"); return; } if (txtCount.Text == "") { MessageBox.Show("最大行数不允许为空，请确认"); return; } if (txtSize.Text == "") { MessageBox.Show("总条数不能为空"); } SaveFileDialog sfd = new SaveFileDialog(); //sfd.Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx"; sfd.Filter = "Excel文件(*.xlsx)|*.xlsx"; if (sfd.ShowDialog() == DialogResult.OK) { button1.Enabled = false; button2.Enabled = false; label1.Text = "文件下载中..."; string filePath = sfd.FileName; Thread t = new Thread(new ParameterizedThreadStart(T)); t.Start(filePath); } } /// <summary> /// 线程方法 /// </summary> private void T(object o) { string fileName; string[] fileNames = o.ToString().Split('.'); XSSFWorkbook workbook = null; try { //创建表 ISheet sheet = null; ICellStyle cellStyle = null; NPOI.SS.UserModel.IFont cellfont = null; //获得DataTable MySqlHelper.connectionStringManager = PubClass.getConnString(); IRow headerRow = null; ICell cell = null; int count = int.Parse(txtCount.Text); //一个Excel的总条数 int rowIndex = 0; //行标 int maxRowNum = 0; int minRowNum = 0; string mysql = ""; //string countsql = string.Format("SET @rows=0; SELECT ROWNUM FROM ({0}) AS A ORDER BY ROWNUM DESC LIMIT 1", sql, minRowNum, maxRowNum); //DataTable dt = MySqlHelper.GetDataTable(null, CommandType.Text, countsql); if (txtSize.Text!="") { int size = int.Parse(txtSize.Text); // 1130389; //size = int.Parse(dt.Rows[0]["ROWNUM"].ToString()); int index = size / count + (size % count > 0 ? 1 : 0); for (int i = 0; i < index; i++) { m_SyncContext.Post(SetTextSafePost, string.Format("第{0}个Excel数据正在读取数据，请稍后.....", i + 1)); //创建工作薄 workbook = new XSSFWorkbook(); sheet = workbook.CreateSheet("Sheet1"); #region 样式 ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); #endregion rowIndex = 0; minRowNum = i * count; maxRowNum = (i + 1) * count; mysql = string.Format("SET @rows=0; SELECT * FROM ({0}) AS A WHERE A.ROWNUM>{1} AND a.ROWNUM<={2}", sql, minRowNum, maxRowNum); using (MySqlDataReader reader = MySqlHelper.ExecuteReader(MySqlHelper.connectionStringManager, CommandType.Text, mysql, null)) { int columns = reader.FieldCount; //获得总列数 while (reader.Read()) { if (rowIndex == 0) { headerRow = sheet.CreateRow(0); for (int colIndex = 0; colIndex < columns; colIndex++) { cell = headerRow.CreateCell(colIndex); cell.SetCellValue(reader.GetName(colIndex).Trim()); cell.CellStyle = HeadercellStyle; } } headerRow = sheet.CreateRow(rowIndex + 1); for (int colIndex = 0; colIndex < columns; colIndex++) { cell = headerRow.CreateCell(colIndex); cell.SetCellValue(reader[reader.GetName(colIndex)].ToString()); cell.CellStyle = cellStyle; } rowIndex++; } } if (workbook.GetSheet("Sheet1").GetRow(1) != null) { m_SyncContext.Post(SetTextSafePost, String.Format("第{0}个Excel读取数据完毕，正在写入Excel文件中，请稍后.....", i + 1)); fileName = fileNames[0] + (i + 1).ToString() + "." + fileNames[1]; SaveExcel(fileName, workbook); } else { break; } workbook = null; } m_SyncContext.Post(SetTextSafePost, String.Format("导出成功")); m_SyncContext.Post(setButtonEnable, true); MessageBox.Show("导出成功", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { m_SyncContext.Post(SetTextSafePost, String.Format("导出失败")); m_SyncContext.Post(setButtonEnable, true); MessageBox.Show("导出失败,提示信息为：" + ex.Message, "消息", MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { workbook = null; //将WorkBook写入到File中 //for (int i = 0; i < lstWorkBook.Count; i++) //{ // fileName = fileNames[0] + i.ToString() + "." + fileNames[1]; // SaveExcel(fileName, lstWorkBook[i]); //} } } private void SaveExcel(string fileName, XSSFWorkbook workbook) { if (workbook != null) { //转为字节数组 MemoryStream stream = new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } } private void SetTextSafePost(object o) { label1.Text = o.ToString(); } private void setButtonEnable(object o) { button1.Enabled = (bool)o; button2.Enabled = (bool)o; label1.Text = "文件下载完成"; } /// <summary> /// 没用的代码 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Form1_Load(object sender, EventArgs e) { sql = txtSql.Text; txtCount.Text = "300000"; txtSize.Text = "1000000"; } } }
