public static List <List <string> > Read(string fileName)
{
var lst = new List <List <string> >();
using (FileStream fs = File.OpenRead(fileName)) //打开myxls.xls文件
{
IWorkbook wk;
if (fileName.EndsWith(".xlsx"))
{
//office07
wk = new XSSFWorkbook(fs);
}
else
{
//office03
wk = new HSSFWorkbook(fs);
}
for (int i = 0; i < wk.NumberOfSheets; i++) //NumberOfSheets是myxls.xls中总共的表数
{
ISheet sheet = wk.GetSheetAt(i); //读取当前表数据
for (int j = 0; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数
{
IRow row = sheet.GetRow(j); //读取当前行数据
if (row != null)
{
var lstrow = new List <string>();
lst.Add(lstrow);
for (int k = 0; k <= row.LastCellNum; k++) //LastCellNum 是当前行的总列数
{
ICell cell = row.GetCell(k); //当前表格
lstrow.Add(cell?.ToString() ?? "");
}
}
}
}
}
return(lst);
}
/// <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);
}
/// <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:
sourceValue = sourceCell.BooleanCellValue;
break;
case CellType.Error:
break;
case CellType.Formula:
break;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(sourceCell)) //日期类型
{
sourceValue = sourceCell.DateCellValue;
}
else //其他数字类型
{
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 "boolean":
rs = sourceCell.BooleanCellValue;
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);
}
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public static int DataTableToExcel(DataTable data, string fileName, string sheetName, bool isColumnWritten)
{
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
IWorkbook workbook = null;
//fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook();
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook();
}
if (workbook != null)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
return(-1);
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
{
var headcell = row.CreateCell(j);
headcell.SetCellValue(data.Columns[j].ColumnName);
var headcellstyle = workbook.CreateCellStyle();
headcellstyle.Alignment = HorizontalAlignment.Center;
var c = data.Columns[j].Prefix.Replace("c", "");
if (!string.IsNullOrEmpty(c))
{
headcellstyle.FillForegroundColor = Convert.ToInt16(c);
headcellstyle.FillPattern = FillPattern.SolidForeground;
}
headcell.CellStyle = headcellstyle;
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
var cell = row.CreateCell(j);
cell.SetCellValue(data.Rows[i][j].ToString());
var cellstyle = workbook.CreateCellStyle();
cellstyle.Alignment = HorizontalAlignment.Center;
var c = data.Columns[j].Prefix.Replace("c", "");
if (!string.IsNullOrEmpty(c))
{
cellstyle.FillForegroundColor = Convert.ToInt16(c);
cellstyle.FillPattern = FillPattern.SolidForeground;
}
cell.CellStyle = cellstyle;
}
++count;
}
//列宽自适应，只对英文和数字有效
for (i = 0; i <= data.Rows.Count; i++)
{
sheet.AutoSizeColumn(i);
}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= data.Columns.Count; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(columnNum, Math.Max(10, columnWidth) * 256);
}
using (var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs); //写入到excel
}
workbook = null;
return(count);
}
/// <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);
}
}