private IEnumerable <CellMarkEntity> ProcessMarkCell <T>(ISheet sheet, ref string errorMessage) where T : class, new() { IWorkbook workBook = sheet.Workbook; int NumberOfNames = workBook.NumberOfNames; List <CellMarkEntity> list = new List <CellMarkEntity>(); CellMarkEntity markEntity = null; T entity = new T(); PropertyInfo[] infos = entity.GetType().GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance); for (int index = 0; index < NumberOfNames; index++) { markEntity = new CellMarkEntity(); IName iname = workBook.GetNameAt(index); string region = iname.RefersToFormula.Replace(iname.SheetName, "").Replace("!", "").Replace("$", ""); string[] regions = region.Split(':'); int rowIndex = 0; int colIndex = 0; // 获取行位置和列位置 DealPostion(regions[0], ref rowIndex, ref colIndex); if (iname.NameName.StartsWith("&")) { markEntity.TransGain = true; } markEntity.CellIndex = colIndex; markEntity.RowIndex = rowIndex; markEntity.IName = iname; markEntity.TitleName = sheet.GetRow(rowIndex).GetCell(colIndex).ToString(); string name = iname.NameName.Replace("$", "").Replace("&", ""); IEnumerable <PropertyInfo> tempInfo = infos.Where(t => t.Name.Equals(name, StringComparison.OrdinalIgnoreCase)); if (tempInfo != null && tempInfo.Any()) { PropertyInfo info = tempInfo.FirstOrDefault(); markEntity.ColumnName = info.Name; markEntity.PropertyInfo = info; IEnumerable <Attribute> attributes = info.GetCustomAttributes(); if (attributes != null && attributes.Any()) { markEntity.Attribute = attributes.FirstOrDefault(); } list.Add(markEntity); } else { errorMessage = "第【{0}】行第【{1}】列配置不正确,请联系管理员"; errorMessage = string.Format(errorMessage, rowIndex + 1, ExportExcelUtil.IndexToColName(colIndex)); break; } } return(list.AsEnumerable()); }
private CellDataEntity ProcessCell(ISheet sheet, CellMarkEntity item, IEnumerable <CellMergeEntity> cellMerges, IRow row = null) { CellDataEntity cellData = new CellDataEntity(); cellData.TitleName = item.TitleName; cellData.ColumnName = item.ColumnName; cellData.ColIndex = item.CellIndex; ICell cell = null; int rowIndex = 0; if (row == null) { rowIndex = item.RowIndex; cell = sheet.GetRow(item.RowIndex).GetCell(item.CellIndex + 1); } else { rowIndex = row.RowNum; cell = row.GetCell(item.CellIndex); } if (cell == null) { return(null); } cellData.RowIndex = rowIndex; if (cell.IsMergedCell == false) { cellData.CellPostion = string.Concat(ExportExcelUtil.IndexToColName(item.CellIndex), rowIndex + 1); Type originalType = item.PropertyInfo.PropertyType.GetGenericArguments()[0]; string cellValue = ""; switch (cell.CellType) { case CellType.Numeric: if (HSSFDateUtil.IsCellDateFormatted(cell)) { cellValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff"); } else { cellValue = cell.NumericCellValue.ToString(); } break; case CellType.String: cellValue = cell.StringCellValue; break; case CellType.Boolean: cellValue = cell.BooleanCellValue.ToString(); break; case CellType.Formula: if (originalType == typeof(DateTime)) { cellValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff"); } else { cell.SetCellType(CellType.String); cellValue = cell.ToString(); } break; } cellData.IsEmpty = string.IsNullOrEmpty(cellValue); cellData.CellValue = cellValue; //if (originalType == typeof(DateTime)) //{ // cellData.CellValue = cell.DateCellValue.ToString(); //} //else //{ // if (cell.CellType == CellType.Formula) // cell.SetCellType(CellType.String); // cellData.CellValue = cell.ToString(); //} } else { CellDimension dimension = ProcessCellDimension(cell, cellMerges); var merges = cellMerges.Where(t => t.FirstRow == dimension.FirstRowIndex && t.FirstColumn == dimension.FirstColIndex); cellData.CellPostion = string.Concat(ExportExcelUtil.IndexToColName(dimension.FirstColIndex), dimension.FirstRowIndex + 1); if (merges != null && merges.Any()) { string cellValue = merges.FirstOrDefault().DataCell.ToString(); cellData.CellValue = cellValue; } } cellData.Length = StringUtil.StrLength(cellData.CellValue); return(cellData); }
private string BuildRecSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataRow drMain, DataTable dtSub) { string errorMessage = string.Empty; ISheet sheet = null; if (!string.IsNullOrEmpty(sheetEntity.SheetName)) { sheet = workBook.CreateSheet(sheetEntity.SheetName); } else { sheet = workBook.CreateSheet(); } // 设置开始行和列 int beginRow = 0 + helpEntity.SkipRowNum; int beginCol = 0 + helpEntity.SkipColNum; #region 对表头数据进行赋值 // 标题行 IRow rowTitle = sheet.CreateRow(beginRow); ICell cellTitle = rowTitle.CreateCell(beginCol); cellTitle.SetCellValue(sheetEntity.SheetTitle); // 设置下划线 IFont fontLine = workBook.CreateFont(); fontLine.Underline = FontUnderlineType.Single; if (helpEntity.TitleBoldMark == true) { fontLine.FontHeight = (double)FontBoldWeight.Bold; } cellTitle.CellStyle.SetFont(fontLine); cellTitle.CellStyle.Alignment = HorizontalAlignment.Center; cellTitle.CellStyle.VerticalAlignment = VerticalAlignment.Center; rowTitle.Height = helpEntity.THeight; // 标题行合并 sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, beginCol, beginCol + helpEntity.ExportColumns.Count() - 1)); beginRow++; // 表头字段集合 var masterColumn = helpEntity.ExportColumns.Where(t => t.PrimaryMark == true); var subColumn = helpEntity.ExportColumns.Where(t => t.PrimaryMark == false); int maxColIndex = masterColumn.Select(t => t.ColIndex).Max(); int minColIndex = masterColumn.Select(t => t.ColIndex).Min(); var listTitleContent = masterColumn; var temp = masterColumn.Select(t => t.RowIndex).Distinct(); int colIndex = beginCol; // 循环赋值表头数据 foreach (var rowIndex in temp) { IRow rowContent = sheet.CreateRow(beginRow); var curRow = listTitleContent.Where(t => t.RowIndex == rowIndex).OrderBy(t => t.ColIndex); colIndex = beginCol; var curColMinIndex = curRow.Select(t => t.ColIndex).Min(); var diff = curColMinIndex - minColIndex; // 此处判断主要是为了有跳过列的 if (diff > 0) { // 标题 colIndex += diff; // 内容 colIndex += diff; } foreach (var item in curRow) { colIndex = colIndex - item.diffNum; // 先赋值标题,再赋值 ICell curTitle = rowContent.CreateCell(colIndex); curTitle.SetCellValue(item.ExcelName); curTitle.CellStyle = item.CellStyle; IName iname = workBook.CreateName(); iname.NameName = item.ColumnName; iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1); colIndex++; ICell cell = rowContent.CreateCell(colIndex); string curColumn = item.ColumnName; object curValue = drMain[curColumn]; if (item.TitleColSpan > 1) { sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, colIndex, colIndex + item.TitleColSpan - 1)); colIndex++; } cell.SetCellValue(curValue, item, item.CellStyle); colIndex++; } beginRow++; } #endregion // 获取和循环设置表体表体行 var listTitle = helpEntity.ExportColumns; IRow rowSubTitle = sheet.CreateRow(beginRow); colIndex = beginCol; foreach (var item in listTitle) { ICell cellSubTitle = rowSubTitle.CreateCell(colIndex); cellSubTitle.SetCellValue(item.ExcelName); sheet.SetColumnWidth(colIndex, item.Width); colIndex++; } beginRow++; // 冻结上述行和列 if (helpEntity.FreezeTitleRow) { sheet.CreateFreezePane(beginCol, beginRow - 1, beginCol, beginRow - 1); } // 循环赋值列表数据 foreach (DataRow dr in dtSub.Rows) { IRow rowSubContent = sheet.CreateRow(beginRow); colIndex = beginCol; foreach (var item in listTitle) { ICell cellSubContent = rowSubContent.CreateCell(colIndex); object curValue = dr[item.ColumnName]; // 设置表体内容 cellSubContent.SetCellValue(curValue, item, item.CellStyle); colIndex++; } beginRow++; } // 筛选 if (helpEntity.AutoFilter) { CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum + 1, 0 + helpEntity.SkipRowNum + 1, beginCol, colIndex); sheet.SetAutoFilter(c); } sheet.DisplayGridlines = helpEntity.ShowGridLine; ProcessSheet(sheet, helpEntity); return(errorMessage); }
private string BuildBillSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtMain, DataTable[] dtSub) { string errorMessage = string.Empty; // 在工作簿建立空白工作表 ISheet sheet = null; if (!string.IsNullOrEmpty(sheetEntity.SheetName)) { sheet = workBook.CreateSheet(sheetEntity.SheetName); } else { sheet = workBook.CreateSheet(); } // 看是否有跳过 int beginRow = 0 + helpEntity.SkipRowNum; int beginCol = 0 + helpEntity.SkipColNum; IRow rowHead = sheet.CreateRow(beginRow); // 循环添加表头 int colIndex = beginCol; var tempMast = helpEntity.ExportColumns.Where(t => t.PrimaryMark == true); var tempSub = helpEntity.ExportColumns.Where(t => t.PrimaryMark == false); var tempCol = tempMast.Union(tempSub); foreach (var item in tempCol) { if (item.Hidden) { continue; } ICell cell = rowHead.CreateCell(colIndex); cell.SetCellValue(item.ExcelName); cell.CellStyle = item.CellStyle; sheet.SetColumnWidth(colIndex, item.Width); rowHead.Height = helpEntity.THeight; IName iname = workBook.CreateName(); iname.NameName = item.ColumnName; iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1); colIndex++; } if (helpEntity.FreezeTitleRow) { sheet.CreateFreezePane(0, 0 + helpEntity.SkipRowNum + 1, 0, helpEntity.SkipRowNum + 1); } colIndex = beginCol; beginRow++; //循环赋值内容 for (int i = 0; i < dtMain.Rows.Count; i++) { DataRow drMain = dtMain.Rows[i]; DataTable dtData = dtSub[i]; for (int j = 0; j < dtData.Rows.Count; j++) { DataRow drSub = dtData.Rows[j]; IRow row = sheet.CreateRow(beginRow); ICell cell = null; colIndex = beginCol; foreach (var col in tempCol) { if (col.Hidden) { continue; } cell = row.CreateCell(colIndex); object value = null; if (col.PrimaryMark == true) { value = drMain[col.ColumnName]; if (helpEntity.OneMain == true && j > 0) { colIndex++; continue; } } value = drSub[col.ColumnName]; cell.SetCellValue(value, col, col.CellStyle); colIndex++; } beginRow++; } } // 筛选 if (helpEntity.AutoFilter) { CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum, 0 + helpEntity.SkipRowNum, beginCol, colIndex); sheet.SetAutoFilter(c); } sheet.DisplayGridlines = helpEntity.ShowGridLine; ProcessSheet(sheet, helpEntity); return(errorMessage); }
/// <summary> /// 处理每个工作表数据 /// </summary> /// <param name="dtData"></param> /// <param name="helpEntity"></param> /// <returns></returns> private string BuildSinpleSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtData) { string errorMessage = string.Empty; // 在工作簿建立空白工作表 ISheet sheet = null; if (!string.IsNullOrEmpty(sheetEntity.SheetName)) { sheet = workBook.CreateSheet(sheetEntity.SheetName); } else { sheet = workBook.CreateSheet(); } // 看是否有跳过 int beginRow = 0 + helpEntity.SkipRowNum; int beginCol = 0 + helpEntity.SkipColNum; IRow rowHead = sheet.CreateRow(beginRow); // 循环添加表头 int colIndex = beginCol; foreach (var item in helpEntity.ExportColumns) { if (item.Hidden) { continue; } ICell cell = rowHead.CreateCell(colIndex); cell.SetCellValue(item.ExcelName); cell.CellStyle = item.CellStyle; sheet.SetColumnWidth(colIndex, item.Width); rowHead.Height = helpEntity.THeight; IName iname = workBook.CreateName(); iname.NameName = item.ColumnName; iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1); colIndex++; } if (helpEntity.FreezeTitleRow) { sheet.CreateFreezePane(0, 0 + helpEntity.SkipRowNum + 1, 0, helpEntity.SkipRowNum + 1); } colIndex = beginCol; beginRow++; //循环赋值内容 foreach (DataRow dr in dtData.Rows) { IRow rowContent = sheet.CreateRow(beginRow); ICell cell = null; colIndex = beginCol; foreach (var item in helpEntity.ExportColumns) { if (item.Hidden) { continue; } cell = rowContent.CreateCell(colIndex); object curValue = dr[item.ColumnName]; cell.SetCellValue(curValue, item, item.CellStyle); colIndex++; } rowContent.Height = (short)helpEntity.CHeight; beginRow++; } // 筛选 if (helpEntity.AutoFilter) { CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum, 0 + helpEntity.SkipRowNum, beginCol, colIndex); sheet.SetAutoFilter(c); } sheet.DisplayGridlines = helpEntity.ShowGridLine; ProcessSheet(sheet, helpEntity); return(errorMessage); }