2.0.1npoi的dll文件下载：
NPOI2.0.1.zip
调用测试：
public static void test1()
{
NpoiHelper np = new NpoiHelper();
DataTable dt1 = np.ReadExcel(AppDomain.CurrentDomain.BaseDirectory + "1测试数据.xls", 2).Tables[0];//读2003格式数据
DataSet ds1 = new DataSet();
ds1.Tables.Add(dt1.Copy());
ds1.AcceptChanges();
string SaveFileName = "output1.xls";
np.CreateExcel2003(SaveFileName, "sheet001", ds1, 0);//写2003格式数据
}
public static void test2()
{
NpoiHelper np = new NpoiHelper();
DataTable dt1 = np.ReadExcel(AppDomain.CurrentDomain.BaseDirectory + "2测试数据.xlsx", 2).Tables[0];//读2007格式数据
DataSet ds1 = new DataSet();
ds1.Tables.Add(dt1.Copy());
ds1.AcceptChanges();
string SaveFileName = "output2.xlsx";
np.CreateExcel2007(SaveFileName, "sheet001", ds1, 0);//写2007格式数据
Console.ReadKey();
}
公共操作Excel类NpoiHelper：
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using NPOI.HSSF.UserModel;
using System.Data;
using System.Collections;
using System.Drawing;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.HPSF;
using NPOI.XSSF.UserModel;
namespace Tools.Common
{
/// <summary>
/// Excel文件到DataSet的转换类
/// </summary>
public class NpoiHelper
{
#region 读取Excel文件内容转换为DataSet
/// <summary>
/// 读取Excel文件内容转换为DataSet,列名依次为 "c0"……c[columnlength-1]
/// </summary>
/// <param name="FileName">文件绝对路径</param>
/// <param name="startRow">数据开始行数(1为第一行)</param>
/// <param name="ColumnDataType">每列的数据类型</param>
/// <returns></returns>
public DataSet ReadExcel(string FileName, int startRow, params NpoiDataType[] ColumnDataType)
{
int ertime = 0;
int intime = 0;
DataSet ds = new DataSet("ds");
DataTable dt = new DataTable("dt");
DataRow dr;
StringBuilder sb = new StringBuilder();
using (FileStream stream = new FileStream(@FileName, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = WorkbookFactory.Create(stream);//使用接口，自动识别excel2003/2007格式
ISheet sheet = workbook.GetSheetAt(0);//得到里面第一个sheet
int j;
IRow row;
#region ColumnDataType赋值
if (ColumnDataType.Length <= 0)
{
row = sheet.GetRow(startRow - 1);//得到第i行
ColumnDataType = new NpoiDataType[row.LastCellNum];
for (int i = 0; i < row.LastCellNum; i++)
{
ICell hs = row.GetCell(i);
ColumnDataType[i] = GetCellDataType(hs);
}
}
#endregion
for (j = 0; j < ColumnDataType.Length; j++)
{
Type tp = GetDataTableType(ColumnDataType[j]);
dt.Columns.Add("c" + j, tp);
}
for (int i = startRow - 1; i <= sheet.PhysicalNumberOfRows; i++)
{
row = sheet.GetRow(i);//得到第i行
if (row == null) continue;
try
{
dr = dt.NewRow();
for (j = 0; j < ColumnDataType.Length; j++)
{
dr["c" + j] = GetCellData(ColumnDataType[j], row, j);
}
dt.Rows.Add(dr);
intime++;
}
catch (Exception er)
{
ertime++;
sb.Append(string.Format("第{0}行出错：{1}\r\n", i + 1, er.Message));
continue;
}
}
ds.Tables.Add(dt);
}
if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
return ds;
}
#endregion#region 读取上传 Excel 文件的第一个Sheet转换成 Datatable
public DataTable ReadExcelFromPost(HttpPostedFileBase file, int startRow, params NpoiDataType[] columnDataType)
{
int ertime = 0;
int intime = 0;
DataTable table = new DataTable();
DataRow dr;
IWorkbook workBook = WorkbookFactory.Create(file.InputStream);
ISheet sheet = workBook.GetSheetAt(0);
int j;
IRow row;
#region ColumnDataType赋值
if (columnDataType.Length <= 0)
{
row = sheet.GetRow(startRow - 1); //得到第i行
columnDataType = new NpoiDataType[row.LastCellNum];
for (int i = 0; i < row.LastCellNum; i++)
{
ICell hs = row.GetCell(i);
columnDataType[i] = GetCellDataType(hs);
}
}
#endregion
for (j = 0; j < columnDataType.Length; j++)
{
Type tp = GetDataTableType(columnDataType[j]);
table.Columns.Add("c" + j, tp);
}
for (int i = startRow - 1; i <= sheet.PhysicalNumberOfRows; i++)
{
row = sheet.GetRow(i);//得到第i行
if (row == null) continue;
// 判断行有否数据
bool IsHasData = false;
for (int k = 0; k < columnDataType.Length; k++)
{
var d = GetCellData(columnDataType[k], row, k);
if (d != null && !string.IsNullOrEmpty((string)d))
{
IsHasData = true;
break;
}
}
if (!IsHasData) continue;
try
{
dr = table.NewRow();
for (j = 0; j < columnDataType.Length; j++)
{
dr["c" + j] = GetCellData(columnDataType[j], row, j);
//// 注意空行
//if (row.Cells.Count > j)
//{
// dr["c" + j] = GetCellData(columnDataType[j], row, j);
//}
//else
//{
// dr["c" + j] = null;
//}
}
table.Rows.Add(dr);
intime++;
}
catch (Exception er)
{
ertime++;
throw new Exception(string.Format("第{0}行出错：{1}\r\n", i + 1, er.Message));
}
}
file.InputStream.Close();
file.InputStream.Dispose();
return table;
}
#endregion
#region 读取上传 Excel 文件的转换成 DataTable（指定读取的页码数,从第一页sheet开始）
/// <summary>
/// 读取上传 Excel 文件的指定的Sheet转换成 DataTable
/// </summary>
/// <param name="file">上传的Excel文件类</param>
/// <param name="startRow">数据起始行数</param>
/// <param name="sheetIndex">需要读取的Sheet页码数（由0开始）</param>
/// <param name="columnDataType">设置返回DataTable的列类型</param>
/// <returns></returns>
public DataSet ReadExcelFromPost(HttpPostedFileBase file, int startRow, int sheetNum, params NpoiDataType[] columnDataType)
{
DataSet ds = new DataSet();
IWorkbook workBook = WorkbookFactory.Create(file.InputStream);
for (int sheetIndex = 0; sheetIndex <= sheetNum; sheetIndex++)
{
int ertime = 0;
int intime = 0;
DataTable table = new DataTable();
DataRow dr;
ISheet sheet = workBook.GetSheetAt(sheetIndex);
int j;
IRow row;
#region ColumnDataType赋值
if (columnDataType.Length <= 0)
{
row = sheet.GetRow(startRow - 1); //得到第i行
columnDataType = new NpoiDataType[row.LastCellNum];
for (int i = 0; i < row.LastCellNum; i++)
{
ICell hs = row.GetCell(i);
columnDataType[i] = GetCellDataType(hs);
}
}
#endregion
for (j = 0; j < columnDataType.Length; j++)
{
Type tp = GetDataTableType(columnDataType[j]);
table.Columns.Add("c" + j, tp);
}
for (int i = startRow - 1; i <= sheet.PhysicalNumberOfRows; i++)
{
row = sheet.GetRow(i);//得到第i行
if (row == null) continue;
// 判断行有否数据
bool IsHasData = false;
for (int k = 0; k < columnDataType.Length; k++)
{
var d = GetCellData(columnDataType[k], row, 0);
if (d != null && !string.IsNullOrEmpty((string)d))
{
IsHasData = true;
}
}
if (!IsHasData) continue;
try
{
dr = table.NewRow();
for (j = 0; j < columnDataType.Length; j++)
{
dr["c" + j] = GetCellData(columnDataType[j], row, j);
}
table.Rows.Add(dr);
intime++;
}
catch (Exception er)
{
ertime++;
throw new Exception(string.Format("第{0}页,第{1}行出错：{2}\r\n", sheetIndex, i + 1, er.Message));
}
}
ds.Tables.Add(table);
}
file.InputStream.Close();
file.InputStream.Dispose();
return ds;
}
#endregion
#region 读取上传 Csv 文件转换成 Datatable
/// <summary>
/// 读取上传 Csv 文件转换成 Datatable
/// </summary>
/// <param name="importedExcel">上传的Excel文件类</param>
/// <param name="skipLineCount">忽略掉前skipLineCount行，即从第skipLineCount+1行开始</param>
/// <param name="dataTypes">列类型数组</param>
/// <param name="columnsName">列名数组</param>
/// <returns></returns>
public DataTable ReadCsvFromPost(HttpPostedFileBase importedExcel, int skipLineCount, Type[] dataTypes, string[] columnsName = null)
{
DataTable dt = new DataTable();
for (int i = 0; i < dataTypes.Length; i++)
{
if (columnsName != null)
{
dt.Columns.Add(columnsName[i], dataTypes[i]);
}
else
{
dt.Columns.Add(i.ToString(), dataTypes[i]);
}
}
int lineCount = 1;
using (StreamReader sr = new StreamReader(importedExcel.InputStream, Encoding.Default))
{
string line;
while (true)
{
line = sr.ReadLine();
if (string.IsNullOrWhiteSpace(line))
{
break;
}
lineCount++;
if (lineCount > skipLineCount)
{
string[] split = line.Split(',');
//当第一列为空时退出csv读取
if (string.IsNullOrWhiteSpace(split[0]))
{
break;
}
// if (split.Length > dataTypes.Length) continue; //列数大于数据表的属于无效行，忽略
dt.Rows.Add(split);
}
}
}
return dt;
}
#endregion
Color LevelOneColor = Color.Green;
Color LevelTwoColor = Color.FromArgb(201, 217, 243);
Color LevelThreeColor = Color.FromArgb(231, 238, 248);
Color LevelFourColor = Color.FromArgb(232, 230, 231);
Color LevelFiveColor = Color.FromArgb(250, 252, 213);
#region 从DataSet导出到MemoryStream流2003
/// <summary>
/// 从DataSet导出到MemoryStream流2003
/// </summary>
/// <param name="SaveFileName">文件保存路径</param>
/// <param name="SheetName">Excel文件中的Sheet名称</param>
/// <param name="ds">存储数据的DataSet</param>
/// <param name="startRow">从哪一行开始写入，从0开始</param>
/// <param name="datatypes">DataSet中的各列对应的数据类型</param>
public bool CreateExcel2003(string SaveFileName, string SheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes)
{
try
{
if (startRow < 0) startRow = 0;
HSSFWorkbook wb = new HSSFWorkbook();
wb = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "pkm";
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Title =
si.Subject = "automatic genereted document";
si.Author = "pkm";
wb.DocumentSummaryInformation = dsi;
wb.SummaryInformation = si;
ISheet sheet = wb.CreateSheet(SheetName);
//sheet.SetColumnWidth(0, 50 * 256);
//sheet.SetColumnWidth(1, 100 * 256);
IRow row;
ICell cell;
DataRow dr;
int j;
int maxLength = 0;
int curLength = 0;
object columnValue;
DataTable dt = ds.Tables[0];
if (datatypes.Length < dt.Columns.Count)
{
datatypes = new NpoiDataType[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
string dtcolumntype = dt.Columns[i].DataType.Name.ToLower();
switch (dtcolumntype)
{
case "string": datatypes[i] = NpoiDataType.String;
break;
case "datetime": datatypes[i] = NpoiDataType.Datetime;
break;
case "boolean": datatypes[i] = NpoiDataType.Bool;
break;
case "double": datatypes[i] = NpoiDataType.Numeric;
break;
default: datatypes[i] = NpoiDataType.String;
break;
}
}
}
#region 创建表头
row = sheet.CreateRow(0);//创建第i行
ICellStyle style1 = wb.CreateCellStyle();//样式
IFont font1 = wb.CreateFont();//字体
font1.Color = HSSFColor.WHITE.index;//字体颜色
font1.Boldweight = (short)FontBoldWeight.BOLD;//字体加粗样式
//style1.FillBackgroundColor = HSSFColor.WHITE.index;//GetXLColour(wb, LevelOneColor);// 设置图案色
style1.FillForegroundColor = HSSFColor.GREEN.index;//GetXLColour(wb, LevelOneColor);// 设置背景色
style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
style1.SetFont(font1);//样式里的字体设置具体的字体样式
style1.Alignment = HorizontalAlignment.CENTER;//文字水平对齐方式
style1.VerticalAlignment = VerticalAlignment.CENTER;//文字垂直对齐方式
row.HeightInPoints = 25;
for (j = 0; j < dt.Columns.Count; j++)
{
columnValue = dt.Columns[j].ColumnName;
curLength = Encoding.Default.GetByteCount(columnValue.ToString());
maxLength = (maxLength < curLength ? curLength : maxLength);
int colounwidth = 256 * maxLength;
sheet.SetColumnWidth(j, colounwidth);
try
{
cell = row.CreateCell(j);//创建第0行的第j列
cell.CellStyle = style1;//单元格式设置样式
try
{
cell.SetCellType(CellType.STRING);
cell.SetCellValue(columnValue.ToString());
}
catch { }
}
catch
{
continue;
}
}
#endregion
#region 创建每一行
for (int i = startRow; i < ds.Tables[0].Rows.Count; i++)
{
dr = ds.Tables[0].Rows[i];
row = sheet.CreateRow(i + 1);//创建第i行
for (j = 0; j < dt.Columns.Count; j++)
{
columnValue = dr[j];
curLength = Encoding.Default.GetByteCount(columnValue.ToString());
maxLength = (maxLength < curLength ? curLength : maxLength);
int colounwidth = 256 * maxLength;
sheet.SetColumnWidth(j, colounwidth);
try
{
cell = row.CreateCell(j);//创建第i行的第j列
#region 插入第j列的数据
try
{
NpoiDataType dtype = datatypes[j];
switch (dtype)
{
case NpoiDataType.String:
{
cell.SetCellType(CellType.STRING);
cell.SetCellValue(columnValue.ToString());
} break;
case NpoiDataType.Datetime:
{
cell.SetCellType(CellType.STRING);
cell.SetCellValue(columnValue.ToString());
} break;
case NpoiDataType.Numeric:
{
cell.SetCellType(CellType.NUMERIC);
cell.SetCellValue(Convert.ToDouble(columnValue));
} break;
case NpoiDataType.Bool:
{
cell.SetCellType(CellType.BOOLEAN);
cell.SetCellValue(Convert.ToBoolean(columnValue));
} break;
case NpoiDataType.Richtext:
{
cell.SetCellType(CellType.FORMULA);
cell.SetCellValue(columnValue.ToString());
} break;
}
}
catch
{
cell.SetCellType(CellType.STRING);
cell.SetCellValue(columnValue.ToString());
}
#endregion
}
catch
{
continue;
}
}
}
#endregion
//using (FileStream fs = new FileStream(@SaveFileName, FileMode.OpenOrCreate))//生成文件在服务器上
//{
// wb.Write(fs);
//}
//string SaveFileName = "output.xls";
using (FileStream fs = new FileStream(@SaveFileName, FileMode.OpenOrCreate, FileAccess.Write))//生成文件在服务器上
{
wb.Write(fs);
Console.WriteLine("文件保存成功！" + SaveFileName);
}
return true;
}
catch (Exception er)
{
Console.WriteLine("文件保存成功！" + SaveFileName);
return false;
}
}
#endregion
#region 从DataSet导出到MemoryStream流2007
/// <summary>
/// 从DataSet导出到MemoryStream流2007
/// </summary>
/// <param name="SaveFileName">文件保存路径</param>
/// <param name="SheetName">Excel文件中的Sheet名称</param>
/// <param name="ds">存储数据的DataSet</param>
/// <param name="startRow">从哪一行开始写入，从0开始</param>
/// <param name="datatypes">DataSet中的各列对应的数据类型</param>
public bool CreateExcel2007(string SaveFileName,string SheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes)
{
try
{
if (startRow < 0) startRow = 0;
XSSFWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet(SheetName);
//sheet.SetColumnWidth(0, 50 * 256);
//sheet.SetColumnWidth(1, 100 * 256);
IRow row;
ICell cell;
DataRow dr;
int j;
int maxLength = 0;
int curLength = 0;
object columnValue;
DataTable dt = ds.Tables[0];
if (datatypes.Length < dt.Columns.Count)
{
datatypes = new NpoiDataType[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
string dtcolumntype = dt.Columns[i].DataType.Name.ToLower();
switch (dtcolumntype)
{
case "string": datatypes[i] = NpoiDataType.String;
break;
case "datetime": datatypes[i] = NpoiDataType.Datetime;
break;
case "boolean": datatypes[i] = NpoiDataType.Bool;
break;
case "double": datatypes[i] = NpoiDataType.Numeric;
break;
default: datatypes[i] = NpoiDataType.String;
break;
}
}
}
#region 创建表头
row = sheet.CreateRow(0);//创建第i行
ICellStyle style1 = wb.CreateCellStyle();//样式
IFont font1 = wb.CreateFont();//字体
font1.Color = HSSFColor.WHITE.index;//字体颜色
font1.Boldweight = (short)FontBoldWeight.BOLD;//字体加粗样式
//style1.FillBackgroundColor = HSSFColor.WHITE.index;//GetXLColour(wb, LevelOneColor);// 设置图案色
style1.FillForegroundColor = HSSFColor.GREEN.index;//GetXLColour(wb, LevelOneColor);// 设置背景色
style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
style1.SetFont(font1);//样式里的字体设置具体的字体样式
style1.Alignment = HorizontalAlignment.CENTER;//文字水平对齐方式
style1.VerticalAlignment = VerticalAlignment.CENTER;//文字垂直对齐方式
row.HeightInPoints = 25;
for (j = 0; j < dt.Columns.Count; j++)
{
columnValue = dt.Columns[j].ColumnName;
curLength = Encoding.Default.GetByteCount(columnValue.ToString());
maxLength = (maxLength < curLength ? curLength : maxLength);
int colounwidth = 256 * maxLength;
sheet.SetColumnWidth(j, colounwidth);
try
{
cell = row.CreateCell(j);//创建第0行的第j列
cell.CellStyle = style1;//单元格式设置样式
try
{
//cell.SetCellType(CellType.STRING);
cell.SetCellValue(columnValue.ToString());
}
catch { }
}
catch
{
continue;
}
}
#endregion
#region 创建每一行
for (int i = startRow; i < ds.Tables[0].Rows.Count; i++)
{
dr = ds.Tables[0].Rows[i];
row = sheet.CreateRow(i + 1);//创建第i行
for (j = 0; j < dt.Columns.Count; j++)
{
columnValue = dr[j];
curLength = Encoding.Default.GetByteCount(columnValue.ToString());
maxLength = (maxLength < curLength ? curLength : maxLength);
int colounwidth = 256 * maxLength;
sheet.SetColumnWidth(j, colounwidth);
try
{
cell = row.CreateCell(j);//创建第i行的第j列
#region 插入第j列的数据
try
{
NpoiDataType dtype = datatypes[j];
switch (dtype)
{
case NpoiDataType.String:
{
//cell.SetCellType(CellType.STRING);
cell.SetCellValue(columnValue.ToString());
} break;
case NpoiDataType.Datetime:
{
// cell.SetCellType(CellType.STRING);
cell.SetCellValue(columnValue.ToString());
} break;
case NpoiDataType.Numeric:
{
//cell.SetCellType(CellType.NUMERIC);
cell.SetCellValue(Convert.ToDouble(columnValue));
} break;
case NpoiDataType.Bool:
{
//cell.SetCellType(CellType.BOOLEAN);
cell.SetCellValue(Convert.ToBoolean(columnValue));
} break;
case NpoiDataType.Richtext:
{
// cell.SetCellType(CellType.FORMULA);
cell.SetCellValue(columnValue.ToString());
} break;
}
}
catch
{
//cell.SetCellType(HSSFCell.CELL_TYPE_STRING);
cell.SetCellValue(columnValue.ToString());
}
#endregion
}
catch
{
continue;
}
}
}
#endregion
//using (FileStream fs = new FileStream(@SaveFileName, FileMode.OpenOrCreate))//生成文件在服务器上
//{
// wb.Write(fs);
//}
//string SaveFileName = "output.xlsx";
using (FileStream fs = new FileStream(SaveFileName, FileMode.OpenOrCreate, FileAccess.Write))//生成文件在服务器上
{
wb.Write(fs);
Console.WriteLine("文件保存成功！" + SaveFileName);
}
return true;
}
catch (Exception er)
{
Console.WriteLine("文件保存失败！" + SaveFileName);
return false;
}
}
#endregion
private short GetXLColour(HSSFWorkbook workbook, System.Drawing.Color SystemColour)
{
short s = 0;
HSSFPalette XlPalette = workbook.GetCustomPalette();
HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);
if (XlColour == null)
{
if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)
{
if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64)
{
//NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE= 64;
//NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1;
XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B);
}
else
{
XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B);
}
s = XlColour.GetIndex();
}
}
else
s = XlColour.GetIndex();
return s;
}
#region 读Excel-根据NpoiDataType创建的DataTable列的数据类型
/// <summary>
/// 读Excel-根据NpoiDataType创建的DataTable列的数据类型
/// </summary>
/// <param name="datatype"></param>
/// <returns></returns>
private Type GetDataTableType(NpoiDataType datatype)
{
Type tp = typeof(string);//Type.GetType("System.String")
switch (datatype)
{
case NpoiDataType.Bool:
tp = typeof(bool);
break;
case NpoiDataType.Datetime:
tp = typeof(DateTime);
break;
case NpoiDataType.Numeric:
tp = typeof(double);
break;
case NpoiDataType.Error:
tp = typeof(string);
break;
case NpoiDataType.Blank:
tp = typeof(string);
break;
}
return tp;
}
#endregion
#region 读Excel-得到不同数据类型单元格的数据
/// <summary>
/// 读Excel-得到不同数据类型单元格的数据
/// </summary>
/// <param name="datatype">数据类型</param>
/// <param name="row">数据中的一行</param>
/// <param name="column">哪列</param>
/// <returns></returns>
private object GetCellData(NpoiDataType datatype, IRow row, int column)
{
switch (datatype)
{
case NpoiDataType.String:
try
{
return row.GetCell(column).DateCellValue;
}
catch
{
try
{
return row.GetCell(column).StringCellValue;
}
catch
{
return row.GetCell(column).NumericCellValue;
}
}
case NpoiDataType.Bool:
try { return row.GetCell(column).BooleanCellValue; }
catch { return row.GetCell(column).StringCellValue; }
case NpoiDataType.Datetime:
try { return row.GetCell(column).DateCellValue; }
catch { return row.GetCell(column).StringCellValue; }
case NpoiDataType.Numeric:
try { return row.GetCell(column).NumericCellValue; }
catch { return row.GetCell(column).StringCellValue; }
case NpoiDataType.Richtext:
try { return row.GetCell(column).RichStringCellValue; }
catch { return row.GetCell(column).StringCellValue; }
case NpoiDataType.Error:
try { return row.GetCell(column).ErrorCellValue; }
catch { return row.GetCell(column).StringCellValue; }
case NpoiDataType.Blank:
try { return row.GetCell(column).StringCellValue; }
catch { return ""; }
default: return "";
}
}
#endregion
#region 获取单元格数据类型
/// <summary>
/// 获取单元格数据类型
/// </summary>
/// <param name="hs"></param>
/// <returns></returns>
private NpoiDataType GetCellDataType(ICell hs)
{
NpoiDataType dtype;
DateTime t1;
string cellvalue = "";
switch (hs.CellType)
{
case CellType.BLANK:
dtype = NpoiDataType.String;
cellvalue = hs.StringCellValue;
break;
case CellType.BOOLEAN:
dtype = NpoiDataType.Bool;
break;
case CellType.NUMERIC:
dtype = NpoiDataType.Numeric;
cellvalue = hs.NumericCellValue.ToString();
break;
case CellType.STRING:
dtype = NpoiDataType.String;
cellvalue = hs.StringCellValue;
break;
case CellType.ERROR:
dtype = NpoiDataType.Error;
break;
case CellType.FORMULA:
default:
dtype = NpoiDataType.Datetime;
break;
}
if (cellvalue != "" && DateTime.TryParse(cellvalue, out t1)) dtype = NpoiDataType.Datetime;
return dtype;
}
#endregion
#region 测试代码
#endregion
}
#region 枚举(Excel单元格数据类型)
/// <summary>
/// 枚举(Excel单元格数据类型)
/// </summary>
public enum NpoiDataType
{
/// <summary>
/// 字符串类型-值为1
/// </summary>
String,
/// <summary>
/// 布尔类型-值为2
/// </summary>
Bool,
/// <summary>
/// 时间类型-值为3
/// </summary>
Datetime,
/// <summary>
/// 数字类型-值为4
/// </summary>
Numeric,
/// <summary>
/// 复杂文本类型-值为5
/// </summary>
Richtext,
/// <summary>
/// 空白
/// </summary>
Blank,
/// <summary>
/// 错误
/// </summary>
Error
}
#endregion
}
另外，常用的单元格合并方法：
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3); sheet.AddMergedRegion(region);还有常用的单元格样式构建方法：
public static ICellStyle CreateCellStyle(IWorkbook workbook, short fontSize = 10, bool isBold = true) { ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //headerStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index; //headerStyle.FillForegroundColor = 44; // NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index; cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index; // NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index; cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; #region 字体 IFont fontHeader = workbook.CreateFont(); fontHeader.FontHeightInPoints = fontSize;//default:10 fontHeader.FontName = "微软雅黑"; if (isBold) { fontHeader.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; } //fontHeader.Color = (short)FontColor.Normal; #endregion cellStyle.SetFont(fontHeader); return cellStyle; }