/// <summary>
/// 把ExcelSheet对象保存成Excel文件
/// </summary>
/// <param name="excelSheet"></param>
/// <param name="filePath"></param>
/// <param name="version"></param>
/// <returns></returns>
public static int Write <TRow>(string filePath, IList <TRow> dataList, ExcelVersions version = ExcelVersions.Xls) where TRow : IExcelRow
{
List <PropertyAttribute <ExcelColumnAttribute> > properties = Reflections.GetPropertyAttribute <ExcelColumnAttribute>(typeof(TRow));
if (properties.Count == 0)
{
return(DotNETCode.SUCCESS);
}
ExcelSheet sheet = new ExcelSheet();
// 先写第一行，相当于是标题
ExcelRow titleRow = new ExcelRow();
IEnumerable <ExcelColumnAttribute> columns = properties.Select(v => v.Attribute);
foreach (ExcelColumnAttribute column in columns)
{
titleRow.AddCell(column.Name, ExcelCellTypes.String);
}
sheet.AddRow(titleRow);
foreach (TRow row in dataList)
{
ExcelRow excelRow = new ExcelRow();
foreach (PropertyAttribute <ExcelColumnAttribute> property in properties)
{
// 当前要写入的列
object value = property.Property.GetValue(row, null);
excelRow.AddCell(value, property.Attribute.Type);
}
sheet.AddRow(excelRow);
}
return(QuickWrite(sheet, filePath, version));
}
public static int QuickRead(string filePath, ReadOptions options, out ExcelSheet excelSheet)
{
excelSheet = null;
if (!File.Exists(filePath))
{
return(DotNETCode.FILE_NOT_FOUND);
}
using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
string extention = Path.GetExtension(filePath);
IWorkbook workbook = OpenRead(extention, fs);
ISheet sheet = workbook.GetSheetAt(0);
// 还剩leftRow行没读取
int leftRow = sheet.PhysicalNumberOfRows;
excelSheet = new ExcelSheet();
excelSheet.Name = sheet.SheetName;
int currentRow = 0;
// 要注意处理空行
while (leftRow > 0)
{
IRow row = sheet.GetRow(currentRow);
if (row == null)
{
// 该行是空行，不计算在内
currentRow++;
continue;
}
int numCell = row.PhysicalNumberOfCells;
ExcelRow excelRow = new ExcelRow();
for (int cellnum = 0; cellnum < numCell; cellnum++)
{
ICell cell = row.GetCell(cellnum);
if (cell == null || cell.CellType == CellType.Blank)
{
// 空列，根据选项进行处理
if (options.HasFlag(ReadOptions.IgnoreEmptyCell))
{
}
else if (options.HasFlag(ReadOptions.KeepEmptyCell))
{
excelRow.AddCell(null, ExcelCellTypes.Null);
}
continue;
}
switch (cell.CellType)
{
case CellType.Numeric:
{
excelRow.AddCell(cell.NumericCellValue, ExcelCellTypes.Numberic);
break;
}
case CellType.String:
{
excelRow.AddCell(cell.StringCellValue, ExcelCellTypes.String);
break;
}
case CellType.Blank:
{
// 空列，在107行进行了处理
break;
}
default:
logger.ErrorFormat("不支持的Cell数据类型, {0}", cell.CellType);
return(DotNETCode.NOT_SUPPORTED);
}
}
excelSheet.AddRow(excelRow);
leftRow--;
currentRow++;
}
fs.Close();
}
return(DotNETCode.SUCCESS);
}
