private void BtnExcelOutput2_OnClick(object sender, RoutedEventArgs e)
{
ExcelContentEntity entity = new ExcelContentEntity();
entity.ColumnWidthArrays = new int[] { 30, 30, 20, 20, 20 };
entity.HeaderRows = new List <ExcelHeaderDefinition>();
var headerRows = entity.HeaderRows;
headerRows.Add(new ExcelHeaderDefinition {
StartIndex = new Point(0, 0), EndIndex = new Point(0, 0), CellValue = "科室"
});
headerRows.Add(new ExcelHeaderDefinition {
StartIndex = new Point(0, 1), EndIndex = new Point(0, 1), CellValue = "日期"
});
headerRows.Add(new ExcelHeaderDefinition {
StartIndex = new Point(0, 2), EndIndex = new Point(0, 2), CellValue = "应执行"
});
headerRows.Add(new ExcelHeaderDefinition {
StartIndex = new Point(0, 3), EndIndex = new Point(0, 3), CellValue = "已执行"
});
headerRows.Add(new ExcelHeaderDefinition {
StartIndex = new Point(0, 4), EndIndex = new Point(0, 4), CellValue = "执行率%"
});
var datas = (IList <TestEntity1>)dataGrid1.ItemsSource;
var dataRows = new List <List <ExcelCellDataDefinition> >();
foreach (var item in datas)
{
dataRows.Add(ConvertToExcelDataRow(item));
}
entity.DataRows = dataRows;
ExcelOutputHelper.OutputExcel(@"E://2.xls", "", entity);
}
/// <summary>
/// 检查Excel输出内容是否有效
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private static bool ValidateExcelContent(ExcelContentEntity entity)
{
if (entity == null)
{
throw new NotAllowException("ExcelContentEntity不允许为空！");
}
if (entity.ColumnWidthArrays == null || entity.ColumnWidthArrays.Count() <= 0)
{
throw new NotAllowException("ColumnWidthArrays没有定义");
}
if (entity.HeaderRows == null || entity.HeaderRows.Count <= 0)
{
throw new NotAllowException("HeaderRows没有定义");
}
if (entity.DataRows == null || entity.DataRows.Count <= 0)
{
throw new NotAllowException("DataRows没有定义");
}
return(true);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="filenName"></param>
/// <param name="sheetName"></param>
/// <param name="entity"></param>
public static void OutputExcel(string filenName, string sheetName, ExcelContentEntity entity)
{
IWorkbook wb = CreateFactory(filenName);
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "sheet1";
}
ValidateExcelContent(entity); // 检查Excel的内容
ICellStyle headerCellStyle = wb.CreateCellStyle();
// 居中方式
headerCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headerCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
// 边框
headerCellStyle.BorderBottom = BorderStyle.Thin;
headerCellStyle.BorderTop = BorderStyle.Thin;
headerCellStyle.BorderLeft = BorderStyle.Thin;
headerCellStyle.BorderRight = BorderStyle.Thin;
// 自动换行
headerCellStyle.WrapText = false;
headerCellStyle.FillBackgroundColor = 1;
// 字符串或者其他
ICellStyle dataStringCellStyle = wb.CreateCellStyle();
// 居中方式
dataStringCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
dataStringCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
// 边框
dataStringCellStyle.BorderBottom = BorderStyle.Thin;
dataStringCellStyle.BorderTop = BorderStyle.Thin;
dataStringCellStyle.BorderLeft = BorderStyle.Thin;
dataStringCellStyle.BorderRight = BorderStyle.Thin;
// 自动换行
dataStringCellStyle.WrapText = true;
// number类型
ICellStyle dataNumberCellStyle = wb.CreateCellStyle();
// 居中方式
dataNumberCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
dataNumberCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
// 边框
dataNumberCellStyle.BorderBottom = BorderStyle.Thin;
dataNumberCellStyle.BorderTop = BorderStyle.Thin;
dataNumberCellStyle.BorderLeft = BorderStyle.Thin;
dataNumberCellStyle.BorderRight = BorderStyle.Thin;
// 自动换行
dataNumberCellStyle.WrapText = true;
// 创建表单
ISheet sheetOne = wb.CreateSheet(sheetName);
// 设置列宽
int[] arrColumnWidths = entity.ColumnWidthArrays;
for (int i = 0; i < arrColumnWidths.Length; i++)
{
sheetOne.SetColumnWidth(i, 256 * arrColumnWidths[i]);
}
IRow dataRow = null;
ICell dataCell = null;
// 创建标题栏
int rowHeaderMaxIndex = entity.GetHeaderRowCount();
for (int i = 0; i < rowHeaderMaxIndex; i++)
{
dataRow = sheetOne.CreateRow(i);
}
foreach (var item in entity.HeaderRows)
{
for (double i = item.StartIndex.X; i <= item.EndIndex.X; i++)
{
dataRow = sheetOne.GetRow((int)i);
for (double j = item.StartIndex.Y; j <= item.EndIndex.Y; j++)
{
dataCell = dataRow.CreateCell((int)j);
dataCell.CellStyle = headerCellStyle;
}
}
dataRow = sheetOne.GetRow((int)item.StartIndex.X);
dataCell = dataRow.GetCell((int)item.StartIndex.Y);
dataCell.SetCellValue(item.CellValue);
if (item.IsMergeRowOrCell())
{
sheetOne.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress((int)item.StartIndex.X, (int)item.EndIndex.X, (int)item.StartIndex.Y, (int)item.EndIndex.Y));
}
}
// 数据行
int dataRowMaxIndex = entity.GetDataRowCount();
for (int i = 0; i < dataRowMaxIndex; i++)
{
dataRow = sheetOne.CreateRow(i + rowHeaderMaxIndex);
foreach (var dataItem in entity.DataRows[i])
{
dataCell = dataRow.CreateCell((int)dataItem.ColumnBeginEndIndex.X, (CellType)dataItem.CellDataType);
if (dataItem.CellDataType == LocalCellType.Numeric)
{
dataCell.CellStyle = dataNumberCellStyle;
}
else
{
dataCell.CellStyle = dataStringCellStyle;
}
dataCell.SetCellValue(dataItem.CellValue);
if (dataItem.IsMergeRowOrCell())
{
for (double j = dataItem.ColumnBeginEndIndex.X + 1; j <= dataItem.ColumnBeginEndIndex.Y; j++)
{
dataRow.CreateCell((int)j);
}
sheetOne.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, i, (int)dataItem.ColumnBeginEndIndex.X, (int)dataItem.ColumnBeginEndIndex.Y));
}
}
}
using (FileStream fs = File.OpenWrite(filenName))
{
wb.Write(fs);//向打开的这个Excel文件中写入表单并保存。
fs.Flush();
}
}
