/// <summary>
/// 根据excel实体导出excel ，文件名不需要加后缀
/// </summary>
/// <param name="excelEntity"></param>
/// <param name="fileName"></param>
public static void ExcelEntityToExcel(Dictionary <string, ExcelEntity> excelEntity, string fileName)
{
if (excelEntity == null)
{
return;
}
IWorkbook workbook = null;
CellEntity firstCellEntity = excelEntity.First().Value.CellEntities.FirstOrDefault();
string fileExtend = "";//文件后缀
if (firstCellEntity != null)
{
if (firstCellEntity.Cell is HSSFCell)
{
workbook = new HSSFWorkbook();
fileExtend = ".xls";
}
else if (firstCellEntity.Cell is XSSFCell)
{
workbook = new XSSFWorkbook();
fileExtend = ".xlsx";
}
}
foreach (var sheetName in excelEntity.Keys)
{
ISheet sheet = workbook.CreateSheet(sheetName);
List <CellEntity> tempList = excelEntity[sheetName].CellEntities.OrderBy(x => x.FirstRow).ThenBy(x => x.FirstCol).ToList();
CellEntity firstCell = tempList.First();
if (firstCell == null)
{
continue;
}
int startRow = firstCell.FirstRow;
int startCol = firstCell.FirstCol;
int rowCount = excelEntity[sheetName].CellEntities.Select(x => x.FirstRow).Distinct().Count();
int colCount = excelEntity[sheetName].CellEntities.Select(x => x.FirstCol).Distinct().Count();
for (int rowIndex = startRow; rowIndex < startRow + rowCount; rowIndex++)
{//创建单元格
IRow row = sheet.CreateRow(rowIndex);
for (int colIndex = startCol; colIndex < startCol + colCount; colIndex++)
{
CellEntity entity = excelEntity[sheetName].CellEntities.Find(x => x.FirstRow == rowIndex && x.FirstCol == colIndex);
if (entity != null)
{
ICell cell = row.CreateCell(colIndex);
if (entity.Cell != null)
{
if (entity.IsMerge)
{//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(entity.FirstRow, entity.FirstRow + entity.RowSpan - 1, entity.FirstCol, entity.FirstCol + entity.ColSpan - 1));
}
cell.SetCellType(entity.Cell.CellType);
switch (entity.Cell.CellType)
{
case CellType.Numeric:
cell.SetCellValue(entity.Cell.NumericCellValue);
break;
case CellType.Boolean:
cell.SetCellValue(entity.Cell.BooleanCellValue);
break;
case CellType.Formula:
case CellType.Blank:
case CellType.String:
case CellType.Unknown:
case CellType.Error:
default:
cell.SetCellValue(entity.Cell.ToString());
break;
}
}
//cell.CellStyle = workbook.CreateCellStyle();
//cell.CellStyle.CloneStyleFrom(entity.Cell.CellStyle);
}
}
}
if (excelEntity[sheetName].Pictures != null && excelEntity[sheetName].Pictures.Count > 0)
{
//插入图片
foreach (var item in excelEntity[sheetName].Pictures)
{
int pictureIdx = workbook.AddPicture(item.PictureData, PictureType.JPEG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = patriarch.CreateAnchor(0, 0, 0, 0, item.MinCol, item.MinRow, item.MaxCol, item.MaxRow);
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
byte[] buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(fileName + fileExtend, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
}
/// <summary>
/// excel转换成实体
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static Dictionary <string, ExcelEntity> ExcelToExcelEntity(string filePath)
{
Dictionary <string, ExcelEntity> excelEntity = new Dictionary <string, ExcelEntity>();
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = null;
bool isO3Excel = false;
if (filePath.Last() == 's')
{
workbook = new HSSFWorkbook(fs);
isO3Excel = true;
}
else
{
workbook = new XSSFWorkbook(fs);
isO3Excel = false;
}
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
string sheetName = sheet.SheetName;
excelEntity.Add(sheetName, new ExcelEntity());
//获取表格信息
for (int rowIndex = sheet.FirstRowNum; rowIndex <= sheet.LastRowNum; rowIndex++)
{
for (int colIndex = sheet.GetRow(rowIndex).FirstCellNum; colIndex <= sheet.GetRow(rowIndex).LastCellNum; colIndex++)
{
if (excelEntity[sheetName].CellEntities == null)
{
excelEntity[sheetName].CellEntities = new List <CellEntity>();
}
CellEntity cell = new CellEntity();
ICell excelCell = null;
if (isO3Excel)
{
excelCell = sheet.GetRow(rowIndex).GetCell(colIndex) as HSSFCell;
}
else
{
excelCell = sheet.GetRow(rowIndex).GetCell(colIndex) as XSSFCell;
}
cell.Cell = excelCell;
cell.FirstRow = rowIndex;
cell.FirstCol = colIndex;
int rowSpan;
int colSpan;
cell.IsMerge = ExcelHelper.IsMergeCell(sheet, rowIndex, colIndex, out rowSpan, out colSpan);
cell.RowSpan = rowSpan;
cell.ColSpan = colSpan;
excelEntity[sheetName].CellEntities.Add(cell);
}
}
//获取excel的图片信息
excelEntity[sheetName].Pictures = sheet.GetAllPictureInfos();
}
}
return(excelEntity);
}