/// <summary>
/// 自动设置Excel列宽
/// </summary>
/// <param name="sheet">Excel表</param>
private static void AutoSizeColumns(ISheet sheet)
{
if (sheet.PhysicalNumberOfRows > 0)
{
IRow headerRow = sheet.GetRow(0);
for (int i = 0, l = headerRow.LastCellNum; i < l; i++)
{
sheet.AutoSizeColumn(i);
}
}
}
private static void CreateHeadRow(DataTable dt, HSSFWorkbook workbook, ISheet sheet)
{
ICellStyle titleStyle = workbook.CreateCellStyle();
var styleHeader = CreateHeaderStyle(workbook,
HSSFColor.WHITE.index, HSSFColor.GREEN.index);
IRow row = sheet.CreateRow(0);
ICell c1 = row.CreateCell(0);
ICell c2 = row.CreateCell(1);
ICell c3 = row.CreateCell(2);
int i = 0;
foreach (DataColumn cell in dt.Columns)
{
ICell iCell = row.CreateCell(i);
iCell.SetCellValue(cell.ColumnName);
iCell.CellStyle = styleHeader;
sheet.AutoSizeColumn(i);
i++;
}
}
private int WriteReportToPage(ISheet summaryPage, DataTable table, int startingRow, Boolean autoSize = true)
{
if (table == null)
return startingRow;
var iRunningColumn = 0;
var cellStyle = summaryPage.Workbook.CreateCellStyle();
cellStyle.BorderBottom = BorderStyle.Thick;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = IndexedColors.Grey50Percent.Index;
var failCellStyle = summaryPage.Workbook.CreateCellStyle();
failCellStyle.FillPattern = FillPattern.SolidForeground;
failCellStyle.FillForegroundColor = IndexedColors.Red.Index;
IRow excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
ICell excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
excelCell.SetCellValue(table.TableName);
startingRow++;
excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
foreach (DataColumn tCol in table.Columns)
{
if (tCol.AutoIncrement)
continue;
var runCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
iRunningColumn++;
runCell.SetCellValue(tCol.Caption);
runCell.CellStyle = cellStyle;
}
startingRow++;
var writer = new ExcelCellVisualValue(summaryPage.Workbook);
foreach (DataRow row in table.Rows)
{
excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
startingRow++;
iRunningColumn = -1;
foreach (DataColumn tCol in table.Columns)
{
if (tCol.AutoIncrement)
continue;
iRunningColumn++;
if (row[tCol] == DBNull.Value)
continue;
excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
// ReSharper disable once CanBeReplacedWithTryCastAndCheckForNull
if (row[tCol] is IVisualValue)
{
writer.SetCell(excelCell, (IVisualValue) row[tCol]);
}
else
{
switch (tCol.DataType.Name)
{
case "String":
excelCell.SetCellValue((string) row[tCol]);
break;
case "Int32":
excelCell.SetCellValue(Convert.ToInt32(row[tCol]));
break;
default:
excelCell.SetCellValue((string) row[tCol]);
break;
}
}
}
}
if (!autoSize)
return startingRow + 1;
// sets all used columns to autosize
for (int irun = 0; irun < iRunningColumn; irun++)
{
summaryPage.AutoSizeColumn(irun);
}
return startingRow + 1;
}
private static void buildTemplateSheet(IWorkbook workbook, ISheet sheet, ISheet sheetDS, List<WFActivityField> fields, ref int dsStartIndex, int sheetIndex)
{
if (fields != null)
{
IRow rowColumn = sheet.CreateRow(0);
for (int i = 0; i < fields.Count; i++)
{
ICell cell = rowColumn.CreateCell(i);
string colName = String.IsNullOrWhiteSpace(fields[i].DisplayName) ? fields[i].FieldName : fields[i].DisplayName;
cell.SetCellValue(colName);
ICellStyle cellStyle = workbook.CreateCellStyle();
if (fields[i].IsRequired)
{
cellStyle.FillForegroundColor = IndexedColors.Yellow.Index;
}
else
{
cellStyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
}
cellStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = cellStyle;
sheet.AutoSizeColumn(i);
switch (fields[i].DataType)
{
case FieldInfo.DATATYPE_LIST:
case FieldInfo.DATATYPE_LIST_SQL:
string lstFormulaName = fields[i].FieldName + "fn";
int dsEndIndex = BuildDataSource(fields[i], sheetDS, dsStartIndex);
if (dsEndIndex > dsStartIndex)
{
IName name = sheet.Workbook.CreateName();
name.RefersToFormula = String.Format("'DataSource'!$A${0}:$A${1}", dsStartIndex + 1, dsEndIndex);
name.NameName = lstFormulaName;
name.SheetIndex = sheetIndex;
CellRangeAddressList addressList = new CellRangeAddressList(1, 1, i, i);
IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint dvConstraint = dvHelper.CreateFormulaListConstraint(lstFormulaName);
IDataValidation validation = dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
dsStartIndex = dsEndIndex;
}
break;
}
}
}
}
/// <summary>
/// Create a rules sheet
/// </summary>
private void ReportRules()
{
ISheet rulesSheet = XlsWorkbook.GetSheet(RulesSheet) ?? XlsWorkbook.CreateSheet(RulesSheet);
//Add Header
List <string> headings = new List <string>()
{
"Component Sheet Excluded Objects",
"Type Sheet Excluded Objects",
"Assembly Sheet Excluded Objects",
"Attributes Excludes All Sheets (Name Containing)",
"Attributes Excludes All Sheets (Name Equal)",
"Attributes Excludes All Sheets (Name Starts With)",
"Attributes Excludes Components (Name Containing)",
"Attributes Excludes Components (Name Equal)",
"Attributes Excludes Facility (Name Containing)",
"Attributes Excludes Facility (Name Equal)",
"Attributes Excludes Floor (Name Containing)",
"Attributes Excludes Floor (Name Equal)",
"Attributes Excludes Space (Name Containing)",
"Attributes Excludes Space (Name Equal)",
"Attributes Excludes Space (PropertySet Name)",
"Attributes Excludes Spare (Name Containing)",
"Attributes Excludes Spare (Name Equal)",
"Attributes Excludes Types (Name Containing)",
"Attributes Excludes Types (Name Equal)",
"Attributes Excludes Types (PropertySet Name)",
"Attributes Excludes Zone (Name Containing)"
};
int col = 0;
IRow excelRow = rulesSheet.GetRow(0) ?? rulesSheet.CreateRow(0);
foreach (string title in headings)
{
ICell excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue(title);
col++;
}
WriteExcludesObjects(0, rulesSheet, Excludes.ObjectType.Component);
WriteExcludesObjects(1, rulesSheet, Excludes.ObjectType.Types);
WriteExcludesObjects(2, rulesSheet, Excludes.ObjectType.Assembly);
WriteExcludesStrings(3, rulesSheet, Excludes.Common.AttributesContain);
WriteExcludesStrings(4, rulesSheet, Excludes.Common.AttributesEqualTo);
WriteExcludesStrings(5, rulesSheet, Excludes.Common.AttributesStartWith);
WriteExcludesStrings(6, rulesSheet, Excludes.Component.AttributesContain);
WriteExcludesStrings(7, rulesSheet, Excludes.Component.AttributesEqualTo);
WriteExcludesStrings(8, rulesSheet, Excludes.Facility.AttributesContain);
WriteExcludesStrings(9, rulesSheet, Excludes.Facility.AttributesEqualTo);
WriteExcludesStrings(10, rulesSheet, Excludes.Floor.AttributesContain);
WriteExcludesStrings(11, rulesSheet, Excludes.Floor.AttributesEqualTo);
WriteExcludesStrings(12, rulesSheet, Excludes.Space.AttributesContain);
WriteExcludesStrings(13, rulesSheet, Excludes.Space.AttributesEqualTo);
WriteExcludesStrings(14, rulesSheet, Excludes.Space.PropertySetsEqualTo);
WriteExcludesStrings(15, rulesSheet, Excludes.Spare.AttributesContain);
WriteExcludesStrings(16, rulesSheet, Excludes.Spare.AttributesEqualTo);
WriteExcludesStrings(17, rulesSheet, Excludes.Types.AttributesContain);
WriteExcludesStrings(18, rulesSheet, Excludes.Types.AttributesEqualTo);
WriteExcludesStrings(19, rulesSheet, Excludes.Types.PropertySetsEqualTo);
WriteExcludesStrings(20, rulesSheet, Excludes.Zone.AttributesContain);
for (int c = 0; c < headings.Count; c++)
{
rulesSheet.AutoSizeColumn(c);
}
}
public static bool DataTableToExcel(DataTable dt, string filePath)
{
bool result = false;
IWorkbook workbook = null;
FileStream fs = null;
IRow row = null;
ISheet sheet = null;
ICell cell = null;
try
{
if (dt != null && dt.Rows.Count > 0)
{
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet("Sheet0"); //创建一个名称为Sheet0的表
int rowCount = dt.Rows.Count; //行数
int columnCount = dt.Columns.Count; //列数
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat dataFormatCustom = workbook.CreateDataFormat();
dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");
//设置列头
row = sheet.CreateRow(0);//excel第一行设为列头
for (int c = 0; c < columnCount; c++)
{
cell = row.CreateCell(c);
cell.SetCellValue(dt.Columns[c].ColumnName);
}
//设置每行每列的单元格,
for (int i = 0; i < rowCount; i++)
{
row = sheet.CreateRow(i + 1);
for (int j = 0; j < columnCount; j++)
{
cell = row.CreateCell(j); //excel第二行开始写入数据
SetCellValue(cell, dt.Rows[i][j]); //cell.SetCellValue(dt.Rows[i][j]);
//如果是日期，则设置日期显示的格式
if (dt.Rows[i][j].GetType() == typeof(DateTime))
{
cell.CellStyle = dateStyle;
}
sheet.AutoSizeColumn(j);
}
}
using (fs = File.OpenWrite(filePath))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
result = true;
}
}
return(result);
}
catch (Exception)
{
if (fs != null)
{
fs.Close();
}
return(false);
}
}
/// <summary>
/// DataTable生成Excel
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="strExcelFileName">物理路径 + 文件名称 + 格式</param>
/// <returns>返回生成状态</returns>
public static bool DataTableToExcel(DataTable dt, string strExcelFileName)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
//标题样式
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HeadercellStyle.VerticalAlignment = VerticalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
headerRow.Height = 20 * 22;
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
//单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 1;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
DataRow.Height = 20 * 16;
foreach (DataColumn Colitem in dt.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//写Excel
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
return(true);
}
catch (Exception)
{
return(false);
}
}
/// <summary>
/// 功能： 导出到Excel
/// </summary>
/// <param name="workbook"></param>
/// <param name="title">表头标题</param>
/// <param name="dt">导出的数据</param>
/// <param name="sheetname">sheet表格的名字</param>
public static bool CreateExcel(NPOI.SS.UserModel.IWorkbook workbook, List <string> title, List <int> iColumns, DataTable dt, string sheetname, string sheetTitle, string subTitle)
{
if (iColumns.Count() != title.Count() || iColumns.Max() >= dt.Columns.Count)
{
return(false);
}
int rowRecord = 0;
ISheet sheet = workbook.CreateSheet(sheetname);
#region 定义Sheet标题及子标题
IRow titleRow = sheet.CreateRow(0);
ICell titleCell = titleRow.CreateCell(0);
titleCell.SetCellValue(sheetTitle);
//ICellStyle titleStyle = workbook.CreateCellStyle();
//titleStyle.Alignment = HorizontalAlignment.Center;
//IFont titleFont = workbook.CreateFont();
//titleFont.Boldweight = 200;
//titleFont.FontHeight = 16 * 16;
//titleStyle.SetFont(titleFont);
//titleCell.CellStyle = titleStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, title.Count));
IRow subTitleRow = sheet.CreateRow(1);
ICell subTitleCell = subTitleRow.CreateCell(0);
subTitleCell.SetCellValue(subTitle);
//ICellStyle subTitleStyle = workbook.CreateCellStyle();
//subTitleStyle.Alignment = HorizontalAlignment.Center;
//IFont subTitleFont = workbook.CreateFont();
//subTitleFont.Boldweight = 100;
//subTitleFont.FontHeight = 15 * 15;
//subTitleFont.Color = HSSFColor.Grey80Percent.Index;
//subTitleStyle.SetFont(subTitleFont);
//subTitleCell.CellStyle = subTitleStyle;
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, title.Count));
#endregion
#region 初始化表格标题
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
headerStyle.FillForegroundColor = HSSFColor.Grey50Percent.Index;
headerStyle.FillBackgroundColor = HSSFColor.Grey50Percent.Index;
headerStyle.FillPattern = FillPattern.Squares;
IFont headerFont = workbook.CreateFont();
//headerFont.Boldweight = 200;
//headerFont.FontHeight = 15 * 15;
headerFont.Color = HSSFColor.White.Index;
headerStyle.SetFont(headerFont);
ICellStyle valueStyle = workbook.CreateCellStyle();
//valueStyle.Alignment = HorizontalAlignment.Center;
valueStyle.BorderBottom = BorderStyle.Thin;
valueStyle.BorderLeft = BorderStyle.Thin;
valueStyle.BorderRight = BorderStyle.Thin;
valueStyle.BorderTop = BorderStyle.Thin;
//IFont valueFont = workbook.CreateFont();
//valueFont.FontHeight = 15 * 15;
//valueStyle.SetFont(valueFont);
int rowIndex = 3;
IRow headerRow = sheet.CreateRow(rowIndex);
for (int j = 0; j <= title.Count; j++)
{
ICell headerCell = headerRow.CreateCell(j);
if (j == 0)
{
headerCell.SetCellValue("序号");
}
else
{
headerCell.SetCellValue(title[j - 1]);
}
headerCell.CellStyle = headerStyle;
}
#endregion
if (dt.Rows.Count > 0)
{
#region 数据写入
rowIndex += 1;
for (int k = rowRecord; k < dt.Rows.Count; k++)
{
rowRecord = k;
if (rowIndex > 65535)
{
break;
}
IRow dtRow = sheet.CreateRow(rowIndex);
DataRow dr = dt.Rows[k];
for (int l = 0; l <= dt.Columns.Count; l++)
{
for (int cl = 0; cl <= iColumns.Count(); cl++)
{
ICell valueCell = dtRow.CreateCell(cl);
if (cl == 0)
{
valueCell.SetCellValue(k + 1);
}
else
{
string columnValue = dr[iColumns[cl - 1]].ConvertTo <string>();
columnValue = columnValue.IsNullOrEmpty() || dt.Columns[iColumns[cl - 1]].DataType.Name != "DateTime" ? columnValue : DateTime.Parse(columnValue).ToString("yyyy-MM-dd");
valueCell.SetCellValue(columnValue);
}
valueCell.CellStyle = valueStyle;
}
}
rowIndex += 1;
}
#endregion
}
else
{
rowIndex += 1;
IRow dtRow = sheet.CreateRow(rowIndex);
for (int colIndex = 0; colIndex <= title.Count; colIndex++)
{
ICell valueCell = dtRow.CreateCell(colIndex);
if (colIndex == 0)
{
valueCell.SetCellValue("没有数据");
}
else
{
valueCell.SetCellValue("");
}
valueCell.CellStyle = valueStyle;
}
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, title.Count));
}
#region 自动伸展列宽
for (int colIndex = 0; colIndex <= title.Count; colIndex++)
{
sheet.AutoSizeColumn(colIndex);
}
#endregion
//}
return(true);
}
private void WriteErrors(ISheet errorsSheet, COBieErrorCollection errorCollection)
{
// Write Header
var summary = errorCollection
.GroupBy(row => new { row.SheetName, row.FieldName, row.ErrorType })
.Select(grp => new { grp.Key.SheetName, grp.Key.ErrorType, grp.Key.FieldName, CountError = grp.Count(err => err.ErrorLevel == COBieError.ErrorLevels.Error), CountWarning = grp.Count(err => err.ErrorLevel == COBieError.ErrorLevels.Warning) })
.OrderBy(r => r.SheetName);
//just in case we do not have ErrorLevel property in sheet COBieErrorCollection COBieError
if (!hasErrorLevel)
{
summary = errorCollection
.GroupBy(row => new { row.SheetName, row.FieldName, row.ErrorType })
.Select(grp => new { grp.Key.SheetName, grp.Key.ErrorType, grp.Key.FieldName, CountError = grp.Count(), CountWarning = 0 })
.OrderBy(r => r.SheetName);
}
//Add Header
if (_row == 0)
{
IRow excelRow = errorsSheet.GetRow(0) ?? errorsSheet.CreateRow(0);
int col = 0;
ICell excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue("Sheet Name");
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue("Field Name");
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue("Error Type");
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue("Error Count");
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue("Warning Count");
col++;
_row++;
}
foreach(var error in summary)
{
IRow excelRow = errorsSheet.GetRow(_row + 1) ?? errorsSheet.CreateRow(_row + 1);
int col = 0;
ICell excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue(error.SheetName);
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue(error.FieldName);
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue(error.ErrorType.ToString());
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue(error.CountError);
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue(error.CountWarning);
col++;
_row++;
}
for (int c = 0 ; c < 5 ; c++)
{
errorsSheet.AutoSizeColumn(c);
}
}
private int WriteReportToPage(ISheet summaryPage, DataTable table, int startingRow, Boolean autoSize = true)
{
if (table == null)
{
return(startingRow);
}
var iRunningColumn = 0;
var cellStyle = summaryPage.Workbook.CreateCellStyle();
cellStyle.BorderBottom = BorderStyle.Thick;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = IndexedColors.Grey50Percent.Index;
var failCellStyle = summaryPage.Workbook.CreateCellStyle();
failCellStyle.FillPattern = FillPattern.SolidForeground;
failCellStyle.FillForegroundColor = IndexedColors.Red.Index;
IRow excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
ICell excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
excelCell.SetCellValue(table.TableName);
startingRow++;
excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
foreach (DataColumn tCol in table.Columns)
{
if (tCol.AutoIncrement)
{
continue;
}
var runCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
iRunningColumn++;
runCell.SetCellValue(tCol.Caption);
runCell.CellStyle = cellStyle;
}
startingRow++;
var writer = new ExcelCellVisualValue(summaryPage.Workbook);
foreach (DataRow row in table.Rows)
{
excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
startingRow++;
iRunningColumn = -1;
foreach (DataColumn tCol in table.Columns)
{
if (tCol.AutoIncrement)
{
continue;
}
iRunningColumn++;
if (row[tCol] == DBNull.Value)
{
continue;
}
excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
// ReSharper disable once CanBeReplacedWithTryCastAndCheckForNull
if (row[tCol] is IVisualValue)
{
writer.SetCell(excelCell, (IVisualValue)row[tCol]);
}
else
{
switch (tCol.DataType.Name)
{
case "String":
excelCell.SetCellValue((string)row[tCol]);
break;
case "Int32":
excelCell.SetCellValue(Convert.ToInt32(row[tCol]));
break;
default:
excelCell.SetCellValue((string)row[tCol]);
break;
}
}
}
}
if (!autoSize)
{
return(startingRow + 1);
}
// sets all used columns to autosize
for (int irun = 0; irun < iRunningColumn; irun++)
{
summaryPage.AutoSizeColumn(irun);
}
return(startingRow + 1);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt"></param>
public static byte[] Export(DataTable dt)
{
byte[] Result = null;
if (dt != null && dt.Rows.Count > 0)
{
var book = new HSSFWorkbook();
#region Sheet名称
var TableName = dt.TableName;
var ExtendedProperties = dt.ExtendedProperties;
if (ExtendedProperties != null && ExtendedProperties.Count > 0)
{
if (ExtendedProperties.ContainsKey("CommentName") == true)
{
TableName = Convert.ToString(ExtendedProperties["CommentName"]);
}
}
ISheet sheet = book.CreateSheet(dt.TableName == "" ? "Sheet1" : dt.TableName);
#endregion
#region 创建列头
IRow rowHead = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
var dc = dt.Columns[i];
rowHead.CreateCell(i).SetCellValue(dc.Caption == "" ? dc.ColumnName : dc.Caption);
}
#endregion
#region 写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow rowBody = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = rowBody.CreateCell(j);
cell.SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
#endregion
#region 设置列宽
//列宽自适应，只对英文和数字有效
for (int i = 0; i <= dt.Rows.Count; i++)
{
sheet.AutoSizeColumn(i);
}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= dt.Rows.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, columnWidth * 256);
}
#endregion
#region 生成对象
// 写入到客户端
using (MemoryStream ms = new MemoryStream())
{
book.Write(ms);
Result = ms.ToArray();
book = null;
}
#endregion
}
return(Result);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="FileFullName"></param>
/// <param name="ColumnsStartRowIndex"></param>
/// <param name="DatasStartRowIndex"></param>
/// <param name="IsShowTableColumn"></param>
/// <returns></returns>
public static string Export(DataTable dt, string FileFullName, int ColumnsStartRowIndex, int DatasStartRowIndex, bool IsShowTableColumn)
{
if (string.IsNullOrWhiteSpace(FileFullName) == true)
{
FileFullName = AppDomain.CurrentDomain.BaseDirectory + @"TempFiles\" + DateTime.Now.ToString("yyyyMMdd") + @"\" + Guid.NewGuid().ToString() + ".xls";
}
var fileInfo = new FileInfo(FileFullName);
fileInfo.Directory.Create();
if (dt != null)
{
IWorkbook book = null;
var fileExt = fileInfo.Extension.ToLower();
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx")
{
book = new XSSFWorkbook();
}
else if (fileExt == ".xls")
{
book = new HSSFWorkbook();
}
else
{
book = null;
}
if (book == null)
{
return(null);
}
#region Sheet名称
var TableName = dt.TableName;
var ExtendedProperties = dt.ExtendedProperties;
if (ExtendedProperties != null && ExtendedProperties.Count > 0)
{
if (ExtendedProperties.ContainsKey("CommentName") == true)
{
TableName = Convert.ToString(ExtendedProperties["CommentName"]);
}
}
ISheet sheet = book.CreateSheet(dt.TableName == "" ? "Sheet1" : dt.TableName);
#endregion
#region 创建列头
if (IsShowTableColumn == true)
{
IRow rowHead = sheet.CreateRow(ColumnsStartRowIndex);
for (int i = 0; i < dt.Columns.Count; i++)
{
var dc = dt.Columns[i];
rowHead.CreateCell(i).SetCellValue(dc.Caption == "" ? dc.ColumnName : dc.Caption);
}
}
#endregion
#region 写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow rowBody = sheet.CreateRow(DatasStartRowIndex + i);
for (int j = 0; j < dt.Columns.Count; j++)
{
rowBody.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
#endregion
#region 设置列宽
//列宽自适应，只对英文和数字有效
for (int i = 0; i <= dt.Rows.Count; i++)
{
sheet.AutoSizeColumn(i);
}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= dt.Rows.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, columnWidth * 256);
}
#endregion
#region 生成对象
// 写入到客户端
using (MemoryStream ms = new MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(FileFullName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
#endregion
}
return(FileFullName);
}
/// <summary>
/// 校验数据是否正常
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="outputStream">输出流</param>
/// <param name="sheet">数据sheet</param>
/// <param name="userInfo">用户信息</param>
/// <param name="fileName">文件名称</param>
/// <param name="DictColumnFields">英文字段名到中文列名映射关系</param>
/// <returns>ImportResult</returns>
public virtual ImportResult Verify(DataTable dt, ISheet sheet, Dictionary <string, object> extraInfo, UserInfo userInfo, string fileName, Dictionary <string, ImportVerify> DictColumnFields)
{
IWorkbook wb = sheet.Workbook;
ImportResult result = new ImportResult();
string[] arrErrorMsg = null;
string errorMsg = string.Empty;
int columnCount = dt.Columns.Count;
string columnName = string.Empty;
ImportVerify objVerify = null;
ImportVerifyParam objVerifyParam = new ImportVerifyParam {
DTExcel = dt, CellValue = null, ColName = columnName, ColumnIndex = 0, RowIndex = 0
};
DataRow row = null;
object objExtra = null;
bool isCorrect = true;
//错误数据行样式
var cellErrorStyle = NPOIHelper.GetErrorCellStyle(wb);
ICell errorCell = null;
IRow sheetRow = null;
for (int i = 0, rLength = dt.Rows.Count; i < rLength; i++)
{
row = dt.Rows[i];
arrErrorMsg = new string[columnCount];
for (int j = 0; j < columnCount; j++)
{
columnName = dt.Columns[j].ColumnName;
if (DictColumnFields.TryGetValue(columnName, out objVerify))
{
if (objVerify.VerifyFunc != null)
{
objVerifyParam.CellValue = row[j];
objVerifyParam.ColumnIndex = j;
objVerifyParam.RowIndex = i;
objVerifyParam.ColName = objVerify.ColumnName;
if (extraInfo != null)
{
extraInfo.TryGetValue(columnName, out objExtra);
}
arrErrorMsg[j] = objVerify.VerifyFunc(objVerifyParam, objExtra);
}
}
}
errorMsg = string.Join("，", arrErrorMsg.Where(e => !string.IsNullOrEmpty(e)));
if (!string.IsNullOrEmpty(errorMsg))
{
isCorrect = false;
//设置错误信息
sheetRow = sheet.GetRow(StartRowIndex + 1 + i);
errorCell = sheetRow.GetCell(columnCount);
if (errorCell == null)
{
errorCell = sheetRow.CreateCell(columnCount);
}
errorCell.CellStyle = cellErrorStyle;
errorCell.SetCellValue(errorMsg);
}
}
//输出错误信息模版
if (!isCorrect)
{
sheetRow = sheet.GetRow(StartRowIndex);
errorCell = sheetRow.GetCell(columnCount);
if (errorCell == null)
{
errorCell = sheetRow.CreateCell(columnCount);
}
ICellStyle copyStyle = sheetRow.GetCell(columnCount - 1).CellStyle;
ICellStyle style = NPOIHelper.GetErrorHeadCellStyle(wb);
IFont font = style.GetFont(wb);
IFont copyfont = copyStyle.GetFont(wb);
font.FontHeight = copyfont.FontHeight;
font.FontName = copyfont.FontName;
style.FillForegroundColor = copyStyle.FillForegroundColor;
style.BorderBottom = copyStyle.BorderBottom;
style.BorderLeft = copyStyle.BorderLeft;
style.BorderRight = copyStyle.BorderRight;
style.BorderTop = copyStyle.BorderTop;
errorCell.CellStyle = style;
errorCell.SetCellValue("错误信息");
//自适应列宽度
sheet.AutoSizeColumn(columnCount);
int width = sheet.GetColumnWidth(columnCount) + 2560;
sheet.SetColumnWidth(columnCount, width > NPOIHelper.MAX_COLUMN_WIDTH ? NPOIHelper.MAX_COLUMN_WIDTH : width);
result.Message = ExcelImportHelper.GetErrorExcel(wb, fileName);
}
else
{
result.IsSuccess = true;
}
return(result);
}
public void AutoSizeColumn(int column)
{
_sheet.AutoSizeColumn(column);
}
public IWorkbook GenerateExcelFileWithTitle(DataTable dt, string titulo)
{
try
{
IWorkbook workbook;
workbook = new HSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet 1");
int fila_titulo = 0;
IRow row_titulo = sheet1.CreateRow(fila_titulo);
//Creamos el estilo para el titutlo
IFont boldFontTitulo = workbook.CreateFont();
boldFontTitulo.Boldweight = (short)FontBoldWeight.Bold;
boldFontTitulo.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;
boldFontTitulo.FontHeightInPoints = 15;
ICellStyle boldStyle_titulo = workbook.CreateCellStyle();
//boldStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SeaGreen.Index;
//boldStyle.FillPattern = FillPattern.SolidForeground;
boldStyle_titulo.SetFont(boldFontTitulo);
boldStyle_titulo.Alignment = HorizontalAlignment.Center;
//Ponemos el contenido del título.
ICell cell_titulo = row_titulo.CreateCell(0);
cell_titulo.CellStyle = boldStyle_titulo;
cell_titulo.SetCellValue(titulo);
//Hacemos el merge
var merged = new NPOI.SS.Util.CellRangeAddress(fila_titulo, fila_titulo, 0, dt.Columns.Count == 0 ? 8 : dt.Columns.Count - 1);
sheet1.AddMergedRegion(merged);
int fila_inicial = fila_titulo + 2;
//make a header row
IRow row1 = sheet1.CreateRow(fila_inicial);
IFont boldFont = workbook.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.Bold;
boldFont.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
ICellStyle boldStyle = workbook.CreateCellStyle();
boldStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SeaGreen.Index;
boldStyle.FillPattern = FillPattern.SolidForeground;
boldStyle.SetFont(boldFont);
//Creamos el estilo para manejar números.
ICellStyle cellNumberStyle = workbook.CreateCellStyle();
cellNumberStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
String columnName = dt.Columns[j].ToString();
cell.CellStyle = boldStyle; //Ponemos el estilo del encabezado.
cell.SetCellValue(columnName);
}
//loops through data
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet1.CreateRow(i + fila_inicial + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
String columnName = dt.Columns[j].ToString();
//Parseamos a float
float valor = 0;
//En caso de ser número, ponemos la celda como tipo número.
if (float.TryParse(dt.Rows[i][columnName].ToString(), out valor) &&
!columnName.ToString().ToLower().Contains("folio"))
{
cell.SetCellValue(valor);
cell.CellStyle = cellNumberStyle;
}
else
{
cell.SetCellValue(dt.Rows[i][columnName].ToString());
}
}
}
//Hacemos el autosize
foreach (DataColumn column in dt.Columns)
{
sheet1.AutoSizeColumn(column.Ordinal);
}
return(workbook);
}
catch (Exception ex)
{
return(null);
}
// Declare one MemoryStream variable for write file in stream
/*var stream = new MemoryStream();
* workbook.Write(stream);
*
* string FilePath = "archivo001";
*
* //Write to file using file stream
* FileStream file = new FileStream(FilePath, FileMode.CreateNew, FileAccess.Write);
* stream.WriteTo(file);
* file.Close();
* stream.Close();*/
}
private void FinalizeWorkSheet(ISheet worksheet)
{
if (worksheet != null)
{
var hssfSheet = worksheet as HSSFSheet;
if (hssfSheet != null)
{
hssfSheet.SetAutoFilter(new CellRangeAddress(0, _rowIndex - 1, 0, _splitColumns.Length - 1));
}
ForEachColumn((i, f) =>
{
worksheet.AutoSizeColumn(i);
// Units are 256 per character.
// Maximum width is 255 characters.
var width = Math.Min(worksheet.GetColumnWidth(i) + 1024, 255 * 256);
worksheet.SetColumnWidth(i, width);
});
}
}
public static void ExportExcel(DataTable dt, Dictionary <string, string> columns, string path, string sheetName)
{
try
{
IWorkbook workbook = null;
string fileExt = Path.GetExtension(path).ToLower();
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook();
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook();
}
else
{
throw new ArgumentException("文件扩展名错误。");
}
ISheet sheet = workbook.CreateSheet(sheetName); //创建一个 sheet 表
IRow rowH = sheet.CreateRow(0); //创建标题行
int k = 0;
foreach (var col in columns)
{
sheet.AutoSizeColumn(k);
ICell cell = rowH.CreateCell(k);
cell.SetCellValue(col.Value);
cell.CellStyle = GetColumnHeaderStyle(workbook);
k++;
}
//for (int i = 0; i < columns.Count; i++)
//{
// ICell cell= rowH.CreateCell(i);
// cell.SetCellValue(columns[i]);
// cell.CellStyle = GetColumnHeaderStyle(workbook);
//}
ICellStyle cellStyle = GetContentCellStyle(workbook);
for (int i = 0; i < dt.Rows.Count; i++) //写入数据
{
IRow row = sheet.CreateRow(i + 1); //跳过第一行，第一行为列名
int j = 0;
foreach (var col in columns)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][col.Key].ToString());
cell.CellStyle = cellStyle;
j++;
}
//for (int j = 0; j < dt.Columns.Count; j++)
//{
// ICell cell = row.CreateCell(j);
// cell.SetCellValue(dt.Rows[i][j].ToString());
// var ss= dt.Rows[i][""];
// cell.CellStyle = cellStyle;
//}
}
for (int columnNum = 0; columnNum < columns.Count; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow 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 + 4)
{
columnWidth = length + 4;
}
}
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
MemoryStream ms = new MemoryStream(); //创建一个 IO 流
workbook.Write(ms); //写入到流
byte[] bytes = ms.ToArray(); //转换为字节数组
using (FileStream file = new FileStream(path, FileMode.CreateNew, FileAccess.Write))
{
file.Write(bytes, 0, bytes.Length);
file.Flush();
}
//workbook.Close();
//sheet = null;
//workbook = null;
// ICell cell = null; //创建一个单元格
////设置列名
//foreach (DataColumn col in dt.Columns)
//{
// //创建单元格并设置单元格内容
// rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption);
// //设置单元格格式
// rowH.Cells[col.Ordinal].CellStyle = cellStyle;
//}
//for (int i = 0; i < columnHeaders.Length; i++)
//{
// sheet.AutoSizeColumn(i);
// //创建单元格并设置单元格内容
// rowH.CreateCell(i).SetCellValue(columnHeaders[i]);
// //设置单元格格式
// rowH.Cells[i].CellStyle = cellStyle;
//}
}
catch (Exception ex)
{
throw ex;
}
}
public void WriteSheetData <T>(List <T> exportData, string sheetName = DefaultSheetName)
{
List <string> _headers = new List <string>();
List <string> _type = new List <string>();
ISheet _sheet = _workbook.CreateSheet(sheetName); //Creating New Excel Sheet object
var headerStyle = _workbook.CreateCellStyle(); //Formatting
var headerFont = _workbook.CreateFont();
headerFont.IsBold = true;
headerFont.FontName = "Calibri";
headerFont.FontHeight = 16;
headerStyle.SetFont(headerFont);
#region Create Header
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
foreach (PropertyDescriptor prop in properties)
{
var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
_type.Add(type.Name);
string name = Regex.Replace(prop.Name, "([A-Z])", " $1").Trim(); //space separated
//name by caps for header
_headers.Add(name);
}
var header = _sheet.CreateRow(0);
for (var i = 0; i < _headers.Count; i++)
{
var cell = header.CreateCell(i);
cell.SetCellValue(_headers[i]);
//cell.CellStyle = headerStyle;
}
#endregion
#region write data
IRow sheetRow = null;
for (int i = 0; i < exportData.Count; i++)
{
sheetRow = _sheet.CreateRow(i + 1);
for (int j = 0; j < properties.Count; j++)
{
ICell Row1 = sheetRow.CreateCell(j);
string type = (Nullable.GetUnderlyingType(properties[j].PropertyType) ?? properties[j].PropertyType).Name.ToLower();
var currentCellValue = exportData[i].GetType().GetProperty(properties[j].Name).GetValue(exportData[i], null);
if (currentCellValue != null &&
!string.IsNullOrEmpty(Convert.ToString(currentCellValue)))
{
if (type == "string")
{
Row1.SetCellValue(Convert.ToString(currentCellValue));
}
else if (type == "int32")
{
Row1.SetCellValue(Convert.ToInt32(currentCellValue));
}
else if (type == "double")
{
Row1.SetCellValue(Convert.ToDouble(currentCellValue));
}
}
else
{
Row1.SetCellValue(string.Empty);
}
}
}
#endregion
for (var i = 0; i < _headers.Count; i++)
{
_sheet.AutoSizeColumn(i);
}
}
public void CreateXSLXFile(System.Collections.ArrayList collection)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFFont myFont = (HSSFFont)workbook.CreateFont();
myFont.FontHeightInPoints = 11;
myFont.FontName = "Tahoma";
// Defining a border
HSSFCellStyle borderedCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
borderedCellStyle.SetFont(myFont);
borderedCellStyle.BorderLeft = BorderStyle.Medium;
borderedCellStyle.BorderTop = BorderStyle.Medium;
borderedCellStyle.BorderRight = BorderStyle.Medium;
borderedCellStyle.BorderBottom = BorderStyle.Medium;
borderedCellStyle.VerticalAlignment = VerticalAlignment.Center;
ISheet Sheet = workbook.CreateSheet("Report");
//Creat The Headers of the excel
IRow HeaderRow = Sheet.CreateRow(0);
Type itemType = collection[0].GetType();
int columnIndex = 0;
foreach (PropertyInfo prop in itemType.GetProperties())
{
CreateCell(HeaderRow, columnIndex, prop.Name, borderedCellStyle);
columnIndex++;
}
// This Where the Data row starts from
int RowIndex = 1;
//Iteration through some collection
foreach (object item in collection)
{
//Creating the CurrentDataRow
IRow CurrentRow = Sheet.CreateRow(RowIndex);
foreach (PropertyInfo prop in item.GetType().GetProperties())
{
// prop.GetCustomAttribute(System.ComponentModel.DataAnnotation.Disp)
}
CreateCell(CurrentRow, 0, batchErrorReport.Name, borderedCellStyle);
// This will be used to calculate the merge area
int NumberOfRules = batchErrorReport.Rules.Count;
if (NumberOfRules > 1)
{
int MergeIndex = (NumberOfRules - 1) + RowIndex;
//Merging Cells
NPOI.SS.Util.CellRangeAddress MergedBatch = new NPOI.SS.Util.CellRangeAddress(RowIndex, MergeIndex, 0, 0);
Sheet.AddMergedRegion(MergedBatch);
}
int i = 0;
// Iterate through cub collection
foreach (BatchDataQuality batchDataQuality in batchErrorReport.Rules)
{
if (i > 0)
{
CurrentRow = Sheet.CreateRow(RowIndex);
}
CreateCell(CurrentRow, 1, batchDataQuality.RuleID, borderedCellStyle);
CreateCell(CurrentRow, 2, batchDataQuality.RuleType, borderedCellStyle);
CreateCell(CurrentRow, 3, batchDataQuality.CodeMessageType, borderedCellStyle);
CreateCell(CurrentRow, 4, batchDataQuality.Severity, borderedCellStyle);
RowIndex++;
i++;
}
RowIndex = NumberOfRules >= 1 ? RowIndex : RowIndex + 1;
}
// Auto sized all the affected columns
int lastColumNum = Sheet.GetRow(0).LastCellNum;
for (int i = 0; i <= lastColumNum; i++)
{
Sheet.AutoSizeColumn(i);
GC.Collect();
}
// Write Excel to disk
using (var fileData = new FileStream(Utility.DOCUMENT_PATH + "ReportName.xls", FileMode.Create))
{
workbook.Write(fileData);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
private static MemoryStream Export(DataTable dtSource, Hashtable Columns, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
//#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "NPOI";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "文件作者信息"; //填加xls文件作者信息
// si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
// si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
// si.Comments = "作者信息"; //填加xls文件作者信息
// si.Title = "标题信息"; //填加xls文件标题信息
// si.Subject = "主题信息";//填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
//#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
//int[] arrColWidth = new int[dtSource.Columns.Count];
//foreach (DataColumn item in dtSource.Columns)
//{
// arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
//}
//for (int i = 0; i < dtSource.Rows.Count; i++)
//{
// for (int j = 0; j < dtSource.Columns.Count; j++)
// {
// int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
// if (intTemp > arrColWidth[j])
// {
// arrColWidth[j] = intTemp;
// }
// }
//}
int rowIndex = 0;
IRow excelRow;
ICellStyle headStyle = workbook.CreateCellStyle();
IFont font;
ICell newCell;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == MaxRowCount || rowIndex == 0)
{
if (rowIndex != 0)
{
rowIndex = 0;
sheet = workbook.CreateSheet();
}
#region 表头及样式
if (!string.IsNullOrEmpty(strHeaderText))
{
excelRow = sheet.CreateRow(0);
excelRow.HeightInPoints = 25;
excelRow.CreateCell(0).SetCellValue(strHeaderText);
headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
excelRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
// headerRow.Dispose();
rowIndex++;
}
#endregion
#region 列头及样式
excelRow = sheet.CreateRow(rowIndex);
headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
string columnName;
foreach (DataColumn column in dtSource.Columns)
{
columnName = GetColumnName(column.ColumnName, Columns);
excelRow.CreateCell(column.Ordinal).SetCellValue(columnName);
excelRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
// sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
// headerRow.Dispose();
rowIndex++;
#endregion
// rowIndex = 2;
}
#endregion
#region 填充内容
excelRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
newCell = excelRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
if (DateTime.TryParse(drValue, out dateV))
{
newCell.SetCellValue(dateV.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
newCell.SetCellValue("");
}
// newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
int maxRowCount = dtSource.Rows.Count;
maxRowCount = string.IsNullOrEmpty(strHeaderText) ? maxRowCount + 1 : maxRowCount + 2;
maxRowCount = Math.Min(maxRowCount, 65535);
if (maxRowCount == rowIndex)
{
for (int i = 0; i < sheet.GetRow(0).PhysicalNumberOfCells; i++)
{
sheet.AutoSizeColumn(i);
}
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
private Stream ExportCoursesByAdmin()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
var allCourses = _courseService.GetCourses();
var allUsers = _userService.GetAll();
var existedCourseUsers = GetAllCourseUser();
if (allCourses.Count() <= 0)
{
//如果没有课程数据，则只生成表头
ISheet sheet = hssfworkbook.CreateSheet("Demo");
IRow courseHeader = sheet.CreateRow(0);
//生成课程标题
courseHeader.CreateCell(0).SetCellValue("Date");
courseHeader.CreateCell(1).SetCellValue("StartTime");
courseHeader.CreateCell(2).SetCellValue("EndTime");
courseHeader.CreateCell(3).SetCellValue("CourseCode");
courseHeader.CreateCell(4).SetCellValue("CourseName");
courseHeader.CreateCell(5).SetCellValue("CourseDetail");
courseHeader.CreateCell(6).SetCellValue("TotalStudent");
courseHeader.CreateCell(7).SetCellValue("Class");
courseHeader.CreateCell(8).SetCellValue("Region");
//生成学员列表表头
IRow fellowListHeader = sheet.CreateRow(3);
fellowListHeader.CreateCell(0).SetCellValue("学员列表");
}
allCourses.ToList().ForEach(course => {
ISheet sheet = hssfworkbook.CreateSheet(course.Lecturer + "-" + course.CourseID);
IRow courseHeader = sheet.CreateRow(0);
//生成课程标题
courseHeader.CreateCell(0).SetCellValue("Date");
courseHeader.CreateCell(1).SetCellValue("StartTime");
courseHeader.CreateCell(2).SetCellValue("EndTime");
courseHeader.CreateCell(3).SetCellValue("CourseCode");
courseHeader.CreateCell(4).SetCellValue("CourseName");
courseHeader.CreateCell(5).SetCellValue("CourseDetail");
courseHeader.CreateCell(6).SetCellValue("TotalStudent");
courseHeader.CreateCell(7).SetCellValue("Class");
courseHeader.CreateCell(8).SetCellValue("Region");
var userIds = existedCourseUsers.Where(p => p.CourseID == course.CourseID).Select(p => p.UserID).Distinct().ToList();
//生成课程内容
IRow courseRow = sheet.CreateRow(1);
courseRow.CreateCell(0).SetCellValue(course.Date?.ToString("YYYY-MM-DD"));
courseRow.CreateCell(1).SetCellValue(course.StartTime);
courseRow.CreateCell(2).SetCellValue(course.EndTime);
courseRow.CreateCell(3).SetCellValue(course.CourseCode);
courseRow.CreateCell(4).SetCellValue(course.CourseName);
courseRow.CreateCell(5).SetCellValue(course.CourseDetail);
courseRow.CreateCell(6).SetCellValue(userIds.Count());
courseRow.CreateCell(7).SetCellValue(course.Class);
courseRow.CreateCell(8).SetCellValue(_courseService.GetRegionDescription(course.Region));
//生成学员列表表头
IRow fellowListHeader = sheet.CreateRow(3);
fellowListHeader.CreateCell(0).SetCellValue("学员列表");
//生成学员列表
var rowIndex = 3;
userIds.ForEach(userId => {
var user = allUsers.FirstOrDefault(tempUser => tempUser.UserID == userId);
if (user == null)
{
return;
}
rowIndex++;
IRow rowTemp = sheet.CreateRow(rowIndex);
rowTemp.CreateCell(0).SetCellValue(user.Name);
});
for (int i = 0; i <= 8; i++)
{
sheet.AutoSizeColumn(i);
}
});
MemoryStream stream = new MemoryStream();
hssfworkbook.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
return(stream);
}
public static void ProtocolToXlsx(string exportPath, Protocol protocol)
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet($"Протокол №{protocol.Id}");
XSSFCellStyle captionCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
captionCellStyle.WrapText = true;
captionCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
captionCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
captionCellStyle.SetFont(new XSSFFont()
{
IsBold = true, FontName = "Segoe UI", FontHeight = 12, Boldweight = (short)FontBoldWeight.Bold
});
captionCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
captionCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
captionCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
captionCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
IRow captionRow = sheet.CreateRow(0);
captionRow.Height = 900;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 11));
for (int i = 1; i < 11; i++)
{
captionRow.CreateCell(i).CellStyle = captionCellStyle;
}
var captionCell = captionRow.CreateCell(0);
captionCell.SetCellValue($"Протокол №{protocol.Id}{Environment.NewLine}проверки работоспособности комплекса счётчик - LoRa-модуль{Environment.NewLine}{protocol.DateTime.ToString("dddd, dd MMMM yyyy HH:mm:ss")}");
captionCell.CellStyle = captionCellStyle;
List <Header> protocolHeaders = new List <Header>()
{
new Header
{
Name = "№ п/п",
Index = 0,
ProtocolHeaders = null
},
new Header
{
Name = "Зав. №",
Index = 1,
ProtocolHeaders = null
},
new Header
{
Name = "Версия ПО",
Index = 2,
ProtocolHeaders = null
},
new Header
{
Name = "DevEui",
Index = 3,
ProtocolHeaders = null
},
new Header
{
Name = "DevAdd/NwkSKey",
Index = 4,
ProtocolHeaders = null
},
new Header
{
Name = "AppSKey/AppEui/AppKey",
Index = 5,
ProtocolHeaders = null
},
new Header
{
Name = "Качество связи (SNR)",
Index = 6,
ProtocolHeaders = null
},
new Header
{
Name = "Системное время",
Index = 7,
ProtocolHeaders = null
},
new Header
{
Name = "Состояние реле",
Index = 8,
ProtocolHeaders = null
},
new Header
{
Name = "Примечание",
Index = 9,
ProtocolHeaders = null
}
};
var headerRow = sheet.CreateRow(1);
var headerRow2 = sheet.CreateRow(2);
protocolHeaders.ForEach(item =>
{
headerRow.CreateCell(item.Index).SetCellValue(item.Name);
headerRow.Cells.Where(c => c.ColumnIndex == item.Index).FirstOrDefault().CellStyle = captionCellStyle;
if (item.ProtocolHeaders != null)
{
item.ProtocolHeaders.ForEach(it =>
{
headerRow2.CreateCell(item.Index + it.Index).SetCellValue(it.Name);
headerRow2.Cells.Where(c => c.ColumnIndex == item.Index + it.Index).FirstOrDefault().CellStyle = captionCellStyle;
});
sheet.AddMergedRegion(new CellRangeAddress(1, 1, item.Index, item.Index + item.ProtocolHeaders.Count - 1));
}
else
{
headerRow2.CreateCell(item.Index);
headerRow2.Cells.Where(c => c.ColumnIndex == item.Index).FirstOrDefault().CellStyle = captionCellStyle;
sheet.AddMergedRegion(new CellRangeAddress(1, 2, item.Index, item.Index));
}
});
int j = 2;
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellStyle.WrapText = true;
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyle.SetFont(new XSSFFont()
{
IsBold = true, FontName = "Calibri", FontHeight = 10
});
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderDiagonal = BorderDiagonal.Backward;
int x = 1;
foreach (var device in protocol.Devices)
{
var row = sheet.CreateRow(j + x);
var cell_A = row.CreateCell(0);
cell_A.CellStyle = cellStyle;
cell_A.SetCellType(CellType.String);
cell_A.SetCellValue(x);
var cell_B = row.CreateCell(1);
cell_B.CellStyle = cellStyle;
cell_B.SetCellType(CellType.String);
cell_B.SetCellValue(device.FactoryNumber); //заводской номер
var cell_C = row.CreateCell(2);
cell_C.CellStyle = cellStyle;
cell_C.SetCellType(CellType.String);
cell_C.SetCellValue(device.SoftwareVersion); //версия ПО
var cell_D = row.CreateCell(3);
cell_D.CellStyle = cellStyle;
cell_D.SetCellType(CellType.String);
cell_D.SetCellValue(device.DevEui);
var cell_E = row.CreateCell(4);
cell_E.CellStyle = cellStyle;
cell_E.SetCellType(CellType.String);
cell_E.SetCellValue(device.DevAdd + "/" + Environment.NewLine + device.NwkSKey);
var cell_F = row.CreateCell(5);
cell_F.CellStyle = cellStyle;
cell_F.SetCellType(CellType.String);
cell_F.SetCellValue(device.AppSKey + "/" + Environment.NewLine + device.AppEui + "/" + Environment.NewLine + device.AppKey);
var cell_G = row.CreateCell(6);
cell_G.CellStyle = cellStyle;
cell_G.SetCellType(CellType.String);
cell_G.SetCellValue(device.Snr); //качество связи
//var cell_H = row.CreateCell(7);
//cell_H.CellStyle = cellStyle;
//cell_H.SetCellType(CellType.String);
////cell_H.SetCellValue(device.TimeBefore); //отклонение времени до коррекции
//var cell_I = row.CreateCell(8);
//cell_I.CellStyle = cellStyle;
//cell_I.SetCellType(CellType.String);
////cell_I.SetCellValue(device.TimeAfter); //отклонение времени после коррекции
//var cell_J = row.CreateCell(9);
//cell_J.CellStyle = cellStyle;
//cell_J.SetCellType(CellType.String);
////cell_J.SetCellValue(device.RelayOff); //реле откл.
//var cell_K = row.CreateCell(10);
//cell_K.CellStyle = cellStyle;
//cell_K.SetCellType(CellType.String);
////cell_K.SetCellValue(device.RelayOn); //реле вкл.
var cell_H = row.CreateCell(7);
cell_H.CellStyle = cellStyle;
cell_H.SetCellType(CellType.String);
cell_H.SetCellValue(device.TimeBefore); //системное время
var cell_K = row.CreateCell(8);
cell_K.CellStyle = cellStyle;
cell_K.SetCellType(CellType.String);
cell_K.SetCellValue(device.Relay); //состояние реле
var cell_L = row.CreateCell(9);
cell_L.CellStyle = cellStyle;
cell_L.SetCellType(CellType.String);
cell_L.SetCellValue(device.Notes); //примечание
x++;
}
var testerRow = sheet.CreateRow(j + protocol.Devices.Count + 2);
testerRow.CreateCell(5).SetCellValue("Испытание провёл:");
testerRow.CreateCell(6).SetCellValue(protocol.Tester = "Ендовицкий И.Н.");
sheet.AddMergedRegion(new CellRangeAddress(j + protocol.Devices.Count + 2, j + protocol.Devices.Count + 2, 6, 11));
for (int i = 0; i <= 11; i++)
{
sheet.AutoSizeColumn(i);
}
using (FileStream fileStream = new FileStream(Path.Combine(exportPath, $"Протокол №{protocol.Id}.xlsx"), FileMode.Create, FileAccess.ReadWrite))
{
workbook.Write(fileStream);
}
}
public static void CreateExcelFile(string jsonStr, string fileName = "")
{
DtoModels model = Newtonsoft.Json.JsonConvert.DeserializeObject <DtoModels>(jsonStr);
HSSFWorkbook wk = new HSSFWorkbook();
ICellStyle style11 = wk.CreateCellStyle();
style11.DataFormat = 194;
//创建一个Sheet
ISheet sheet = wk.CreateSheet("sheet1");
//在第一行创建行
IRow row = sheet.CreateRow(0);
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue("offerType");
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue("linkDest");
ICell cell2 = row.CreateCell(2);
cell2.SetCellValue("convertsOn");
ICell cell3 = row.CreateCell(3);
cell3.SetCellValue("appInfo/appID");
ICell cell4 = row.CreateCell(4);
cell4.SetCellValue("appInfo/previewLink");
ICell cell5 = row.CreateCell(5);
cell5.SetCellValue("appInfo/appName");
ICell cell6 = row.CreateCell(6);
cell6.SetCellValue("appInfo/appCategory");
ICell cell7 = row.CreateCell(7);
cell7.SetCellValue("targets/offerID");
ICell cell8 = row.CreateCell(8);
cell8.SetCellValue("targets/approvalStatus");
ICell cell9 = row.CreateCell(9);
cell9.SetCellValue("targets/offerStatus");
ICell cell10 = row.CreateCell(10);
cell10.SetCellValue("targets/trackingLink");
ICell cell11 = row.CreateCell(11);
cell11.SetCellValue("targets/countries");
ICell cell12 = row.CreateCell(12);
cell12.SetCellValue("targets/platforms");
ICell cell13 = row.CreateCell(13);
cell13.SetCellValue("targets/payoutUSD");
ICell cell14 = row.CreateCell(14);
cell14.SetCellValue("targets/endDate");
ICell cell15 = row.CreateCell(15);
cell15.SetCellValue("targets/dailyConversionCap");
ICell cell16 = row.CreateCell(16);
cell16.SetCellValue("targets/restrictions/allowIncent");
ICell cell17 = row.CreateCell(17);
cell17.SetCellValue("targets/restrictions/deviceIDRequired");
ICell cell18 = row.CreateCell(18);
cell18.SetCellValue("targets/restrictions/minOSVersion");
ICell cell19 = row.CreateCell(19);
cell19.SetCellValue("requestID");
ICellStyle style = wk.CreateCellStyle(); //创建样式对象
IFont font = wk.CreateFont(); //创建一个字体样式对象
font.FontName = "微软雅黑"; //和excel里面的字体对应
font.FontHeightInPoints = 12; //字体大小
font.Boldweight = short.MinValue; //字体加粗
style.SetFont(font); //将字体样式赋给样式对象
for (int j = 0; j <= 19; j++)
{
row.GetCell(j).CellStyle = style;//把样式赋给单元格
}
int index1 = 0;
foreach (var m in model.offers)
{
foreach (var target in m.targets)
{
index1++;
IRow row1 = sheet.CreateRow(index1);
ICell cel0 = row1.CreateCell(0);
cel0.SetCellValue(m.offerType);
ICell cel1 = row1.CreateCell(1);
cel1.SetCellValue(m.linkDest);
ICell cel2 = row1.CreateCell(2);
cel2.SetCellValue(m.convertsOn);
ICell cel3 = row1.CreateCell(3);
cel3.SetCellValue(m.appInfo.appID);
ICell cel4 = row1.CreateCell(4);
cel4.SetCellValue(m.appInfo.previewLink);
ICell cel5 = row1.CreateCell(5);
cel5.SetCellValue(m.appInfo.appName);
ICell cel6 = row1.CreateCell(6);
cel6.SetCellValue(m.appInfo.appCategory);
ICell cel7 = row1.CreateCell(7);
cel7.SetCellValue(target.offerID);
ICell cel8 = row1.CreateCell(8);
cel8.SetCellValue(target.approvalStatus);
ICell cel9 = row1.CreateCell(9);
cel9.SetCellValue(target.offerStatus);
ICell cel10 = row1.CreateCell(10);
cel10.SetCellValue(target.trackingLink);
ICell cel11 = row1.CreateCell(11);
cel11.SetCellValue(string.Join(",", target.countries));
ICell cel12 = row1.CreateCell(12);
cel12.SetCellValue(string.Join(",", target.platforms));
ICell cel13 = row1.CreateCell(13);
cel13.SetCellValue(target.payout.amount.ToString());
cel13.CellStyle = style11; //设置格式为数字
ICell cel14 = row1.CreateCell(14);
cel14.SetCellValue(target.endDate);
ICell cel15 = row1.CreateCell(15);
cel15.SetCellValue(target.dailyConversionCap);
ICell cel16 = row1.CreateCell(16);
cel16.SetCellValue(target.restrictions.allowIncent);
ICell cel17 = row1.CreateCell(17);
cel17.SetCellValue(target.restrictions.deviceIDRequired);
ICell cel18 = row1.CreateCell(18);
cel18.SetCellValue(target.restrictions.minOSVersion);
ICell cel19 = row1.CreateCell(19);
cel19.SetCellValue(model.requestID);
}
}
for (int i = 0; i <= 19; i++)
{
sheet.AutoSizeColumn(i);
}
fileName = fileName == "" ? DateTime.Now.ToString("yyyyMMddhhmmss") : fileName;
//打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建时不要打开该文件
using (FileStream fs = File.OpenWrite(Environment.CurrentDirectory + "\\Excel\\" + fileName + ".xls"))
{
wk.Write(fs);//向打开的这个xls文件中写入并保存。
}
Console.WriteLine("Excel built success:File name is " + fileName + ".xls");
}
private void WriteErrors(ISheet errorsSheet, COBieErrorCollection errorCollection)
{
// Write Header
var summary = errorCollection
.GroupBy(row => new { row.SheetName, row.FieldName, row.ErrorType })
.Select(grp => new { grp.Key.SheetName, grp.Key.ErrorType, grp.Key.FieldName, CountError = grp.Count(err => err.ErrorLevel == COBieError.ErrorLevels.Error), CountWarning = grp.Count(err => err.ErrorLevel == COBieError.ErrorLevels.Warning) })
.OrderBy(r => r.SheetName);
//just in case we do not have ErrorLevel property in sheet COBieErrorCollection COBieError
if (!hasErrorLevel)
{
summary = errorCollection
.GroupBy(row => new { row.SheetName, row.FieldName, row.ErrorType })
.Select(grp => new { grp.Key.SheetName, grp.Key.ErrorType, grp.Key.FieldName, CountError = grp.Count(), CountWarning = 0 })
.OrderBy(r => r.SheetName);
}
//Add Header
if (_row == 0)
{
IRow excelRow = errorsSheet.GetRow(0) ?? errorsSheet.CreateRow(0);
int col = 0;
ICell excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue("Sheet Name");
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue("Field Name");
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue("Error Type");
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue("Error Count");
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue("Warning Count");
col++;
_row++;
}
foreach (var error in summary)
{
IRow excelRow = errorsSheet.GetRow(_row + 1) ?? errorsSheet.CreateRow(_row + 1);
int col = 0;
ICell excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue(error.SheetName);
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue(error.FieldName);
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue(error.ErrorType.ToString());
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue(error.CountError);
col++;
excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue(error.CountWarning);
col++;
_row++;
}
for (int c = 0; c < 5; c++)
{
errorsSheet.AutoSizeColumn(c);
}
}
/// <summary>
/// 把数组转成Excel文件
/// </summary>
/// <param name="arr"></param>
/// <param name="filename"></param>
/// <param name="showTitle"></param>
public static void ToExcel(IList arr, string filename, bool showTitle)
{
if (arr.Count <= 0)
{
return;
}
Util.CreateDir(Path.GetDirectoryName(filename));
var type = arr[0].GetType();
var props = GetProperties(type);
//创建Excel文件名称
using (FileStream fs = File.Create(filename))
{
//创建工作薄
IWorkbook workbook = new XSSFWorkbook();
//创建sheet
ISheet sheet = workbook.CreateSheet("sheet0");
var rowNum = 0;
// 写入标题，粗体
var style = workbook.CreateCellStyle();
var font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontHeightInPoints = 16;
style.SetFont(font);
//style.FillForegroundColor = HSSFColor.Blue.Index;// 背景蓝色
//style.FillPattern = FillPattern.SolidForeground;//HSSFColor.Blue.Index;// 背景蓝色
style.Alignment = HorizontalAlignment.Center; // 居中
if (showTitle)
{
IRow rowTitle = sheet.CreateRow(rowNum);
var colTitleNum = 0;
foreach (var prop in props)
{
ICell cell = rowTitle.CreateCell(colTitleNum);
cell.SetCellValue(GetPropDesc(prop));
cell.CellStyle = style;
colTitleNum++;
}
}
//依次创建行和列 数据
foreach (var item in arr)
{
rowNum++;
IRow row = sheet.CreateRow(rowNum);
var colNum = 0;
foreach (var prop in props)
{
ICell cell = row.CreateCell(colNum);
string val = GetPropVal(item, prop);
if (val.Length > 0 && val[0] == '=')
{
// 表示公式
var fval = string.Format(val.Substring(1), GetColIdx(colNum - 1), rowNum + 1);
cell.SetCellFormula(fval);
}
else
{
cell.SetCellValue(val);
}
colNum++;
}
}
// 列宽自适应
for (var i = 0; i < props.Count; i++)
{
sheet.AutoSizeColumn(i);
}
//向excel文件中写入数据并保保存
workbook.Write(fs);
}
}
private void PopulateData(ref ISheet sheet, DataTable dt)
{
for (int rowIdx = 1; rowIdx < dt.Rows.Count; rowIdx++)
{
var row = sheet.CreateRow(rowIdx);
Int32 columnLength = dt.Columns.Count;
for (int colIdx = 0; colIdx < columnLength; colIdx++)
{
row.CreateCell(colIdx).SetCellValue(dt.Rows[rowIdx][colIdx].ToString());
sheet.AutoSizeColumn(colIdx);
}
}
}
public static bool Export(DataTable table, string filePath)
{
if (table == null)
{
return(false);
}
using (table)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
sheet.AutoSizeColumn(column.Ordinal);
}
ICellStyle dateTimeStyle = DateTimeFormat(workbook);
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
var cell = dataRow.CreateCell(column.Ordinal);
Type type = row[column].GetType();
switch (type.ToString())
{
case "System.Boolean":
{
bool boolValue = default(bool);
if (row[column] != null)
{
bool.TryParse(row[column].ToString(), out boolValue);
}
cell.SetCellValue(boolValue);
}
break;
case "System.Double":
{
double doubleValue = default(double);
if (row[column] != null)
{
double.TryParse(row[column].ToString(), out doubleValue);
}
cell.SetCellValue(doubleValue);
}
break;
case "System.DateTime":
{
DateTime dateValue = DateTime.MinValue;
if (row[column] != null)
{
DateTime.TryParse(row[column].ToString(), out dateValue);
}
cell.SetCellValue(dateValue);
cell.CellStyle = dateTimeStyle;
sheet.SetColumnWidth(column.Ordinal, (sheet.GetColumnWidth(column.Ordinal) + 100));
}
break;
default:
{
if (row[column] != null)
{
cell.SetCellValue(row[column].ToString());
}
else
{
cell.SetCellValue(string.Empty);
}
}
break;
}
}
rowIndex++;
}
FileStream fileStream = File.Create(filePath);
workbook.Write(fileStream);
fileStream.Close();
return(filePath.FileIsValid());
}
}
private static void SetAutoSizeColumn(ISheet all, string[] headers, string[] names, string[] patterns)
{
foreach (var name in names)
{
var index = System.Array.IndexOf(headers, name);
if (index >= 0)
{
all.AutoSizeColumn(index);
}
}
foreach (var pattern in patterns)
{
for (int index = 0; index < headers.Length; index++)
{
if (Regex.Match(headers[index], pattern).Success)
{
all.AutoSizeColumn(index);
}
}
}
}
public void ExceleCikar()
{
MemoryStream ms = new MemoryStream();
try
{
SaveFileDialog saveFileDialog = new SaveFileDialog
{
Filter = "Excel Dosyaları (*.xls)|*.xls",
DefaultExt = "xls",
RestoreDirectory = true,
Title = "Excele aktar",
FileName = "Rapor-" + DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss"),
AddExtension = true
};
DialogResult dialogSonucu = saveFileDialog.ShowDialog();
IWorkbook calismaKitabi = new HSSFWorkbook();
ISheet sayfa = calismaKitabi.CreateSheet();
IRow baslikSatiri = sayfa.CreateRow(0);
var font = calismaKitabi.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
foreach (DataGridViewColumn sutun in dtg.Columns)
{
var baslikHucresi = baslikSatiri.CreateCell(sutun.DisplayIndex);
baslikHucresi.SetCellValue(sutun.HeaderText);
baslikHucresi.CellStyle = calismaKitabi.CreateCellStyle();
baslikHucresi.CellStyle.SetFont(font);
}
int satirSayaci = 1;
foreach (DataGridViewRow satir in dtg.Rows)
{
IRow veriSatiri = sayfa.CreateRow(satirSayaci);
foreach (DataGridViewColumn sutun in dtg.Columns)
{
veriSatiri.CreateCell(sutun.DisplayIndex).SetCellValue(satir.Cells[sutun.Index].Value.ToString());
}
satirSayaci++;
}
for (int i = 0; i <= sayfa.GetRow(0).LastCellNum; i++)
{
sayfa.AutoSizeColumn(i);
GC.Collect();
}
calismaKitabi.Write(ms);
ms.Flush();
ms.Position = 0;
if (dialogSonucu == DialogResult.OK)
{
FileStream fs = new FileStream(saveFileDialog.FileName, FileMode.Create);
ms.WriteTo(fs);
fs.Close();
ms.Close();
}
}
catch (Exception ex)
{
ShowMessage.Error("HATA: " + ex.Message);
}
}
private void CreateHeadRow(GridView gr, HSSFWorkbook workbook, ISheet sheet)
{
ICellStyle titleStyle = workbook.CreateCellStyle();
var styleHeader = CreateHeaderStyle(workbook,
HSSFColor.WHITE.index, HSSFColor.GREEN.index);
IRow row = sheet.CreateRow(0);
ICell c1 = row.CreateCell(0);
ICell c2 = row.CreateCell(1);
ICell c3 = row.CreateCell(2);
int i = 0;
foreach (TableCell cell in gr.HeaderRow.Cells)
{
ICell iCell = row.CreateCell(i);
iCell.SetCellValue(cell.Text);
iCell.CellStyle = styleHeader;
sheet.AutoSizeColumn(i);
i++;
}
}
public void DateCells()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
IWorkbook workbook = _testDataProvider.CreateWorkbook();
FixFonts(workbook);
ISheet sheet = workbook.CreateSheet();
IDataFormat df = workbook.GetCreationHelper().CreateDataFormat();
ICellStyle style1 = workbook.CreateCellStyle();
style1.DataFormat = (/*setter*/ df.GetFormat("m"));
ICellStyle style3 = workbook.CreateCellStyle();
style3.DataFormat = (/*setter*/ df.GetFormat("mmm"));
ICellStyle style5 = workbook.CreateCellStyle(); //rotated text
style5.DataFormat = (/*setter*/ df.GetFormat("mmm/dd/yyyy"));
//Calendar calendar = Calendar.Instance;
//calendar.Set(2010, 0, 1); // Jan 1 2010
DateTime calendar = new DateTime(2010, 1, 1);
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(DateUtil.GetJavaDate(0)); //default date
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue(calendar);
cell1.CellStyle = (/*setter*/ style1);
row.CreateCell(2).SetCellValue("1"); // column 1 should be sized as '1'
ICell cell3 = row.CreateCell(3);
cell3.SetCellValue(calendar);
cell3.CellStyle = (/*setter*/ style3);
row.CreateCell(4).SetCellValue("Jan");
ICell cell5 = row.CreateCell(5);
cell5.SetCellValue(calendar);
cell5.CellStyle = (/*setter*/ style5);
row.CreateCell(6).SetCellValue("Jan/01/2010");
ICell cell7 = row.CreateCell(7);
cell7.CellFormula = (/*setter*/ "DATE(2010,1,1)");
cell7.CellStyle = (/*setter*/ style3); // should be sized as 'Jan'
// autosize not-Evaluated cells, formula cells are sized as if the result is 0
for (int i = 0; i < 8; i++)
{
sheet.AutoSizeColumn(i);
}
Assert.AreEqual(sheet.GetColumnWidth(2), sheet.GetColumnWidth(1)); // date formatted as 'm'
Assert.IsTrue(sheet.GetColumnWidth(3) > sheet.GetColumnWidth(1)); // 'mmm' is wider than 'm'
Assert.AreEqual(sheet.GetColumnWidth(4), sheet.GetColumnWidth(3)); // date formatted as 'mmm'
Assert.IsTrue(sheet.GetColumnWidth(5) > sheet.GetColumnWidth(3)); // 'mmm/dd/yyyy' is wider than 'mmm'
Assert.AreEqual(sheet.GetColumnWidth(6), sheet.GetColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'
// YK: width of not-Evaluated formulas that return data is not determined
// POI seems to conevert '0' to Excel date which is the beginng of the Excel's date system
// Evaluate formulas and re-autosize
EvaluateWorkbook(workbook);
for (int i = 0; i < 8; i++)
{
sheet.AutoSizeColumn(i);
}
Assert.AreEqual(sheet.GetColumnWidth(2), sheet.GetColumnWidth(1)); // date formatted as 'm'
Assert.IsTrue(sheet.GetColumnWidth(3) > sheet.GetColumnWidth(1)); // 'mmm' is wider than 'm'
Assert.AreEqual(sheet.GetColumnWidth(4), sheet.GetColumnWidth(3)); // date formatted as 'mmm'
Assert.IsTrue(sheet.GetColumnWidth(5) > sheet.GetColumnWidth(3)); // 'mmm/dd/yyyy' is wider than 'mmm'
Assert.AreEqual(sheet.GetColumnWidth(6), sheet.GetColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'
Assert.AreEqual(sheet.GetColumnWidth(4), sheet.GetColumnWidth(7)); // date formula formatted as 'mmm'
}
/// <summary>
/// 自动适应列宽
/// </summary>
/// <param name="sheet">需要自适应列宽的sheet表</param>
/// <param name="columnCount">列数</param>
public void AutoFitColumnWidth(ISheet sheet, int columnCount)
{
//列宽自适应，只对英文和数字有效
for (int ci = 0; ci < columnCount; ci++)
{
sheet.AutoSizeColumn(ci);
}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum < columnCount; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum < sheet.LastRowNum; rowNum++)
{
if (rowNum == 0 || rowNum == sheet.LastRowNum - 1 || rowNum == sheet.LastRowNum / 2)
{
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;
}
}
}
}
if (columnWidth > 255)
{
columnWidth = 255;
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
}
/// <summary>
/// 校验数据是否正常
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="outputStream">输出流</param>
/// <param name="sheet">数据sheet</param>
/// <param name="userInfo">用户信息</param>
/// <param name="fileName">文件名称</param>
/// <param name="DictColumnFields">英文字段名到中文列名映射关系</param>
/// <returns>ImportResult</returns>
public virtual ImportResult Verify(DataTable dt, ISheet sheet, Dictionary<string, object> extraInfo, UserInfo userInfo, string fileName, Dictionary<string, ImportVerify> DictColumnFields)
{
IWorkbook wb = sheet.Workbook;
ImportResult result = new ImportResult();
string[] arrErrorMsg = null;
string errorMsg = string.Empty;
int columnCount = dt.Columns.Count;
string columnName = string.Empty;
ImportVerify objVerify = null;
ImportVerifyParam objVerifyParam = new ImportVerifyParam { DTExcel = dt, CellValue = null, ColName = columnName, ColumnIndex = 0, RowIndex = 0 };
DataRow row = null;
object objExtra = null;
bool isCorrect = true;
//错误数据行样式
var cellErrorStyle = NPOIHelper.GetErrorCellStyle(wb);
ICell errorCell = null;
IRow sheetRow = null;
for (int i = 0, rLength = dt.Rows.Count; i < rLength; i++)
{
row = dt.Rows[i];
arrErrorMsg = new string[columnCount];
for (int j = 0; j < columnCount; j++)
{
columnName = dt.Columns[j].ColumnName;
if (DictColumnFields.TryGetValue(columnName, out objVerify))
{
if (objVerify.VerifyFunc != null)
{
objVerifyParam.CellValue = row[j];
objVerifyParam.ColumnIndex = j;
objVerifyParam.RowIndex = i;
objVerifyParam.ColName = objVerify.ColumnName;
if (extraInfo != null)
{
extraInfo.TryGetValue(columnName, out objExtra);
}
arrErrorMsg[j] = objVerify.VerifyFunc(objVerifyParam, objExtra);
}
}
}
errorMsg = string.Join("，", arrErrorMsg.Where(e => !string.IsNullOrEmpty(e)));
if (!string.IsNullOrEmpty(errorMsg))
{
isCorrect = false;
//设置错误信息
sheetRow = sheet.GetRow(StartRowIndex + 1 + i);
errorCell = sheetRow.GetCell(columnCount);
if (errorCell == null)
{
errorCell = sheetRow.CreateCell(columnCount);
}
errorCell.CellStyle = cellErrorStyle;
errorCell.SetCellValue(errorMsg);
}
}
//输出错误信息模版
if (!isCorrect)
{
sheetRow = sheet.GetRow(StartRowIndex);
errorCell = sheetRow.GetCell(columnCount);
if (errorCell == null)
{
errorCell = sheetRow.CreateCell(columnCount);
}
ICellStyle copyStyle = sheetRow.GetCell(columnCount - 1).CellStyle;
ICellStyle style = NPOIHelper.GetErrorHeadCellStyle(wb);
IFont font = style.GetFont(wb);
IFont copyfont = copyStyle.GetFont(wb);
font.FontHeight = copyfont.FontHeight;
font.FontName = copyfont.FontName;
style.FillForegroundColor = copyStyle.FillForegroundColor;
style.BorderBottom = copyStyle.BorderBottom;
style.BorderLeft = copyStyle.BorderLeft;
style.BorderRight = copyStyle.BorderRight;
style.BorderTop = copyStyle.BorderTop;
errorCell.CellStyle = style;
errorCell.SetCellValue("错误信息");
//自适应列宽度
sheet.AutoSizeColumn(columnCount);
int width = sheet.GetColumnWidth(columnCount) + 2560;
sheet.SetColumnWidth(columnCount, width > NPOIHelper.MAX_COLUMN_WIDTH ? NPOIHelper.MAX_COLUMN_WIDTH : width);
result.Message = ExcelImportHelper.GetErrorExcel(wb, fileName);
}
else
{
result.IsSuccess = true;
}
return result;
}
static void CreateCells(ISheet sheet, DataGridColumn col,
IEnumerable source, int colIndex)
{
int rowIndex = 0;
#region header
var headerStyle = GetHeaderCellStyle(sheet.Workbook);
var rowHeader = sheet.GetOrCreateRow(rowIndex++);
var cellHeader = rowHeader.GetOrCreateCell(colIndex);
cellHeader.SetCellValue(GetHeaderText(col.Header));
cellHeader.SetCellType(CellType.String);
cellHeader.CellStyle = headerStyle;
#endregion
var cellStyle = GetCellStyle(col, sheet);
if (col is DataGridBoundColumn)
{
var c = (DataGridBoundColumn)col;
var be = new BindingEvaluator(c.Binding);
foreach (var obj in source)
{
IRow row = sheet.GetOrCreateRow(rowIndex++);
ICell cell = row.GetOrCreateCell(colIndex);
cell.CellStyle = cellStyle;
be.DataContext = obj;
cell.SetCellValue(be.Value);
}
}
else if (col is System.Windows.Controls.DataGridComboBoxColumn)
{
var c = (System.Windows.Controls.DataGridComboBoxColumn)col;
var be = new BindingEvaluator(c.SelectedValueBinding);
var bValue = new BindingEvaluator(new Binding(c.SelectedValuePath));
var bDisplay = new BindingEvaluator(new Binding(c.DisplayMemberPath));
var itemSource = new Dictionary<object, object>();
foreach (var i in c.ItemsSource)
{
bValue.DataContext = i;
bDisplay.DataContext = i;
var value = bValue.Value;
if (value != null)
itemSource[value] = bDisplay.Value;
}
foreach (var obj in source)
{
IRow row = sheet.GetOrCreateRow(rowIndex++);
ICell cell = row.GetOrCreateCell(colIndex);
cell.CellStyle = cellStyle;
be.DataContext = obj;
var value = be.Value;
if (itemSource.ContainsKey(value))
cell.SetCellValue(itemSource[value]);
else
cell.SetCellValue(be.Value);
}
}
else if (col is System.Windows.Controls.DataGridTemplateColumn)
{
var c = (DataGridTemplateColumn)col;
var x = DataGridHelper.GetAttachedBinding(c);
if (x == null) return;
foreach (var obj in source)
{
var be = new BindingEvaluator(new Binding(x));
be.DataContext = obj;
IRow row = sheet.GetOrCreateRow(rowIndex++);
ICell cell = row.GetOrCreateCell(colIndex);
cell.CellStyle = cellStyle;
cell.SetCellValue(be.Value);
}
}
sheet.AutoSizeColumn(colIndex);
}
/// <summary>
/// edit this sheet
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataList"></param>
/// <param name="worksheet"></param>
/// <returns></returns>
public static ISheet ExcelSheet <T>(this IEnumerable <T> dataList, ISheet worksheet, IWorkbook workbook, ResourceManager rm = null)
{
var datatype = typeof(T);
//Insert titles
var row = worksheet.CreateRow(0);
var execlColumnHelper = new ExcelColumnHelper(datatype);
var titleList = execlColumnHelper.GetPropertyDisplayNames(rm: rm);
for (int cellNumber = 0; cellNumber < titleList.Count; cellNumber++)
{
row.CreateCell(cellNumber).SetCellValue(titleList[cellNumber]);
}
var numberOfColumns = 0;
//Insert data values
var rowNumber = 1;
foreach (var row_item in dataList)
{
///row
var valueList = execlColumnHelper.GetPropertyValues(row_item);
//var mergeRows = item.GetPropertyMergeRows();
///TODO if need merge columns
//if (mergeRows > 1)
//{
// worksheet.AddMergedRegion(new CellRangeAddress(rowNumber, rowNumber + mergeRows, rowNumber, rowNumber));
//}
var cellNumber = 0;
var new_rowData = true;
foreach (var values in valueList)
{
var tmpRow = worksheet.GetRow(rowNumber);
if (tmpRow == null)
{
tmpRow = worksheet.CreateRow(rowNumber);
}
foreach (var value in values)
{
var cell = tmpRow.CreateCell(cellNumber);
//cell.SetCellType(CellType.)
cell.SetCellValueByType(workbook, value);
numberOfColumns = cellNumber;
cellNumber++;
}
if (new_rowData)
{
new_rowData = false;
}
else
{
cellNumber = cellNumber - values.Count;
rowNumber++;
}
}
rowNumber++;
}
worksheet.Autobreaks = true;
for (int i = 0; i <= numberOfColumns; i++)
{
worksheet.AutoSizeColumn(i);
}
return(worksheet);
}
/// <summary>
/// 实体类集合导出到EXCLE2003
/// </summary>
/// <param name="cellHeard">单元头的Key和Value：{ { "UserName", "姓名" }, { "Age", "年龄" } };</param>
/// <param name="enList">数据源</param>
/// <param name="sheetName">工作表名称</param>
/// <returns>文件的下载地址</returns>
public static string EntityListToExcel2003(Dictionary <string, string> cellHeard, IList enList, string sheetName)
{
try
{
string fileName = sheetName + "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // 文件名称
string urlPath = "ExcelDownload/" + fileName; // 文件下载的URL地址，供给前台下载
string filePath = HttpContext.Current.Server.MapPath("\\" + urlPath); // 文件路径
// 1.检测是否存在文件夹，若不存在就建立个文件夹
string directoryName = Path.GetDirectoryName(filePath);
if (!Directory.Exists(directoryName))
{
Directory.CreateDirectory(directoryName);
}
// 2.解析单元格头部，设置单元头的中文名称
HSSFWorkbook workbook = new HSSFWorkbook(); // 工作簿
ISheet sheet = workbook.CreateSheet(sheetName); // 工作表
IRow row = sheet.CreateRow(0);
List <string> keys = cellHeard.Keys.ToList();
for (int i = 0; i < keys.Count; i++)
{
row.CreateCell(i).SetCellValue(cellHeard[keys[i]]); // 列名为Key的值
sheet.AutoSizeColumn(i, true);
}
// 3.List对象的值赋值到Excel的单元格里
int rowIndex = 1; // 从第二行开始赋值(第一行已设置为单元头)
foreach (var en in enList)
{
IRow rowTmp = sheet.CreateRow(rowIndex);
for (int i = 0; i < keys.Count; i++) // 根据指定的属性名称，获取对象指定属性的值
{
string cellValue = ""; // 单元格的值
object properotyValue = null; // 属性的值
System.Reflection.PropertyInfo properotyInfo = null; // 属性的信息
// 3.1 若属性头的名称包含'.',就表示是子类里的属性，那么就要遍历子类，eg：UserEn.UserName
if (keys[i].IndexOf(".") >= 0)
{
// 3.1.1 解析子类属性(这里只解析1层子类，多层子类未处理)
string[] properotyArray = keys[i].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
string subClassName = properotyArray[0]; // '.'前面的为子类的名称
string subClassProperotyName = properotyArray[1]; // '.'后面的为子类的属性名称
System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型
if (subClassInfo != null)
{
// 3.1.2 获取子类的实例
var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null);
// 3.1.3 根据属性名称获取子类里的属性类型
properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName);
if (properotyInfo != null)
{
properotyValue = properotyInfo.GetValue(subClassEn, null); // 获取子类属性的值
}
}
}
else
{
// 3.2 若不是子类的属性，直接根据属性名称获取对象对应的属性
properotyInfo = en.GetType().GetProperty(keys[i]);
if (properotyInfo != null)
{
properotyValue = properotyInfo.GetValue(en, null);
}
}
// 3.3 属性值经过转换赋值给单元格值
if (properotyValue != null)
{
cellValue = properotyValue.ToString();
// 3.3.1 对时间初始值赋值为空
if (cellValue.Trim() == "0001/1/1 0:00:00" || cellValue.Trim() == "0001/1/1 23:59:59")
{
cellValue = "";
}
}
sheet.AutoSizeColumn(i, true);
// 3.4 填充到Excel的单元格里
rowTmp.CreateCell(i).SetCellValue(cellValue);
}
rowIndex++;
}
// 4.生成文件
FileStream file = new FileStream(filePath, FileMode.Create);
workbook.Write(file);
file.Close();
// 5.返回下载路径
return(urlPath);
}
catch (Exception ex)
{
throw ex;
}
}
public static void Export(string savePath, List <dynamic> data, List <string> ignoreField = null,
Dictionary <string, string> replaceKeys = null, Func <string, object, string> funcValue = null)
{
if (string.IsNullOrWhiteSpace(savePath))
{
return;
}
if (data.Count <= 0)
{
return;
}
string dirPath = Path.GetDirectoryName(savePath);
if (!Directory.Exists(dirPath))
{
Directory.CreateDirectory(dirPath);
}
using (var fs = new FileStream(savePath, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = GetWorkbook(Path.GetExtension(savePath));
if (workbook == null)
{
return;
}
ISheet sheet = workbook.CreateSheet();
IRow headRow = sheet.CreateRow(0);
Dictionary <int, string> columns = new Dictionary <int, string>();
int keyIndex = 0;
if (data[0] != null)
{
if (data[0] is IDictionary <string, object> dataItem)
{
foreach (var key in dataItem.Keys)
{
if (ignoreField != null && ignoreField.Contains(key))
{
continue;
}
string newKey = key;
if (replaceKeys != null && replaceKeys.ContainsKey(key))
{
newKey = replaceKeys[key];
}
headRow.CreateCell(keyIndex).SetCellValue(newKey);
columns.Add(keyIndex, key);
keyIndex++;
}
}
}
sheet.AutoSizeColumn(0);
int rowIndex = 1;
foreach (var item in data)
{
headRow = sheet.CreateRow(rowIndex);
if (item is IDictionary <string, object> dicItem)
{
foreach (var column in columns)
{
object oldValue = dicItem[column.Value];
string setValue = funcValue == null?oldValue.ToStr() : funcValue(column.Value, oldValue);
headRow.CreateCell(column.Key).SetCellValue(setValue);
}
}
sheet.AutoSizeColumn(rowIndex);
rowIndex++;
}
workbook.Write(fs);
}
}
