Datatable导出excel

lyx程序人生

已于 2023-01-16 16:53:08 修改

阅读量2.2k

点赞数

分类专栏： Excel操作辅助类文章标签： linq c# wpf

于 2022-06-17 13:59:55 首次发布

本文链接：https://blog.csdn.net/vs920079469vs/article/details/125332290

版权

Excel操作辅助类专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、引用第三方控件：NPOI.dll、NPOI.OOXML.dll、NPOI.OpenXml4Net.dll、NPOI.OpenXmlFormats.dll

二、调用方法：

DataTable dtA = ExcelHelper.ExcelToDataTable(@"D:\对比数据A.xlsx", 0, true, false, 3);

ExcelHelper.DataSetToExcel(ds, @"D:\对比数据结果.xlsx");

三、辅助类如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;

namespace Demo
{
public class ExcelHelper
{
/// <summary>
/// 将excel导入到datatable
/// </summary>
/// <param name="filePath">excel路径</param>
/// <param name="sheetIndex">sheet索引</param>
/// <param name="isColumnHead">第一行是否是列名</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string filePath, int sheetIndex, bool isColumnHead, bool isTitleHead = false, int? columnCount = null)
{
DataTable dataTable = null;
//FileStream fs = null;
DataColumn column = null;
DataRow dataRow = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
//int startRow = 0;
try
{
using (FileStream fs = File.OpenRead(filePath))
{
// 2007版本
if (filePath.ToLower().IndexOf(".xlsx") > 0)
workbook = new XSSFWorkbook(fs);
// 2003版本
else if (filePath.ToLower().IndexOf(".xls") > 0)
workbook = new HSSFWorkbook(fs);

if (workbook != null)
{
sheet = workbook.GetSheetAt(sheetIndex);//读取第一个sheet，当然也可以循环读取每个sheet

if (sheet != null)
{
dataTable = new DataTable("dt");
int rowCount = sheet.LastRowNum;//总行数
if (rowCount > 0)
{
int startRow = 0;
//标题
if (isTitleHead)
{
row = sheet.GetRow(startRow);
cell = row.GetCell(row.FirstCellNum);
if (cell != null && cell.StringCellValue != null)
{
dataTable.TableName = cell.StringCellValue;
}
startRow++;
}

row = sheet.GetRow(startRow);
int cellCount = row.LastCellNum;//列数
if (columnCount != null)
{
cellCount = columnCount.Value;
}
//构建datatable的列
if (isColumnHead)
{
for (int i = row.FirstCellNum; i < cellCount; ++i)
{
cell = row.GetCell(i);
if (cell != null)
{
if (cell.StringCellValue != null)
{
column = new DataColumn(cell.StringCellValue);
dataTable.Columns.Add(column);
}
}
else
{
column = new DataColumn("column" + (i + 1));
dataTable.Columns.Add(column);
}
}
startRow++;
}
else
{
for (int i = row.FirstCellNum; i < cellCount; ++i)
{
column = new DataColumn("column" + (i + 1));
dataTable.Columns.Add(column);
}
}

//填充行
for (int i = startRow; i <= rowCount; ++i)
{
string strMSG = "";
try
{
row = sheet.GetRow(i);
if (row == null || row.FirstCellNum < 0) continue;

dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
try
{
cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
}
else
{
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
if (format == 14 || format == 31 || format == 57 || format == 58)
dataRow[j] = cell.DateCellValue;
else
dataRow[j] = cell.NumericCellValue;
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
}
}
}
catch (Exception ex)
{
strMSG = ",第" + j + "列";
throw ex;
}
}
dataTable.Rows.Add(dataRow);
}
catch (Exception ex)
{
strMSG = "第" + i + "行" + strMSG;
throw new Exception("读取" + strMSG.TrimStart(',') + "时发生异常。原因：" + ex.Message);
}
}
}
}
}
}
return dataTable;
}
catch (Exception ex)
{
JHLog.Log.Writelog("读取Excel发生异常，原因：" + ex.ToString());
throw ex;
}
}

