Esempio n. 1
0
        /// <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();
                }
            }
        }
Esempio n. 2
0
        /// <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);
        }