/// <summary>
/// 读Excel-根据NpoiDataType创建的DataTable列的数据类型
/// </summary>
/// <param name="datatype"></param>
/// <returns></returns>
private Type GetDataTableType(NpoiDataType datatype)
{
Type tp = typeof(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);
}
private static void SetCellValue(string value, ref ICell cell, NpoiDataType type)
{
switch (type)
{
//字串沒有格式
case NpoiDataType.String:
if (!String.IsNullOrWhiteSpace(value))
{
cell.SetCellValue(value);
}
break;
//轉日期
case NpoiDataType.Date:
if (!String.IsNullOrWhiteSpace(value))
{
cell.SetCellValue(Convert.ToDateTime(value));
}
break;
//轉數字
case NpoiDataType.Number:
if (!String.IsNullOrWhiteSpace(value))
{
cell.SetCellValue(Convert.ToDouble(value));
}
break;
//不會發生;
default:
break;
}
}
/// <summary>
///
/// </summary>
/// <param name="row"></param>
/// <param name="column"></param>
/// <returns></returns>
public static 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("");
}
}
/// <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("");
}
}
/// <summary>
/// 读Excel-得到不同数据类型单元格的数据
/// </summary>
/// <param name="datatype">数据类型</param>
/// <param name="row">数据中的一行</param>
/// <param name="column">哪列</param>
/// <returns></returns>
private static object GetCellData(NpoiDataType datatype, IRow row, int column)
{
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;
}
}
if (datatype == NpoiDataType.Numeric)
{
obj = DBNull.Value;
try
{
//if (row.GetCell(column).StringCellValue != "")
obj = row.GetCell(column).NumericCellValue;
}
catch (Exception e3)
{
//Errors.WriteError(obj.ToString() + "!" + e3.ToString());
obj = row.GetCell(column).StringCellValue;
}
}
return(obj);
}
/// <summary>
/// 設定欄位值、屬性
/// </summary>
/// <param name="value"></param>
/// <param name="cell"></param>
/// <param name="dataType"></param>
private static void SetCellValue(string value, ref ICell cell, NpoiDataType dataType)
{
switch (dataType)
{
case NpoiDataType.String:
if (!string.IsNullOrWhiteSpace(value))
{
cell.SetCellValue(value);
}
break;
case NpoiDataType.Int:
if (!string.IsNullOrWhiteSpace(value))
{
cell.SetCellValue(Convert.ToDouble(value));
}
break;
case NpoiDataType.Double:
if (!string.IsNullOrWhiteSpace(value))
{
cell.SetCellValue(Convert.ToDouble(value));
}
break;
case NpoiDataType.DateTime:
if (!string.IsNullOrWhiteSpace(value))
{
cell.SetCellValue(Convert.ToDateTime(value));
}
break;
case NpoiDataType.Bool:
if (!string.IsNullOrWhiteSpace(value))
{
cell.SetCellValue(Convert.ToBoolean(value));
}
break;
default:
break;
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
NpoiDataType[] ColumnDataType = null;
try
{
using (FileStream stream = new FileStream(@fileName, FileMode.Open, FileAccess.Read))
{
workbook = WorkbookFactory.Create(stream);//使用接口，自动识别excel2003/2007格式
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
data.Columns.Add(column);
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
if (ColumnDataType == null)
{
ColumnDataType = new NpoiDataType[cellCount];
for (int j = 0; j < cellCount; j++)
{
ICell hs = row.GetCell(j);
if (hs != null)
{
ColumnDataType[j] = GetCellDataType(hs);
}
}
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
ICell hs = row.GetCell(j);
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
if (hs.CellType == CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(hs))
{
dataRow[j] = HSSFDateUtil.GetJavaDate(hs.NumericCellValue).ToString("HH:mm:ss");
}
else
{
dataRow[j] = hs;
}
//dataRow[j] = row.GetCell(j).ToString();
}
//dataRow[j] = GetCellData(ColumnDataType[j], row, j);
}
data.Rows.Add(dataRow);
}
}
}
return(data);
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return(null);
}
}
/// <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, int sheetCount, params NpoiDataType[] ColumnDataType)
{
int ertime = 0;
int intime = 0;
DataSet ds = new DataSet("ds");
StringBuilder sb = new StringBuilder();
using (FileStream stream = new FileStream(@FileName, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = WorkbookFactory.Create(stream);//使用接口，自动识别excel2003/2007格式
for (int c = 0; c < sheetCount; c++)
{
ISheet sheet = workbook.GetSheetAt(c);//得到里面第一个sheet
DataTable dt = new DataTable(string.Format("dt{0}", c));
DataRow dr;
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[c].Rows.Count == 0 && sb.ToString() != "")
{
throw new Exception(sb.ToString());
}
}
}
return(ds);
}
/// <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 = NPOI.HSSF.Util.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; //单元格式设置样式
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)
{
Console.WriteLine("文件保存失败！" + SaveFileName);
return(false);
}
}
