前言：公司让做一个导出数据到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 = ; //行标
int maxRowNum = ;
int minRowNum = ;
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 > ? : );
for (int i = ; i < index; i++)
{
m_SyncContext.Post(SetTextSafePost, string.Format("第{0}个Excel数据正在读取数据，请稍后.....", i + ));
//创建工作薄
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 = ;
minRowNum = i * count;
maxRowNum = (i + ) * 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 == )
{
headerRow = sheet.CreateRow();
for (int colIndex = ; colIndex < columns; colIndex++)
{
cell = headerRow.CreateCell(colIndex);
cell.SetCellValue(reader.GetName(colIndex).Trim());
cell.CellStyle = HeadercellStyle;
}
}
headerRow = sheet.CreateRow(rowIndex + );
for (int colIndex = ; colIndex < columns; colIndex++)
{
cell = headerRow.CreateCell(colIndex);
cell.SetCellValue(reader[reader.GetName(colIndex)].ToString());
cell.CellStyle = cellStyle;
}
rowIndex++;
} }
if (workbook.GetSheet("Sheet1").GetRow() != null)
{
m_SyncContext.Post(SetTextSafePost, String.Format("第{0}个Excel读取数据完毕，正在写入Excel文件中，请稍后.....", i + ));
fileName = fileNames[] + (i + ).ToString() + "." + fileNames[];
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, , 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 = "";
txtSize.Text = "";
}
}
}