using System;
|
using System.Collections.Generic;
|
using System.Text;
|
using System.IO;
|
using NPOI.HSSF.UserModel;
|
using System.Data;
|
using System.Drawing;
|
using NPOI.HSSF.Util;
|
using NPOI.SS.UserModel;
|
using NPOI.HPSF;
|
using NPOI.XSSF.UserModel;
|
|
namespace WebAPI.Controllers.SCGL.日计划管理
|
{
|
/// <summary>
|
/// Excel文件到DataSet的转换类
|
/// </summary>
|
public class NpoiHelper
|
{
|
#region 读取Excel文件内容转换为DataSet
|
|
int ReadExcelEndRow = 0;
|
/// <summary>
|
/// 读取Excel文件内容转换为DataSet,列名依次为 "c0"……c[columnlength-1]
|
/// </summary>
|
/// <param name="FileName">文件绝对路径</param>
|
/// <param name="startRow">数据开始行数(1为第一行)</param>
|
/// <param name="ColumnDataType">每列的数据类型</param>
|
/// <param name="numType">获取数据簿的类型</param>
|
/// <param name="name">工作簿的名字</param>
|
/// <returns></returns>
|
public DataSet ReadExcel(string FileName, int startRow,int numType,string name, params NpoiDataType[] ColumnDataType)
|
{
|
string colNamePix = "F";
|
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格式
|
//for (int k = 0; k < 14; k++)
|
ISheet sheet = null ; //{
|
if (numType == 1)//numType==1 获取工作簿按照下标
|
{
|
sheet = workbook.GetSheetAt(int.Parse(name));//得到里面第一个sheet
|
}
|
else if (numType == 2)//numType==12 获取工作簿按照 工作簿的名字
|
{
|
sheet = workbook.GetSheet(name);//得到里面第一个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, MissingCellPolicy.CREATE_NULL_AS_BLANK);
|
ColumnDataType[i] = GetCellDataType(hs); //NpoiDataType.Blank;
|
//if (i == 6)
|
//{
|
// ColumnDataType[i] = NpoiDataType.Numeric;
|
//}
|
}
|
}
|
#endregion
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
Type tp = GetDataTableType(ColumnDataType[j]);
|
//if (j == 6)
|
//{
|
// tp = typeof(double);
|
//}
|
dt.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
|
ReadExcelEndRow = ReadExcelEndRow == 0 ? sheet.PhysicalNumberOfRows : ReadExcelEndRow;
|
for (int i = startRow - 1; i <= ReadExcelEndRow; i++)
|
{
|
row = sheet.GetRow(i);//得到第i行
|
if (row == null) continue;
|
try
|
{
|
dr = dt.NewRow();
|
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = 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//读取多个sheet
|
public DataSet ReadExcelSub(string FileName, int startRow, params NpoiDataType[] ColumnDataType)
|
{
|
string colNamePix = "F";
|
int ertime = 0;
|
int intime = 0;
|
DataSet ds = new DataSet("ds");
|
DataTable dt0 = new DataTable("dt0");
|
DataTable dt1 = new DataTable("dt1");
|
DataTable dt2 = new DataTable("dt2");
|
DataTable dt3 = new DataTable("dt3");
|
DataTable dt4 = new DataTable("dt4");
|
DataTable dt5 = new DataTable("dt5");
|
DataTable dt6 = new DataTable("dt6");
|
DataTable dt7 = new DataTable("dt7");
|
DataTable dt8 = new DataTable("dt8");
|
DataTable dt9 = new DataTable("dt9");
|
DataTable dt10 = new DataTable("dt10");
|
DataTable dt11 = new DataTable("dt11");
|
DataTable dt12 = new DataTable("dt12");
|
DataTable dt13 = new DataTable("dt13");
|
|
|
DataRow dr;
|
StringBuilder sb = new StringBuilder();
|
using (FileStream stream = new FileStream(@FileName, FileMode.Open, FileAccess.Read))
|
{
|
|
|
//根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
|
|
HSSFWorkbook workbook = new HSSFWorkbook(stream);
|
//XSSFWorkbook workbook = new XSSFWorkbook(stream);
|
int x = workbook.NumberOfSheets;
|
|
List<string> sheetNames = new List<string>();
|
|
for (int i = 0; i < x; i++)
|
{
|
|
//sheetNames.Add(workbook.Workbook.GetSheetName(i));
|
ISheet sheet = workbook.GetSheetAt(i);
|
int j;
|
IRow row;
|
#region ColumnDataType赋值
|
if (ColumnDataType.Length <= 0)
|
{
|
row = sheet.GetRow(startRow - 1);//得到第i行
|
ColumnDataType = new NpoiDataType[row.LastCellNum];
|
for (int a = 0; x < row.LastCellNum; a++)
|
{
|
ICell hs = row.GetCell(a, MissingCellPolicy.CREATE_NULL_AS_BLANK);
|
ColumnDataType[a] = GetCellDataType(hs); //NpoiDataType.Blank;
|
|
}
|
}
|
#endregion
|
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
Type tp = GetDataTableType(ColumnDataType[j]);//得到每一列的类型
|
ReadExcelEndRow = 0;
|
if (i == 0)
|
{
|
dt0.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
else if (i == 1)
|
{
|
dt1.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
else if (i == 2)
|
{
|
dt2.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
else if (i == 3)
|
{
|
dt3.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
else if (i == 4)
|
{
|
dt4.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
else if (i == 5)
|
{
|
dt5.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
else if (i == 6)
|
{
|
dt6.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
else if (i == 7)
|
{
|
dt7.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
else if (i == 8)
|
{
|
dt8.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
else if (i == 9)
|
{
|
dt9.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
else if (i == 10)
|
{
|
dt10.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
else if (i == 11)
|
{
|
dt11.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
else if (i == 12)
|
{
|
dt12.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
else if (i == 13)
|
{
|
dt13.Columns.Add(colNamePix + (j + 1), tp);
|
}
|
}
|
|
ReadExcelEndRow = ReadExcelEndRow == 0 ? sheet.PhysicalNumberOfRows : ReadExcelEndRow;
|
for (int a = startRow - 1; a <= ReadExcelEndRow; a++)
|
{
|
row = sheet.GetRow(a);//得到第i行
|
if (row == null) continue;
|
try
|
{
|
if (i == 0)
|
{
|
dr = dt0.NewRow();
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt0.Rows.Add(dr);
|
intime++;
|
}
|
else if (i == 1)
|
{
|
dr = dt1.NewRow();
|
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt1.Rows.Add(dr);
|
intime++;
|
}
|
else if (i == 2)
|
{
|
dr = dt2.NewRow();
|
|
|
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt2.Rows.Add(dr);
|
intime++;
|
}
|
else if (i == 3)
|
{
|
dr = dt3.NewRow();
|
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt3.Rows.Add(dr);
|
intime++;
|
}
|
else if (i == 4)
|
{
|
dr = dt4.NewRow();
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt4.Rows.Add(dr);
|
intime++;
|
}
|
else if (i == 5)
|
{
|
dr = dt5.NewRow();
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt5.Rows.Add(dr);
|
intime++;
|
}
|
else if (i == 6)
|
{
|
dr = dt6.NewRow();
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt6.Rows.Add(dr);
|
intime++;
|
}
|
else if (i == 7)
|
{
|
dr = dt7.NewRow();
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt7.Rows.Add(dr);
|
intime++;
|
}
|
else if (i == 8)
|
{
|
dr = dt8.NewRow();
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt8.Rows.Add(dr);
|
intime++;
|
}
|
else if (i == 9)
|
{
|
dr = dt9.NewRow();
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt9.Rows.Add(dr);
|
intime++;
|
}
|
else if (i == 10)
|
{
|
dr = dt10.NewRow();
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt10.Rows.Add(dr);
|
intime++;
|
}
|
else if (i == 11)
|
{
|
dr = dt11.NewRow();
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt11.Rows.Add(dr);
|
intime++;
|
}
|
else if (i == 12)
|
{
|
dr = dt12.NewRow();
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt12.Rows.Add(dr);
|
intime++;
|
}
|
else if (i == 13)
|
{
|
dr = dt13.NewRow();
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (j + 1)] = GetCellData(ColumnDataType[j], row, j);
|
}
|
|
dt13.Rows.Add(dr);
|
intime++;
|
}
|
|
}
|
catch (Exception er)
|
{
|
ertime++;
|
sb.Append(string.Format("第{0}行出错：{1}\r\n", i + 1, er.Message));
|
continue;
|
}
|
}
|
if (i == 0)
|
{
|
ds.Tables.Add(dt0);
|
}
|
else if (i == 1)
|
{
|
ds.Tables.Add(dt1);
|
}
|
else if (i == 2)
|
{
|
ds.Tables.Add(dt2);
|
}
|
else if (i == 3)
|
{
|
ds.Tables.Add(dt3);
|
}
|
else if (i == 4)
|
{
|
ds.Tables.Add(dt4);
|
}
|
else if (i == 5)
|
{
|
ds.Tables.Add(dt5);
|
}
|
else if (i == 6)
|
{
|
ds.Tables.Add(dt6);
|
}
|
else if (i == 7)
|
{
|
ds.Tables.Add(dt7);
|
}
|
else if (i == 8)
|
{
|
ds.Tables.Add(dt8);
|
}
|
else if (i == 9)
|
{
|
ds.Tables.Add(dt9);
|
}
|
else if (i == 10)
|
{
|
ds.Tables.Add(dt10);
|
}
|
else if (i == 11)
|
{
|
ds.Tables.Add(dt11);
|
}
|
else if (i == 12)
|
{
|
ds.Tables.Add(dt12);
|
}
|
else if (i == 13)
|
{
|
ds.Tables.Add(dt13);
|
}
|
|
}
|
//IWorkbook workbook = WorkbookFactory.Create(stream);//使用接口，自动识别excel2003/2007格式
|
//ISheet sheet = workbook.GetSheetAt(0);//得到里面第一个sheet
|
|
}
|
//if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
|
return ds;
|
}
|
#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);
|
public List<DataSet> ReadExcelBySheetIndex(string FileName, int startRow, string str)
|
{
|
List<DataSet> listDs = new List<DataSet>();
|
|
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(SheetIndex);//得到里面第一个sheet
|
|
List<string> listSheet = GetSheetNames(workbook);
|
ISheet sheet;
|
|
foreach (string sheetName in listSheet)
|
{
|
DataSet ds = new DataSet("ds");
|
DataTable dt = new DataTable("dt");
|
sheet = workbook.GetSheet(sheetName);
|
if (!sheetName.Contains(str))
|
{
|
continue;
|
}
|
NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0);
|
int cellCount = headerRow.LastCellNum;
|
|
for (int j = 0; j < cellCount; j++)
|
{
|
NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
|
dt.Columns.Add("F" + j.ToString());
|
}
|
|
int r = sheet.PhysicalNumberOfRows;
|
for (int i = (sheet.FirstRowNum); i <= 2900; i++)
|
{
|
NPOI.SS.UserModel.IRow row = null;
|
try
|
{
|
row = sheet.GetRow(i);
|
int ex = row.FirstCellNum;
|
}
|
catch (Exception)
|
{
|
continue;
|
}
|
DataRow dataRow = dt.NewRow();
|
|
for (int j = row.FirstCellNum; j < cellCount; j++)
|
{
|
try
|
{
|
if (row.GetCell(j) != null || row.GetCell(j).ToString() != "")
|
//dataRow[j] = row.GetCell(j).ToString();
|
dataRow[j] = GetCellData(row, j);
|
}
|
catch (Exception ex)
|
{
|
string s = ex.ToString();
|
}
|
}
|
dt.Rows.Add(dataRow);
|
}
|
ds.Tables.Add(dt);
|
listDs.Add(ds);
|
}
|
// if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
|
}
|
|
return listDs;
|
|
}
|
public List<string> GetSheetNames(IWorkbook workbook)
|
{
|
int x = workbook.NumberOfSheets;
|
List<string> sheetNames = new List<string>();
|
for (int i = 0; i < x; i++)
|
{
|
sheetNames.Add(workbook.GetSheetName(i));
|
}
|
return sheetNames;
|
}
|
public object GetCellData(IRow row, int column)
|
{
|
try
|
{
|
ICell hs = row.GetCell(column);
|
NpoiDataType datatype = GetCellDataType(hs); //NpoiDataType.Blank;
|
object obj = row.GetCell(column) ?? null;
|
if (datatype == NpoiDataType.Datetime)
|
{
|
string v = "";
|
try
|
{
|
v = row.GetCell(column).StringCellValue;
|
}
|
catch (Exception e1)
|
{
|
v = row.GetCell(column).DateCellValue.ToString("yyyy-MM-dd hh:mm:ss");
|
}
|
if (v != "")
|
{
|
try
|
{
|
obj = row.GetCell(column).DateCellValue.ToString("yyyy-MM-dd hh:mm:ss");
|
|
}
|
catch (Exception e2)
|
{
|
obj = Convert.ToDateTime(v).ToString("yyyy-MM-dd hh:mm:ss");
|
}
|
}
|
else
|
obj = DBNull.Value;
|
|
|
}
|
else if (datatype == NpoiDataType.Numeric)
|
{
|
obj = DBNull.Value;
|
try
|
{
|
//if (row.GetCell(column).StringCellValue != "")
|
obj = row.GetCell(column).NumericCellValue;
|
}
|
catch (Exception e3)
|
{
|
obj = row.GetCell(column).StringCellValue;
|
}
|
|
}
|
else if (datatype == NpoiDataType.String && hs.CellType == CellType.Formula)
|
{
|
obj = row.GetCell(column).StringCellValue;
|
}
|
return obj;
|
}
|
catch (Exception e22)
|
{
|
return "";
|
}
|
}
|
#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 = FillPattern.SolidForeground;
|
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;//单元格式设置样式
|
|
cell.SetCellType(CellType.String);
|
cell.SetCellValue(columnValue.ToString());
|
|
|
}
|
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 = FillPattern.SolidForeground;
|
style1.SetFont(font1);//样式里的字体设置具体的字体样式
|
style1.Alignment = HorizontalAlignment.Center;//文字水平对齐方式
|
style1.VerticalAlignment = VerticalAlignment.Center;//文字垂直对齐方式
|
//row.HeightInPoints = 25;
|
row.HeightInPoints = 15;
|
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;//单元格式设置样式
|
|
//cell.SetCellType(CellType.STRING);
|
cell.SetCellValue(columnValue.ToString());
|
}
|
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;
|
int colounwidth = 3000;
|
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.Indexed;
|
}
|
}
|
else
|
s = XlColour.Indexed;
|
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)
|
{
|
var objcell = row.GetCell(column);
|
|
object obj = objcell ?? null;
|
if (obj == null)
|
{
|
return null;
|
}
|
|
if (obj.ToString().Contains("月") && obj.ToString().Contains("-") && objcell.DateCellValue != null)
|
{
|
datatype = NpoiDataType.Datetime;
|
}
|
else if (objcell.CellType == CellType.Numeric && (obj.ToString().Contains("-") || obj.ToString().Contains("/")) && objcell.DateCellValue != null)
|
{
|
datatype = NpoiDataType.Datetime;
|
}
|
|
|
if (datatype == NpoiDataType.Datetime)
|
{
|
string v = "";
|
try
|
{
|
v = row.GetCell(column).StringCellValue.Trim();
|
}
|
catch (Exception e1)
|
{
|
v = row.GetCell(column).DateCellValue.ToString("yyyy-MM-dd hh:mm:ss");
|
}
|
if (v != "")
|
{
|
try
|
{
|
obj = row.GetCell(column).DateCellValue.ToString("yyyy-MM-dd hh:mm:ss");
|
|
}
|
catch (Exception e2)
|
{
|
obj = Convert.ToDateTime(v).ToString("yyyy-MM-dd hh:mm:ss");
|
}
|
}
|
else
|
obj = DBNull.Value;
|
|
}
|
else if (datatype == NpoiDataType.Numeric)
|
{
|
obj = DBNull.Value;
|
try
|
{
|
//if (row.GetCell(column).StringCellValue != "")
|
obj = row.GetCell(column).NumericCellValue;
|
}
|
catch (Exception e3)
|
{
|
obj = row.GetCell(column).StringCellValue.Trim();
|
}
|
|
}
|
else
|
{
|
if (obj != null)
|
{
|
obj = obj.ToString().Trim().Replace("\n", "");//去掉回车，ligerui tree不支持回车
|
if (obj.ToString().StartsWith("."))
|
{
|
|
decimal.Parse("0" + obj.ToString());
|
obj = "0" + obj.ToString();
|
}
|
}
|
}
|
return obj;
|
}
|
|
private object GetCellData_double(NpoiDataType datatype, IRow row, int column)
|
{
|
return (object)(row.GetCell(column).NumericCellValue) ?? null;
|
}
|
#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.String;
|
if (hs.NumericCellValue.ToString().Contains("-") || hs.NumericCellValue.ToString().Contains("/") || hs.ToString().Contains("-") || hs.ToString().Contains("/"))
|
{
|
hs.DateCellValue.ToString();
|
dtype = NpoiDataType.Datetime;
|
}
|
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:
|
{
|
dtype = NpoiDataType.String;
|
try
|
{
|
if (hs.CachedFormulaResultType == CellType.Numeric && hs.NumericCellValue.ToString() != "")
|
{
|
dtype = NpoiDataType.Numeric;
|
cellvalue = hs.NumericCellValue.ToString();
|
}
|
else if (hs.CachedFormulaResultType == CellType.Numeric && hs.DateCellValue.ToString() != "")
|
{
|
dtype = NpoiDataType.Datetime;
|
cellvalue = hs.DateCellValue.ToString();
|
}
|
else if (hs.RichStringCellValue.ToString() != "")
|
{
|
dtype = NpoiDataType.String;
|
cellvalue = hs.RichStringCellValue.ToString();
|
}
|
}
|
catch
|
{
|
try
|
{
|
if (hs.CachedFormulaResultType == CellType.Numeric && hs.NumericCellValue.ToString() != "")
|
{
|
dtype = NpoiDataType.Numeric;
|
cellvalue = hs.NumericCellValue.ToString();
|
}
|
}
|
catch
|
{
|
//cellvalue = hs.StringCellValue;
|
}
|
}
|
|
break;
|
}
|
default:
|
dtype = NpoiDataType.Datetime;
|
break;
|
}
|
//if (cellvalue != "" && DateTime.TryParse(cellvalue, out t1)) dtype = NpoiDataType.Datetime;
|
return dtype;
|
}
|
#endregion
|
|
|
#region 测试代码
|
|
|
#endregion
|
|
|
public DataSet ReadExcel_ByColNum(string FileName, int startRow, int LastCellNum, params NpoiDataType[] ColumnDataType)
|
{
|
string colNamePix = "F";
|
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[LastCellNum];
|
for (int i = 0; i < LastCellNum; i++)
|
{
|
ICell hs = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
|
ColumnDataType[i] = GetCellDataType(hs); //NpoiDataType.Blank;
|
}
|
}
|
#endregion
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
Type tp = GetDataTableType(ColumnDataType[j]);
|
dt.Columns.Add(colNamePix + j, tp);
|
}
|
|
ReadExcelEndRow = ReadExcelEndRow == 0 ? sheet.PhysicalNumberOfRows : ReadExcelEndRow;
|
int ss = sheet.LastRowNum + 1;
|
if (ReadExcelEndRow < ss && ReadExcelEndRow > 0)
|
{
|
ReadExcelEndRow = ss;
|
}
|
for (int i = startRow - 1; i <= ReadExcelEndRow; i++)
|
{
|
row = sheet.GetRow(i);//得到第i行
|
if (row == null) continue;
|
try
|
{
|
dr = dt.NewRow();
|
|
for (j = 0; j < ColumnDataType.Length; j++)
|
{
|
dr[colNamePix + (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;
|
}
|
|
}
|
|
#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
|
|
}
|
