/// <summary>
/// 将excel内容读到DataTable中
/// </summary>
/// <param name="dt">存放数据DataTable</param>
/// <param name="fileName">excel文件</param>
/// <param name="iSheet">第几sheet页（0开头）</param>
/// <param name="isFirstRowColumn">excel第一行为标题</param>
/// <returns>数据行数</returns>
public static int ReadExcel(ref DataTable dt, string fileName, int iSheet, bool isFirstRowColumn)
{
IWorkbook workbook = null;
FileStream fs = null;
ISheet sheet = null;
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.ToLower().IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.ToLower().IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
//if (sheetName != null)
//{
// sheet = workbook.GetSheet(sheetName);
// if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
// {
// sheet = workbook.GetSheetAt(0);
// }
//}
sheet = workbook.GetSheetAt(iSheet);
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
dt.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
}
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
}
return(dt.Rows.Count);
}
catch (Exception ex)
{
throw new Exception("读取excel失败:" + ex.Message);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
private static MemoryStream Export(DataTable dtSource)
{
XSSFWorkbook workbook = new XSSFWorkbook();
//HSSFWorkbook workbook = new HSSFWorkbook();
//HSSFSheet sheet = workbook.CreateSheet();
ISheet sheet = workbook.CreateSheet();
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 列头及样式
{
IRow headerRow = sheet.CreateRow(0);
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(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
//解决导出excel表中的某个单元格数据过大，显示单元格最大列宽255错误
int colWidth = (arrColWidth[column.Ordinal] + 1) * 256;
if (colWidth < 255 * 256)
{
sheet.SetColumnWidth(column.Ordinal, colWidth < 3000 ? 3000 : colWidth);
}
else
{
sheet.SetColumnWidth(column.Ordinal, 6000);
}
//sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
#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": //字符串类型
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("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
workbook.Close();
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// 从Excel获取值传递到对象的属性里
/// </summary>
/// <param name="distanceType">目标对象类型</param>
/// <param name="sourceCell">对象属性的值</param>
private static Object GetExcelCellToProperty(Type distanceType, ICell sourceCell)
{
object rs = distanceType.IsValueType ? Activator.CreateInstance(distanceType) : null;
// 1.判断传递的单元格是否为空
if (sourceCell == null || string.IsNullOrEmpty(sourceCell.ToString()))
{
return(rs);
}
// 2.Excel文本和数字单元格转换，在Excel里文本和数字是不能进行转换，所以这里预先存值
object sourceValue = null;
switch (sourceCell.CellType)
{
case CellType.Blank:
break;
case CellType.Boolean:
break;
case CellType.Error:
break;
case CellType.Formula:
break;
case CellType.Numeric:
sourceValue = sourceCell.NumericCellValue;
break;
case CellType.String:
sourceValue = sourceCell.StringCellValue;
break;
case CellType.Unknown:
break;
default:
break;
}
string valueDataType = distanceType.Name;
// 在这里进行特定类型的处理
switch (valueDataType.ToLower()) // 以防出错，全部小写
{
case "string":
rs = sourceValue.ToString();
break;
case "int":
case "int16":
case "int32":
rs = (int)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType);
break;
case "float":
case "single":
rs = (float)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType);
break;
case "datetime":
rs = sourceCell.DateCellValue;
break;
case "guid":
rs = (Guid)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType);
return(rs);
}
return(rs);
}
//public void OutputBinayStream(System.IO.FileInfo file, HttpContext context)
//{
// #region Output Binary Stream
// context.Response.Clear();
// context.Response.Charset = "GB2312";
// context.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpContext.Current.Server.UrlEncode(file.Name));
// context.Response.AddHeader("Content-Length", file.Length.ToString());
// context.Response.ContentType = "application/ms-excel";
// context.Response.WriteFile(file.FullName);
// context.Response.End();
// #endregion
//}
#endregion
#region 动态转换成excel
public static bool ToExcelDynamic(string savePath, string SheetName, string HeadName, DataTable table, Dictionary <string, string> excelDataMap)
{
try
{
List <int> dateCellIndex = new List <int>();//日期格式列
bool header = true;
//创建工作薄
HSSFWorkbook wk = new HSSFWorkbook();
//创建一个名称为mySheet的表
ISheet tb = wk.CreateSheet(SheetName);
#region 表头样式
ICellStyle headStyle = wk.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = wk.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 行样式
ICellStyle stylerow = wk.CreateCellStyle();
stylerow.Alignment = HorizontalAlignment.Center;
IFont fontrow = wk.CreateFont();
fontrow.FontName = "宋体";
fontrow.FontHeightInPoints = 12;
stylerow.SetFont(fontrow);
#endregion
#region 日期行样式
ICellStyle dateStyle = wk.CreateCellStyle();
IDataFormat format = wk.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
#endregion
//合并标题行
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelDataMap.Keys.Count));
IRow rowhead = tb.CreateRow(0); //创建一行
rowhead.HeightInPoints = 25; //行高
ICell cellhead = rowhead.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cellhead.CellStyle = headStyle;
cellhead.SetCellValue(HeadName); //写入表头
//SetCellRangeAddress(tb, 0, 0, 1, 20);
int rowIndex = 1;
if (header)
{
IRow row = tb.CreateRow(rowIndex); //创建一行
rowIndex++;
row.HeightInPoints = 25; //行高
int cellIndex = 0; //开始列索引
foreach (var item in excelDataMap)
{
string columsName = item.Value;
tb.SetColumnWidth(cellIndex, 16 * 256); //行宽8个汉字
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.CellStyle = headStyle;
if (columsName == "DEPT_NAME")
{
cell.SetCellValue("单位名称");
}
else
{
cell.SetCellValue(table.Columns[columsName].ToString());//循环往第二行的单元格中添加数据
}
cellIndex++;
}
}
for (int i = 0; i < table.Rows.Count; i++)
{
IRow row = tb.CreateRow(rowIndex + i); //创建一行
row.HeightInPoints = 20; //行高
int cellIndex = 0; //开始列索引
foreach (var item in excelDataMap)
{
string columsName = item.Value;
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.SetCellValue(table.Rows[i][columsName].ToString()); //循环往第二行的单元格中添加数据
cell.CellStyle = stylerow;
cellIndex++;
}
}
using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
{
wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public static bool ToExcelDate(string savePath, string SheetName, string HeadName, IList listJob, string className)
{
try
{
DataTable table = ToDataTable(listJob, className);
List <int> dateCellIndex = new List <int>();//日期格式列
bool header = true;
//创建工作薄
HSSFWorkbook wk = new HSSFWorkbook();
//创建一个名称为mySheet的表
ISheet tb = wk.CreateSheet(SheetName);
#region 表头样式
ICellStyle headStyle = wk.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = wk.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 行样式
ICellStyle stylerow = wk.CreateCellStyle();
stylerow.Alignment = HorizontalAlignment.Center;
IFont fontrow = wk.CreateFont();
fontrow.FontName = "宋体";
fontrow.FontHeightInPoints = 12;
stylerow.SetFont(fontrow);
#endregion
#region 日期行样式
ICellStyle dateStyle = wk.CreateCellStyle();
IDataFormat format = wk.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#endregion
Dictionary <string, string> excelDataMap = ReadXml(className, false);
//合并标题行
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelDataMap.Keys.Count));
IRow rowhead = tb.CreateRow(0); //创建一行
rowhead.HeightInPoints = 25; //行高
ICell cellhead = rowhead.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cellhead.CellStyle = headStyle;
cellhead.SetCellValue(HeadName); //写入表头
//SetCellRangeAddress(tb, 0, 0, 1, 20);
int rowIndex = 1;
if (header)
{
IRow row = tb.CreateRow(rowIndex); //创建一行
rowIndex++;
row.HeightInPoints = 25; //行高
int cellIndex = 0; //开始列索引
#region 弃用
//for (int i = 0; i < table.Columns.Count; i++)
//{
// if (!table.Columns[i].ToString().EndsWith("_wennull"))
// {
// if (table.Columns[i].DataType.ToString() == "System.DateTime")//日期型
// {
// tb.SetColumnWidth(cellIndex, 20 * 256);//行宽10个汉字
// dateCellIndex.Add(cellIndex);
// }
// else
// {
// tb.SetColumnWidth(cellIndex, 16 * 256);//行宽8个汉字
// }
// ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
// cell.CellStyle = headStyle;
// cell.SetCellValue(table.Columns[i].ToString());//循环往第二行的单元格中添加数据
// cellIndex++;
// }
//}
#endregion
foreach (var item in excelDataMap)
{
string columsName = item.Value;
if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型
{
tb.SetColumnWidth(cellIndex, 20 * 256); //行宽10个汉字
dateCellIndex.Add(cellIndex);
}
else
{
tb.SetColumnWidth(cellIndex, 16 * 256); //行宽8个汉字
}
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.CellStyle = headStyle;
cell.SetCellValue(table.Columns[columsName].ToString()); //循环往第二行的单元格中添加数据
cellIndex++;
}
}
for (int i = 0; i < table.Rows.Count; i++)
{
IRow row = tb.CreateRow(rowIndex + i); //创建一行
row.HeightInPoints = 20; //行高
int cellIndex = 0; //开始列索引
#region 弃用
//for (int c = 0; c < table.Columns.Count; c++)
//{
// if (!table.Columns[c].ToString().EndsWith("_wennull"))
// {
// ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
// if (table.Columns[c].DataType.ToString() == "System.DateTime")//日期型
// {
// string value = table.Rows[i][c].ToString();
// if (value != "")
// {
// cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));//循环往第二行的单元格中添加数据
// }
// cell.CellStyle = dateStyle;
// }
// else
// {
// cell.SetCellValue(table.Rows[i][c].ToString());//循环往第二行的单元格中添加数据
// cell.CellStyle = stylerow;
// }
// cellIndex++;
// }
//}
#endregion
foreach (var item in excelDataMap)
{
string columsName = item.Value;
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型
{
string value = table.Rows[i][columsName].ToString();
if (value != "")
{
cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd"));//循环往第二行的单元格中添加数据
}
cell.CellStyle = dateStyle;
}
else
{
cell.SetCellValue(table.Rows[i][columsName].ToString());//循环往第二行的单元格中添加数据
cell.CellStyle = stylerow;
}
cellIndex++;
}
}
//foreach (int cellIndex in dateCellIndex)
//{
// for (int y = table.Rows.Count; y < 500; y++)//至少设置500行格式
// {
// IRow row = tb.CreateRow(rowIndex + y);//创建一行
// row.HeightInPoints = 20; //行高
// ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
// //cell.SetCellValue("");//循环往第二行的单元格中添加数据
// cell.CellStyle = dateStyle;
// }
//}
using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
{
wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
return(true);
}
catch (Exception)
{
return(false);
}
}
/// <summary>
/// excel读取返回DataTable，开始列必须是第一列
/// </summary>
/// <param name="excelPath">excelPath路径</param>
/// <param name="sheetIndex">要读取的工作表索引</param>
/// <param name="rowIndex">开始行数</param>
/// <param name="isHead">开始行是否是DataTable的列名</param>
/// <returns></returns>
public AjaxMsgModel ToDataTable(string excelPath, int sheetIndex, int rowIndex, AjaxMsgModel amm)
{
bool isHead = true;//第一行必须是表头
try
{
DataTable dt = new DataTable(Path.GetFileNameWithoutExtension(excelPath) + "_Sheet" + sheetIndex);
IWorkbook workbook;
//workbook = new HSSFWorkbook(file);//创建对应文件
try
{
using (FileStream file = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(file);//创建对应文件EXCEL2003
}
}
catch (Exception ex)
{
using (FileStream file = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(file);//创建对应文件EXCEL2007
}
}
if (File.Exists(excelPath))//读完即删除
{
File.Delete(excelPath);
}
ISheet sheet = workbook.GetSheetAt(sheetIndex); //按索引获取工作表
var rows = sheet.GetRowEnumerator(); //获取工作表中所有行HSSFSheet.GetRow(rowIndex)获取某一行
for (int i = 0; i < rowIndex; i++) //从第rowindex行开始读取
{
rows.MoveNext();
}
IRow row = (IRow)rows.Current;
try
{
for (int i = 0; i < row.LastCellNum; i++)//给DataTable创建列
{
ICell cell = row.GetCell(i);
string columnName = isHead ? cell.ToString() : i.ToString();
if (columnName.Trim() == "")
{
amm.Statu = AjaxStatu.err;
amm.Msg = "表头名不能为空！";
return(amm);
}
dt.Columns.Add(columnName.Trim(), typeof(string));
}
}
catch (Exception)
{
amm.Statu = AjaxStatu.err;
amm.Msg = "表头名不能为空或有重复！";
return(amm);
}
if (!isHead)//如果第一行是列名则不加入DataTable
{
DataRow first = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
first[i] = cell.ToString();
}
dt.Rows.Add(first);
}
while (rows.MoveNext())
{
row = (IRow)rows.Current;
DataRow dataRow = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell != null)
{
if (cell.CellType == CellType.Numeric)//如果是日期或数字
{
string readvalue = GetCellValue(cell);
if (readvalue.IndexOf("/") > -1)//如果是日期
{
dataRow[i] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
dataRow[i] = readvalue;
}
}
else
{
dataRow[i] = GetCellValue(cell);
}
//dataRow[i] = cell.ToString();
}
else//如果是空白
{
dataRow[i] = "";
}
if (i == dt.Columns.Count - 1)
{
break;//如果行的列比表头多则不继续读取
}
}
dt.Rows.Add(dataRow);
}
amm.Data = removeEmpty(dt);
amm.Statu = AjaxStatu.ok;
amm.Msg = "读取文件内容成功！";
return(amm);
}
catch (Exception)
{
amm.Statu = AjaxStatu.err;
amm.Msg = "读取文件内容失败！可能原因或为文件中没内容";
return(amm);
}
}