/// <summary>
/// 将datatable导出到excel
/// </summary>
/// <param name="columns">数据源及列信息 key:导出数据，value:显示名称，绑定字段，宽度，列是否合并,列是否正序排序（null-不排序，true-正序，false-倒序）</param>
/// <param name="filePath">保存路径</param>
public static void DataSetToExcel(List<ExcelInfo> excelInfoList, string filePath)
{
FileStream fs = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
ICellStyle cellStyle = null;
try
{
if (excelInfoList == null || excelInfoList.Count == 0)
{
throw new Exception("没有导出的数据源");
}
// 2007版本
if (filePath.ToLower().IndexOf(".xlsx") > 0)
workbook = new XSSFWorkbook();
// 2003版本
else if (filePath.ToLower().IndexOf(".xls") > 0)
workbook = new HSSFWorkbook();

if (workbook == null)
{
throw new Exception("只支持导出xlsx格式文件和xls格式文件");
}

foreach (var infoItem in excelInfoList)
{
//依据列排序
string strSort = "";
foreach (var columnInfo in infoItem.ExcelColumnList)
{
if (columnInfo.Sort == ExcelInfo.ExcelColumnSort.ASC && infoItem.DataSource.Columns.Contains(columnInfo.FieldName))
{
strSort += columnInfo.FieldName.ToUpper() + " ASC,";
}
if (columnInfo.Sort == ExcelInfo.ExcelColumnSort.DESC && infoItem.DataSource.Columns.Contains(columnInfo.FieldName))
{
strSort += columnInfo.FieldName.ToUpper() + " DESC,";
}
}
DataTable CurrDt = infoItem.DataSource;
if (!string.IsNullOrEmpty(strSort))
{
infoItem.DataSource.DefaultView.Sort = strSort.TrimEnd(',');
CurrDt = infoItem.DataSource.DefaultView.ToTable();
}
//Sheet
if (string.IsNullOrEmpty(infoItem.SheetCaption) || infoItem.SheetCaption == ExcelInfo.SheetDefaultCaption)
{
infoItem.SheetCaption = ExcelInfo.SheetDefaultCaption + excelInfoList.IndexOf(infoItem);
}
sheet = workbook.CreateSheet(infoItem.SheetCaption);

int iRowTmp = 0;
//标题头
if (infoItem.IsShowTitleHead && !string.IsNullOrEmpty(infoItem.TitleCaption))
{
row = sheet.CreateRow(iRowTmp);
cell = row.CreateCell(0);
cell.SetCellValue(infoItem.TitleCaption);

cellStyle = workbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle = cellStyle;
if (infoItem.ExcelColumnList.Count > 1)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(iRowTmp, iRowTmp, 0, infoItem.ExcelColumnList.Count - 1));
}
iRowTmp++;
}
//列头
if (infoItem.IsShowColumnHead && infoItem.ExcelColumnList != null && infoItem.ExcelColumnList.Count > 0)
{
row = sheet.CreateRow(iRowTmp);
for (int iHead = 0; iHead < infoItem.ExcelColumnList.Count; iHead++)
{
cell = row.CreateCell(iHead);
cell.SetCellValue(infoItem.ExcelColumnList[iHead].Caption);

cellStyle = workbook.CreateCellStyle();
//cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_BLUE.index;
//cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Aqua.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;//FillPatternType.SOLID_FOREGROUND;
cell.CellStyle = cellStyle;
sheet.SetColumnWidth(iHead, infoItem.ExcelColumnList[iHead].Width / 5 * 256);
}
iRowTmp++;
}

//数据
if (infoItem.DataSource != null && infoItem.DataSource.Rows.Count > 0)
{
//Key:ColumnIndex,Value:
Dictionary<int, Tuple<object, int, int?>> dic = new Dictionary<int, Tuple<object, int, int?>>();
for (int iRow = 0; iRow < CurrDt.Rows.Count; iRow++)
{
row = sheet.CreateRow(iRow + iRowTmp);
for (int iCol = 0; iCol < infoItem.ExcelColumnList.Count; iCol++)
{
cell = row.CreateCell(iCol);
if (CurrDt.Columns.Contains(infoItem.ExcelColumnList[iCol].FieldName) && CurrDt.Rows[iRow][infoItem.ExcelColumnList[iCol].FieldName] != null)
{
if (infoItem.ExcelColumnList[iCol].IsMerged == true)
{
//合并
if (!dic.ContainsKey(iCol))
{
dic.Add(iCol, new Tuple<object, int, int?>(CurrDt.Rows[iRow][infoItem.ExcelColumnList[iCol].FieldName], iRow + iRowTmp, null));
cell.SetCellValue(CurrDt.Rows[iRow][infoItem.ExcelColumnList[iCol].FieldName].ToString());

cellStyle = workbook.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cell.CellStyle = cellStyle;

}
else
{
if (dic[iCol].Item1.Equals(CurrDt.Rows[iRow][infoItem.ExcelColumnList[iCol].FieldName]))
{
dic[iCol] = new Tuple<object, int, int?>(dic[iCol].Item1, dic[iCol].Item2, iRow + iRowTmp);
if (iRow == CurrDt.Rows.Count - 1)
{
if (dic[iCol].Item3.HasValue)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(dic[iCol].Item2, dic[iCol].Item3.Value, iCol, iCol));
}
}
}
else
{
if (dic[iCol].Item3.HasValue)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(dic[iCol].Item2, dic[iCol].Item3.Value, iCol, iCol));
}

dic[iCol] = new Tuple<object, int, int?>(CurrDt.Rows[iRow][infoItem.ExcelColumnList[iCol].FieldName], iRow + iRowTmp, null);
cell.SetCellValue(CurrDt.Rows[iRow][infoItem.ExcelColumnList[iCol].FieldName].ToString());

cellStyle = workbook.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cell.CellStyle = cellStyle;
}
}
}
else
{
cell.SetCellValue(CurrDt.Rows[iRow][infoItem.ExcelColumnList[iCol].FieldName].ToString());
}
}
}
}
}
}

