超级会员免费看
本文介绍了C#中使用NPOI库进行Excel导入导出的基本操作，包括创建Sheet、设置单元格数据格式、单元格合并及风格设置，并提供了一个ExcelHelper帮助类的封装示例，方便在业务系统开发中高效处理Excel文件。
一、NPOI简介
1.1、NPOI是什么
NPOI是POI的.NET版本,POI是一套用Java写成的库，我们在开发中经常用到导入导出表格、文档的情况，NPOI能够帮助我们在没有安装微软Office的情况下读写Office文件，如xls, doc, ppt等。NPOI采用的是Apache 2.0许可证（poi也是采用这个许可证），这意味着它可以被用于任何商业或非商业项目，我们不用担心因为使用它而必须开放你自己的源代码，所以它对于很多从事业务系统开发的公司来说绝对是很不错的选择。
1.2、NPOI简单使用
NPOI的API十分人性化，使用起来非常容易上手。首先创建一个控制台应用程序，通过nuget获取NPOI，这里使用的是最新版本2.5.4，如下：
1.2.1、创建Excel的Sheet和给单元格添加数据【基础1】
//创建Sheet和给单元格添加数据【基础1】
private void CreateSheetAndCell_BaseOne(string filePathAndName)
{
if (string.IsNullOrEmpty(filePathAndName)) return;
//创建workbook，说白了就是在内存中创建一个Excel文件
IWorkbook workbook = new HSSFWorkbook();
//要添加至少一个sheet,没有sheet的excel是打不开的
ISheet sheet1 = workbook.CreateSheet("sheet1");
ISheet sheet2 = workbook.CreateSheet("sheet2");
IRow row1 = sheet1.CreateRow(0);//添加第1行,注意行列的索引都是从0开始的
ICell cell1 = row1.CreateCell(0);//给第1行添加第1个单元格
cell1.SetCellValue("hello npoi!");//给单元格赋值
//上边3个步骤合在一起：sheet1.CreateRow(0).CreateCell(0).SetCellValue("hello npoi");
//获取第一行第一列的string值
Console.WriteLine(sheet1.GetRow(0).GetCell(0).StringCellValue); //输出：hello npoi
//写入文件
using (FileStream file = new FileStream(filePathAndName, FileMode.Create))
{
workbook.Write(file);
}
//导出成功提示
ExportSuccessTips(filePathAndName);
}运行后在选择的路径创建对应的Excel文件且格式如下：
二、NPOI使用详解
2.1 单元格数据格式
用过Excel的人都知道，单元格是Excel最有意义的东西，我们做任何操作恐怕都要和单元格打交道。在Excel中我们经常要设置格式，比如说日期格式（2018-1-1）、小数点格式（1.20）、货币格式（$2000）、百分比格式（99.99%）等等，这些东西在过去我们恐怕只能在服务器端生成好，不但增加了服务器端的代码量，还造成了不必要的字符串替换操作，如今NPOI将让服务器从这种完全没有必要的操作中解放出来，一切都将由Excel在客户端处理。
使用NPOI时要注意，所有的格式都是通过 CellStyle.DataFormat 赋给单元格的，而不是直接赋给单元格，设置单元格数据展示的格式有两种方法。
2.1.1、设置excel中内置的格式：
excel内部设置了很多格式，如下图所示：
如果我们想使用这些内置的格式，可用通过 DataFormat.GetBuildinFormat('格式') 来使用，使用实例如下：
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); //两位小数，内置格式2.1.2、设置自定义格式
有时候内置的格式不能满足我们的要求，这时候就可以使用 workbook.CreateDataFormat().GetFormat("格式") 自定义格式了，使用语法如下：
cellStyle.DataFormat= workbook.CreateDataFormat().GetFormat("yyyy年m月d日 hh时mm分ss秒");//显示中文日期和时间，自定义格式 //导出数据到Excel基础_单元格格式转换
private void ExportExcel_CellFormat(string filePathAndName)
{
if (string.IsNullOrEmpty(filePathAndName)) return;
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("new sheet");
//设置日期格式，2018年5月5日格式
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue(new DateTime(2018, 5, 5, 11, 31, 56));
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy年m月d日 hh时mm分ss秒");
//cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/mm/dd hh:mm:ss");
cell.CellStyle = cellStyle;
//保留2位小数
ICell cell2 = sheet.CreateRow(1).CreateCell(0);
cell2.SetCellValue(12.346666);
ICellStyle cellStyle2 = workbook.CreateCellStyle();
cellStyle2.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cell2.CellStyle = cellStyle2;
//货币格式
ICell cell3 = sheet.CreateRow(2).CreateCell(0);
cell3.SetCellValue(1234.66666);
ICellStyle cellStyle3 = workbook.CreateCellStyle();
cellStyle3.DataFormat = workbook.CreateDataFormat().GetFormat("￥#,##0.00");//美元的话格式为 $#,##0.00，其中#，##0表示千分号
cell3.CellStyle = cellStyle3;
//百分比
ICell cell4 = sheet.CreateRow(3).CreateCell(0);
cell4.SetCellValue(1.236666);
ICellStyle cellStyle4 = workbook.CreateCellStyle();
//cellStyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");//保留两位小数
cellStyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("0%");
cell4.CellStyle = cellStyle4;
//中文大写数字
ICell cell5 = sheet.CreateRow(4).CreateCell(0);
cell5.SetCellValue(111);
ICellStyle cellStyle5 = workbook.CreateCellStyle();
cellStyle5.DataFormat = workbook.CreateDataFormat().GetFormat("[DbNum2][$-804]0.00");//不保留小数： [DbNum2][$-804]0
cell5.CellStyle = cellStyle5;
//科学计数法
ICell cell6 = sheet.CreateRow(5).CreateCell(0);
cell6.SetCellValue(1234.6666);
ICellStyle cellStyle6 = workbook.CreateCellStyle();
cellStyle6.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cell6.CellStyle = cellStyle6;
using (FileStream file = new FileStream(filePathAndName, FileMode.Create))
{
workbook.Write(file);
}
//导出成功提示
ExportSuccessTips(filePathAndName);
}运行程序生成的Excel文件效果如下：
2.2、单元格合并及设置风格
单元格的合并和风格这部分内容比较多，但是Api十分的简单，为了看起来清晰，我们分成两个栗子来演示这部分的功能，代码中的注释很详细，这里就直接上代码了
2.2.1、第一个栗子演示单元格合并，内容停靠，字体风格颜色大小等
//导出数据到Excel基础_单元格合并及风格设置1
private void ExportExcel_MergeCellStyleSettingsOne(string filePathAndName)
{
if (string.IsNullOrEmpty(filePathAndName)) return;
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
//------------------单元格合并
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 5));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCol
ICell cell = sheet.CreateRow(1).CreateCell(1);
cell.SetCellValue("合并的区域");
cell.Row.HeightInPoints = 40;//行高
ICellStyle cellstyle = workbook.CreateCellStyle();
//----------------设置单元格常用风格
cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//水平居左，可选Right,Center
cellstyle.VerticalAlignment = VerticalAlignment.Bottom;//垂直居中,可选Top,Center
cellstyle.WrapText = true;//自动换行
cellstyle.Indention = 4;//缩进4个字节
//cellstyle.Rotation = 90;//字体旋转90度，取值范围是[-90,90]
//---------------字体，字体大小、颜色
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 24;//字号为24，可以用font.FontHeight = 24 * 20;FontHeight的单位是1/20点
font.FontName = "幼圆";//字体
font.Boldweight = 700;//加粗
font.Color = HSSFColor.Blue.Index;//字体颜色
cellstyle.SetFont(font);
cell.CellStyle = cellstyle;
using (FileStream file = new FileStream(filePathAndName, FileMode.Create))
{
workbook.Write(file);
}
//导出成功提示
ExportSuccessTips(filePathAndName);
}生成的excel文件如下图所示：
2.2.2、第二个栗子演示单元格的宽高、背景色、边框风格等
//导出数据到Excel基础_单元格合并及风格设置2
private void ExportExcel_MergeCellStyleSettingsTwo(string filePathAndName)
{
if (string.IsNullOrEmpty(filePathAndName)) return;
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
ICell cell = sheet.CreateRow(1).CreateCell(1);
cell.SetCellValue("hello");
ICellStyle cellstyle = workbook.CreateCellStyle();
//设置宽度，把第2列设成20字节宽；参数1指定列的索引；参数2指定宽度，单位是1/256个字符宽度
sheet.SetColumnWidth(1, 20 * 256);
//高度设成40，单位是点；也可以用cell2.Row.Height = 40 * 20;Height的单位是1/20点
cell.Row.HeightInPoints = 40;
//右边框设置成点画线
cellstyle.BorderRight = NPOI.SS.UserModel.BorderStyle.MediumDashDot;
//右边框设成粉色
cellstyle.RightBorderColor = HSSFColor.Pink.Index;
//前景色填充模式，实心
cellstyle.FillPattern = FillPattern.SolidForeground;
//前景色填充颜色，黄色
cellstyle.FillForegroundColor = HSSFColor.Yellow.Index;
//背景色
//cellstyle2.FillBackgroundColor = HSSFColor.Green.Index;//背景色设成green
cell.CellStyle = cellstyle;
using (FileStream file = new FileStream(filePathAndName, FileMode.Create))
{
workbook.Write(file);
}
//导出成功提示
ExportSuccessTips(filePathAndName);
}运行程序，生成的excel文件如下：
其实在NPOI中除了操作单元格的内容和风格外，还提供了添加excel文件摘要，添加批注，画图，使用公式等功能，但是这些功能我们用的比较少，所以就不一一展示了，NPOI的公式我自己很少去使用，一般通过程序直接计算结果，个人感觉在程序中计算更加灵活，当excel的结构发生变化时修改起来也更方便些。
三、封装的ExcelHelper帮助类
3.1、使用ExcelHelper帮助类导出文件
使用Nuget添加几个包：NPOI，log4net(其他的日志记录组件也可以)，System.Text.Encoding.CodePages(为了支持gb2312)，具体代码如下：
//导出数据到Excel
private void ExportExcel(string filePathAndName)
{
if (string.IsNullOrEmpty(filePathAndName)) return;
//用于支持gb2312
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
//测试dataTable数据源
DataTable dataTable = new DataTable();
dataTable.Columns.Add("id", typeof(int));
dataTable.Columns.Add("uname", typeof(string));
dataTable.Columns.Add("sex", typeof(string));
dataTable.Columns.Add("age", typeof(int));
dataTable.Columns.Add("pwd", typeof(string));
dataTable.Columns.Add("email", typeof(string));
dataTable.Columns.Add("address", typeof(string));
Random r = new Random();
for (int i = 0; i < 1000; i++)
{
DataRow row = dataTable.NewRow();
row["id"] = i;
row["uname"] = "hellox" + i.ToString();
row["sex"] = r.Next(2) % 2 == 0 ? "男" : "女";
row["age"] = r.Next(40) + 18;
row["pwd"] = "pwd" + r.Next(5000).ToString();
row["email"] = r.Next(100000) + "@qq.com";
row["address"] = $"北京市，西{r.Next(4) + 1}环，xx路{r.Next(100)}号";
dataTable.Rows.Add(row);
}
//DataTable的列名和excel的列名对应字典，因为excel的列名一般是中文的，DataTable的列名是英文的，字典主要是存储excel和DataTable列明的对应关系，当然我们也可以把这个对应关系存在配置文件或者其他地方
Dictionary<string, string> dir = new Dictionary<string, string>();
dir.Add("id", "编号");
dir.Add("uname", "用户");
dir.Add("sex", "性别");
dir.Add("age", "年龄");
dir.Add("pwd", "密码");
dir.Add("email", "邮箱");
dir.Add("address", "住址");
//使用helper类导出DataTable数据到excel表格中,参数依次是 （DataTable数据源; excel表名; excel存放位置的绝对路径; 列名对应字典; 是否清空以前的数据，设置为false，表示内容追加; 每个sheet放的数据条数,如果超过该条数就会新建一个sheet存储）
ExcelHelper.ExportDTtoExcel(dataTable, "考勤信息表", filePathAndName, dir, false, 400);
//导出成功提示
ExportSuccessTips(filePathAndName);
}运行程序，当一个sheet的数目满了后会新生产一个sheet1来存储，效果如下：
3.2、使用ExcelHelper帮助类导入文件
//从Excel中导入数据到界面
private void ImportUIfFromExcel(string filePathAndName)
{
if (string.IsNullOrEmpty(filePathAndName)) return;
//用于支持gb2312
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
//DataTable的列名和excel的列名对应字典
Dictionary<string, string> dir = new Dictionary<string, string>();
dir.Add("id", "编号");
dir.Add("uname", "用户");
dir.Add("sex", "性别");
dir.Add("age", "年龄");
dir.Add("pwd", "密码");
dir.Add("email", "邮箱");
dir.Add("address", "住址");
//读取数据到DataTable，参数依此是（excel文件路径，列名对应字典，列名所在行，sheet索引）
DataTable dt = ExcelHelper.ImportExceltoDt(filePathAndName, dir, 1, 0);
//遍历DataTable---------------------------
string tmpInfo = null;
foreach (DataColumn item in dt.Columns)
{
//显示dataTable的列名
tmpInfo+=(item.ColumnName + new string('-', 10));
}
tmpInfo += "\r\n";
//遍历DataTable中的数据
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
tmpInfo+=(row[i].ToString() + new string(' ', 10));
}
tmpInfo += "\r\n";
}
//显示数据
ShowInfo(tmpInfo);
//导入成功提示
ImportSuccessTips(filePathAndName);
}运行程序后的效果如下：
3.3、ExcelHelper帮助类
兼容xls和xlsx两种格式
/***
* Title："基础工具" 项目
* 主题：Excel帮助类
* Description：
* 功能：
* 1、导出数据到Excel文件中
* 2、将Excel文件的数据导入到DataTable中
* Date：2021
* Version：0.1版本
* Author：Coffee
* Modify Recoder：
*/
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using System.Linq;
using NPOI.SS.Util;
using System.Text.RegularExpressions;
using NPOI.XSSF.UserModel;
namespace Utils
{
public class ExcelHelper
{
#region 从DataTable导出到excel文件中，支持xls和xlsx格式
#region 导出为xls文件内部方法
/// <summary>
/// 从datatable 中导出到excel
/// </summary>
/// <param name="strFileName">excel文件名</param>
/// <param name="dtSource">datatabe源数据</param>
/// <param name="strHeaderText">表名</param>
/// <param name="sheetnum">sheet的编号</param>
/// <returns></returns>
static MemoryStream ExportDT(String strFileName, DataTable dtSource, string strHeaderText, Dictionary<string, string> dir, int sheetnum)
{
//创建工作簿和sheet
IWorkbook workbook = new HSSFWorkbook();
using (Stream writefile = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read))
{
if (writefile.Length > 0 && sheetnum > 0)
{
workbook = WorkbookFactory.Create(writefile);
}
}
ISheet sheet = null;
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
if (workbook.GetSheetIndex(sheetName) >= 0)
{
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
}
sheet = workbook.CreateSheet(sheetName);
#region 表头及样式
{
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
rowIndex = 1;
}
#endregion
#region 列头及样式
if (rowIndex == 1)
{
IRow headerRow = sheet.CreateRow(1);//第二行设置列名
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//写入列标题
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
}
rowIndex = 2;
}
#endregion
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
//数字字符串
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue(drValue.ToString());
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
}
#endregion
#region 导出为xlsx文件内部方法
/// <summary>
/// 从datatable 中导出到excel
/// </summary>
/// <param name="dtSource">datatable数据源</param>
/// <param name="strHeaderText">表名</param>
/// <param name="fs">文件流</param>
/// <param name="readfs">内存流</param>
/// <param name="sheetnum">sheet索引</param>
static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs, MemoryStream readfs, Dictionary<string, string> dir, int sheetnum)
{
IWorkbook workbook = new XSSFWorkbook();
if (readfs.Length > 0 && sheetnum > 0)
{
workbook = WorkbookFactory.Create(readfs);
}
ISheet sheet = null;
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 表头及样式
{
string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
if (workbook.GetSheetIndex(sheetName) >= 0)
{
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
}
sheet = workbook.CreateSheet(sheetName);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue(drValue.ToString());
break;
}
}
#endregion
rowIndex++;
}
workbook.Write(fs);
fs.Close();
}
#endregion
#region 导出excel表格
/// <summary>
/// DataTable导出到Excel文件，xls文件
/// </summary>
/// <param name="dtSource">数据源</param>
/// <param name="strHeaderText">表名</param>
/// <param name="strFileName">excel文件名</param>
/// <param name="dir">datatable和excel列名对应字典</param>
/// <param name="sheetRow">每个sheet存放的行数</param>
public static void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName, Dictionary<string, string> dir, bool isNew, int sheetRow = 50000)
{
int currentSheetCount = GetSheetNumber(strFileName);//现有的页数sheetnum
if (sheetRow <= 0)
{
sheetRow = dtSource.Rows.Count;
}
string[] temp = strFileName.Split('.');
string fileExtens = temp[temp.Length - 1];
int sheetCount = (int)Math.Ceiling((double)dtSource.Rows.Count / sheetRow);//sheet数目
if (temp[temp.Length - 1] == "xls" && dtSource.Columns.Count < 256 && sheetRow < 65536)
{
if (isNew)
{
currentSheetCount = 0;
}
for (int i = currentSheetCount; i < currentSheetCount + sheetCount; i++)
{
DataTable pageDataTable = dtSource.Clone();
int hasRowCount = dtSource.Rows.Count - sheetRow * (i - currentSheetCount) < sheetRow ? dtSource.Rows.Count - sheetRow * (i - currentSheetCount) : sheetRow;
for (int j = 0; j < hasRowCount; j++)
{
pageDataTable.ImportRow(dtSource.Rows[(i - currentSheetCount) * sheetRow + j]);
}
using (MemoryStream ms = ExportDT(strFileName, pageDataTable, strHeaderText, dir, i))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
}
else
{
if (temp[temp.Length - 1] == "xls")
strFileName = strFileName + "x";
if (isNew)
{
currentSheetCount = 0;
}
for (int i = currentSheetCount; i < currentSheetCount + sheetCount; i++)
{
DataTable pageDataTable = dtSource.Clone();
int hasRowCount = dtSource.Rows.Count - sheetRow * (i - currentSheetCount) < sheetRow ? dtSource.Rows.Count - sheetRow * (i - currentSheetCount) : sheetRow;
for (int j = 0; j < hasRowCount; j++)
{
pageDataTable.ImportRow(dtSource.Rows[(i - currentSheetCount) * sheetRow + j]);
}
FileStream readfs = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read);
MemoryStream readfsm = new MemoryStream();
readfs.CopyTo(readfsm);
readfs.Close();
using (FileStream writefs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
ExportDTI(pageDataTable, strHeaderText, writefs, readfsm, dir, i);
}
readfsm.Close();
}
}
}
#endregion
#endregion
#region 从excel文件中将数据导出到datatable/datatable
/// <summary>
/// 将制定sheet中的数据导出到datatable中
/// </summary>
/// <param name="sheet">需要导出的sheet</param>
/// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
/// <param name="dir">excel列名和DataTable列名的对应字典</param>
/// <returns></returns>
static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, Dictionary<string, string> dir)
{
DataTable table = new DataTable();
IRow headerRow;
int cellCount;
try
{
//没有标头或者不需要表头用excel列的序号（1,2,3..）作为DataTable的列名
if (HeaderRowIndex < 0)
{
headerRow = sheet.GetRow(0);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
//有表头，使用表头做为DataTable的列名
else
{
headerRow = sheet.GetRow(HeaderRowIndex);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
//如果excel某一列列名不存在：以该列的序号作为Datatable的列名，如果DataTable中包含了这个序列为名的列，那么列名为重复列名+序号
if (headerRow.GetCell(i) == null)
{
if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
//excel中的某一列列名不为空，但是重复了：对应的Datatable列名为“重复列名+序号”
else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
//正常情况，列名存在且不重复：用excel中的列名作为datatable中对应的列名
{
string colName = dir.Where(s => s.Value == headerRow.GetCell(i).ToString()).First().Key;
DataColumn column = new DataColumn(colName);
table.Columns.Add(column);
}
}
}
int rowCount = sheet.LastRowNum;
for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)//excel行遍历
{
try
{
IRow row;
if (sheet.GetRow(i) == null)//如果excel有空行，则添加缺失的行
{
row = sheet.CreateRow(i);
}
else
{
row = sheet.GetRow(i);
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)//excel列遍历
{
try
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.String://字符串
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = default(string);
}
break;
case CellType.Numeric://数字
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))//时间戳数字
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.Formula://公式
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
catch (Exception exception)
{
//loger.Error(exception.ToString());
}
}
table.Rows.Add(dataRow);
}
catch (Exception exception)
{
//loger.Error(exception.ToString());
}
}
}
catch (Exception exception)
{
//loger.Error(exception.ToString());
}
return table;
}
/// <summary>
/// 读取Excel文件特定名字sheet的内容到DataTable
/// </summary>
/// <param name="strFileName">excel文件路径</param>
/// <param name="sheet">需要导出的sheet</param>
/// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
/// <param name="dir">excel列名和DataTable列名的对应字典</param>
/// <returns></returns>
public static DataTable ImportExceltoDt(string strFileName, Dictionary<string, string> dir, string SheetName, int HeaderRowIndex = 1)
{
DataTable table = new DataTable();
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (file.Length > 0)
{
IWorkbook wb = WorkbookFactory.Create(file);
ISheet isheet = wb.GetSheet(SheetName);
table = ImportDt(isheet, HeaderRowIndex, dir);
isheet = null;
}
}
return table;
}
/// <summary>
/// 读取Excel文件某一索引sheet的内容到DataTable
/// </summary>
/// <param name="strFileName">excel文件路径</param>
/// <param name="sheet">需要导出的sheet序号</param>
/// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
/// <param name="dir">excel列名和DataTable列名的对应字典</param>
/// <returns></returns>
public static DataTable ImportExceltoDt(string strFileName, Dictionary<string, string> dir, int HeaderRowIndex = 1, int SheetIndex = 0)
{
DataTable table = new DataTable();
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (file.Length > 0)
{
IWorkbook wb = WorkbookFactory.Create(file);
ISheet isheet = wb.GetSheetAt(SheetIndex);
table = ImportDt(isheet, HeaderRowIndex, dir);
isheet = null;
}
}
return table;
}
#endregion
/// <summary>
/// 获取excel文件的sheet数目
/// </summary>
/// <param name="outputFile"></param>
/// <returns></returns>
public static int GetSheetNumber(string outputFile)
{
int number = 0;
using (FileStream readfile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Read))
{
if (readfile.Length > 0)
{
IWorkbook wb = WorkbookFactory.Create(readfile);
number = wb.NumberOfSheets;
}
}
return number;
}
/// <summary>
/// 判断内容是否是数字
/// </summary>
/// <param name="message"></param>
/// <param name="result"></param>
/// <returns></returns>
public static bool isNumeric(String message, out double result)
{
Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");
result = -1;
if (rex.IsMatch(message))
{
result = double.Parse(message);
return true;
}
else
return false;
}
/// <summary>
/// 验证导入的Excel是否有数据
/// </summary>
/// <param name="excelFileStream"></param>
/// <returns></returns>
public static bool HasData(Stream excelFileStream)
{
using (excelFileStream)
{
IWorkbook workBook = new HSSFWorkbook(excelFileStream);
if (workBook.NumberOfSheets > 0)
{
ISheet sheet = workBook.GetSheetAt(0);
return sheet.PhysicalNumberOfRows > 0;
}
}
return false;
}
}//Class_end
}
3.4、导入导出提示
//导出成功提示
private void ExportSuccessTips(string filePathAndName)
{
if (string.IsNullOrEmpty(filePathAndName)) return;
MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (MessageBox.Show("保存成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
System.Diagnostics.Process.Start(filePathAndName);
}
}
//导入成功提示
private void ImportSuccessTips(string filePathAndName)
{
if (string.IsNullOrEmpty(filePathAndName)) return;
MessageBox.Show("导入成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//信息提示
private void ShowInfo(string info)
{
memoEdit1.Text = info;
}
扫一扫
4141
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
