1、NuGet直接安装NPOI程序包;
2、
using NPOI.XSSF.UserModel;
3、导出Excel代码:
private void TsbExport2ExcelClick(object sender, EventArgs e)
{
//获取qrkey
ToolStripButton tsbFiterBy = ((ToolStripButton)sender);
long qryRKEY = Convert.ToInt64(tsbFiterBy.Name.Replace("TsbExport2Excel", string.Empty));
QARRect qr = QryAndRptList.Find(q => q.QryRKEY == qryRKEY);
if (qr == null)
{
MyMsg.Warning("没有找到查询定义条目,请关闭本窗体重试.");
return;
}
A12DataGridView dgvAutoMain = (A12DataGridView)Controls.Find("DGVAutoMain" + qryRKEY, true)[0];
if (dgvAutoMain == null) return;
if (dgvAutoMain.Rows.Count <= 0)
{
MyMsg.Information("没有需要导出的数据,请检查.");
return;
}
TabPage tabPage= (TabPage)Controls.Find("QryTpg" + qryRKEY, true)[0];
string tagSheetName = tabPage.Text;
string saveFileName = tabPage.Text + DateTime.Now.ToString("yyyy-MM-dd");
var saveFileDialoge = new SaveFileDialog
{
FileName = saveFileName,
Filter = "Excel Documents|*.xls;*.xlsx;*.xlsm",
DefaultExt = ".xlsx"
};
if (saveFileDialoge.ShowDialog() != DialogResult.OK)
{
return;
}
else
{
saveFileName = saveFileDialoge.FileName;
if (string.IsNullOrEmpty(saveFileName))
{
return;
}
}
if (File.Exists(saveFileName))
{
File.Delete(saveFileName);//因为在选取文件名称时就有提示是否覆盖,此处直接删除
}
//创建或匹配一个BackgroundWorker,初始化一个耗时任务
BackgroundWorker bgwk = new BackgroundWorker();
BgwkDef bgwkDef = new BgwkDef()
{
RunningAction = delegate () { Export2Excel(bgwk,dgvAutoMain, saveFileName, tagSheetName); },
TagBgwk = bgwk
};
BeginBgwork(bgwkDef);
}
导出按钮代码
private void Export2Excel(BackgroundWorker bgwk, A12DataGridView tagDGV, string fullSaveFileName, string tagSheetName)
{
if (string.IsNullOrEmpty(fullSaveFileName)) return;
try
{
bgwk.ReportProgress(1, "正在准备文档...");
NPOI.SS.UserModel.IWorkbook tagWorkbook = new XSSFWorkbook();
NPOI.SS.UserModel.ISheet tagSheet = tagWorkbook.CreateSheet(tagSheetName);//创建一个Sheet
#region 标题行
//创建标题行
NPOI.SS.UserModel.IRow rowH = tagSheet.CreateRow(0);
//创建单元格样式
NPOI.SS.UserModel.ICellStyle cellStyle = tagWorkbook.CreateCellStyle();
//创建格式
NPOI.SS.UserModel.IDataFormat dataFormat = tagWorkbook.CreateDataFormat();
//设置为文本格式,也可以为 text,即 dataFormat.GetFormat("text");
cellStyle.DataFormat = dataFormat.GetFormat("@");
cellStyle.FillForegroundColor = NPOI.SS.UserModel.IndexedColors.Yellow.Index;
cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Hair;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Hair;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Hair;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Hair;
//设置列名
foreach (DataGridViewColumn col in tagDGV.Columns)
{
//创建单元格并设置单元格内容
rowH.CreateCell(col.Index).SetCellValue(col.HeaderText);
rowH.Height = 20 * 20;
//设置单元格格式,宽度与Datagridview列宽匹配
rowH.Cells[col.Index].CellStyle = cellStyle;
tagSheet.SetColumnWidth(col.Index, OString.NZ2Int(col.Width / 7.5 * 356));
}
tagSheet.CreateFreezePane(0, 1, 0, 1);
#endregion
bgwk.ReportProgress(2, "正在准备数据...");
DataTable DTTmp = OData.GetDTFromObject(tagDGV.DataSource).Copy();
bgwk.ReportProgress(50, "正在写入数据到档案中...");
//写入数据
//创建一个单元格
NPOI.SS.UserModel.ICell cell = null;
int totColCount = DTTmp.Columns.Count;
int totRowCount = DTTmp.Rows.Count;
for (int i = 0; i < totRowCount; i++)
{
//跳过第一行,第一行为列名
NPOI.SS.UserModel.IRow row = tagSheet.CreateRow(i + 1);
row.Height = 20* 20;
for (int j = 0; j < totColCount; j++)
{
cell = row.CreateCell(j);
#region 根据列类型写入值
Type clmDataType = DTTmp.Columns[j].DataType;
if ((clmDataType == typeof(char)) || (clmDataType == typeof(Guid))|| (clmDataType == typeof(string)))
{
cell.SetCellValue(OString.NZ2Str(DTTmp.Rows[i][j]));
}
else if(clmDataType == typeof(bool))
{
cell.SetCellValue(OString.NZ2Bool(DTTmp.Rows[i][j]));
}
else if ((clmDataType == typeof(byte)) || (clmDataType == typeof(decimal)) || (clmDataType == typeof(double)) || (clmDataType == typeof(short)) || (clmDataType == typeof(int)) || (clmDataType == typeof(long)) || (clmDataType == typeof(sbyte)) || (clmDataType == typeof(float)) || (clmDataType == typeof(TimeSpan)) || (clmDataType == typeof(ushort)) || (clmDataType == typeof(uint)) || (clmDataType == typeof(ulong)))
{
cell.SetCellValue(OString.NZ2Double(DTTmp.Rows[i][j]));
}
else if(clmDataType == typeof(DateTime))
{
cell.SetCellValue(Convert.ToDateTime(OString.NZ2DateTime(DTTmp.Rows[i][j])));
//创建单元格样式
NPOI.SS.UserModel.ICellStyle cellStyleValues = tagWorkbook.CreateCellStyle();
//创建格式
NPOI.SS.UserModel.IDataFormat dataFormatValues = tagWorkbook.CreateDataFormat();
if (cell.NumericCellValue.ToString().IndexOf('.') == -1)
{
cellStyleValues.DataFormat = dataFormatValues.GetFormat("yyyy-mm-dd");
}
else
{
cellStyleValues.DataFormat = dataFormatValues.GetFormat("yyyy-mm-dd hh:mm:ss");
}
//设置单元格格式
cell.CellStyle = cellStyleValues;
}
else
{
cell.SetCellValue(OString.NZ2Str(DTTmp.Rows[i][j]));
}
#endregion
}
}
bgwk.ReportProgress(95, "正在保存档案...");
tagWorkbook.Write(new FileStream(fullSaveFileName, FileMode.Create, FileAccess.ReadWrite));
bgwk.ReportProgress(100, "导出成功!");
return;
}
catch (Exception ex)
{
CancelBgwork(bgwk);
MyMsg.Exclamation("导出数据失败,请检查!",ex.Message);
return;
}
}
核心导出代码
这2段摘自框架的通用数据分析平台,其中采用了后台导出任务对话框,可以参考其它文章。