// 转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();

//保存为Excel文件
using (fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
catch (Exception ex)
{
if (fs != null)
{
fs.Close();
}
throw ex;
}
}

public static void DataSetToExcel(DataSet ds, string filePath)
{
List<ExcelInfo> excelInfoList = new List<ExcelInfo>();
foreach (DataTable item in ds.Tables)
{
ExcelInfo columns = new ExcelInfo()
{
DataSource = item,
IsShowColumnHead = true,
IsShowTitleHead = false,
SheetCaption = item.TableName,
};

foreach (DataColumn dc in item.Columns)
{
columns.ExcelColumnList.Add(new ExcelInfo.ExcelColumnInfo()
{
Caption = dc.Caption,
FieldName = dc.ColumnName,
Width = 100,
IsMerged = false,
});
}
excelInfoList.Add(columns);
}

DataSetToExcel(excelInfoList, filePath);
}

/// <summary>
/// Excel信息
/// </summary>
public class ExcelInfo
{
public static readonly string SheetDefaultCaption = "sheet";
private string sheetCaption = SheetDefaultCaption;
/// <summary>
/// Sheet名称
/// </summary>
public string SheetCaption
{
get { return sheetCaption; }
set { sheetCaption = value; }
}

/// <summary>
/// Title名称
/// </summary>
public string TitleCaption { get; set; }

private bool isShowTitleHead = false;
/// <summary>
/// 是否显示标题头
/// </summary>
public bool IsShowTitleHead
{
get { return isShowTitleHead; }
set { isShowTitleHead = value; }
}

private List<ExcelColumnInfo> excelColumnList = new List<ExcelColumnInfo>();
/// <summary>
/// 列信息
/// </summary>
public List<ExcelColumnInfo> ExcelColumnList
{
get { return excelColumnList; }
set { excelColumnList = value; }
}

private bool isShowColumnHead = true;
/// <summary>
/// 是否显示列头
/// </summary>
public bool IsShowColumnHead
{
get { return isShowColumnHead; }
set { isShowColumnHead = value; }
}

/// <summary>
/// 数据源
/// </summary>
public DataTable DataSource { get; set; }

/// <summary>
/// Excel列信息
/// </summary>
public class ExcelColumnInfo
{
/// <summary>
/// 显示名称
/// </summary>
public string Caption { get; set; }
/// <summary>
/// 绑定字段
/// </summary>
public string FieldName { get; set; }

private int width = 100;
/// <summary>
/// 宽度
/// </summary>
public int Width
{
get { return width; }
set { width = value; }
}
/// <summary>
/// 是否合并
/// </summary>
public bool IsMerged { get; set; }
/// <summary>
/// 排序
/// </summary>
public ExcelColumnSort? Sort { get; set; }
}
/// <summary>
/// Excel列排序
/// </summary>
public enum ExcelColumnSort
{
/// <summary>
/// 正序
/// </summary>
ASC = 1,
/// <summary>
/// 倒序
/// </summary>
DESC = 2,
}
}
}
}