C# 使用NPOI进行Excel的导入导出
将Excel的数据转换到DataTable
/// <summary>
/// Excel换为DataTable
/// </summary>
/// <param name="FileName">文件名称</param>
/// <param name="sheetName">表名,默认取第一张</param>
/// <returns>DataTable</returns>
private static DataTable ExcelToTable(string FileName, string sheetName)
{
DataTable? dt = new DataTable();
Stream stream = File.Open(FileName, FileMode.Open);
IWorkbook? workbook = null;
try
{
//表格版本判断
if (Path.GetExtension(FileName).Equals(".xls"))
{
workbook = new HSSFWorkbook(stream);
}
else if (Path.GetExtension(FileName).Equals(".xlsx"))
{
workbook = new XSSFWorkbook(stream);
}
ISheet sheet = null;
//获取工作表 默认取第一张
if (string.IsNullOrWhiteSpace(sheetName))
sheet = workbook.GetSheetAt(0);
else
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
return null;
#region 获取表头
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
if (cell != null)
{
dt.Columns.Add(cell.ToString());
}
else
{
dt.Columns.Add("");
}
}
#endregion
#region 获取内容
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
//判断单元格是否为日期格式
if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
if (row.GetCell(j).DateCellValue.Year >= 1970)
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString();
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
dt.Rows.Add(dataRow);
}
#endregion
}
catch (Exception)
{
dt = null;
}
return dt;
}
将DataTable转换成Excel(xls)数据并保存
/// <summary>
/// 将DataTable数据导出到Excel文件中(xls)
/// </summary>
/// <param name="dt"></param>
/// <param name="FileName">路径</param>
/// <param name="tablename">表名</param>
public void TableToExcelForXLS(DataTable dt, string FileName, string? tablename = null)
{
try
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
if (tablename == null)
tablename = "Sheet1";
ISheet sheet = hssfworkbook.CreateSheet(tablename);
int n = 0; //控制表名,在第一行(列名前面一行)显示
if (dt.TableName != null && dt.TableName != "")
{
//表名
//设置一个合并单元格区域,使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为:起始行,结束行,起始列,结束列
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count));
IRow rowtitle = sheet.CreateRow(0);
ICell celltitle = rowtitle.CreateCell(0);
celltitle.SetCellValue(dt.TableName);
//设置单元格样式时需要注意,务必创建一个新的样式对象进行设置,否则会将工作表所有单元格的样式一同设置,它们应该共享的是一个样式对象
ICellStyle style = hssfworkbook.CreateCellStyle();
//设置单元格的样式:水平对齐居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//新建一个字体样式对象
IFont font = hssfworkbook.CreateFont();
//设置字体加粗样式
font.FontHeight = short.MaxValue;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//将新的样式赋给单元格
celltitle.CellStyle = style;
//添加表名之后置为1
n = 1;
}
//列宽数组(自适应列宽)
int[] ColumnWidthArray = new int[dt.Columns.Count];
//表头
IRow row = sheet.CreateRow(n);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
//可以获取中文长度,中文占2个字符
ColumnWidthArray[i] = System.Text.Encoding.Default.GetBytes(dt.Columns[i].ColumnName).Length;
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + n + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
String cellValue = dt.Rows[i][j].ToString();
cell.SetCellValue(cellValue);
if (System.Text.Encoding.Default.GetBytes(cellValue).Length > ColumnWidthArray[j])
{
ColumnWidthArray[j] = System.Text.Encoding.Default.GetBytes(cellValue).Length;
}
}
}
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.SetColumnWidth(i, (ColumnWidthArray[i] + 2) * 256);
}
//转为字节数组
MemoryStream stream = new MemoryStream();
hssfworkbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
catch (Exception)
{
return;
}
}
方法使用示例
DataTable dataTable = new DataTable();
private void excel导入_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
dataTable = ExcelToTable(openFileDialog.FileName, "Sheet1");
DG.DataSource = dataTable;
}
}
private void Excel导出_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "xlxs files(*.xlxs)|*.xlxs|xls files(*.xls)|*.xls|All files(*.*)|*.*";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
TableToExcelForXLS(dataTable, saveFileDialog.FileName, "S1");
}
}
总结
挺好用的 没找到的 xlxs的保存方法,能保存xls也行了
2022/12/2