126 lines
4.2 KiB
C#
126 lines
4.2 KiB
C#
/*
|
|
* If you need to use this object to export excel. follow the step:
|
|
* 1) Use CreateWorkbook to create new workbook
|
|
* 2) Use CreateSheet to create worksheet in workbook
|
|
* 3) Use CreateInsertableArea to prepare insertable area
|
|
* 4) Insert data with sub class
|
|
* 5) Use ExportExcel to export excel
|
|
*
|
|
* example:
|
|
* var productLca = _db.ProductLCAs.First();
|
|
* var excelExporter = new ProductLCAExcelExporter();
|
|
* var workbook = excelExporter.CreateWorkbook();
|
|
* var sheet = excelExporter.CreateSheet(workbook, "test");
|
|
* excelExporter.CreateInsertableArea(sheet, 0, 34, 0, 8);
|
|
* excelExporter.InsertLcaData(workbook, sheet, productLca);
|
|
* excelExporter.ExoprtExcel(workbook, "final2");
|
|
*/
|
|
// hack !!... 使用 NPOI 匯出 excel
|
|
using NPOI.HSSF.UserModel;
|
|
using NPOI.SS.UserModel;
|
|
using NPOI.SS.Util;
|
|
using NPOI.XSSF.UserModel;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Drawing;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Reflection;
|
|
using System.Web;
|
|
using System.Web.Http;
|
|
//using Weee.Models;
|
|
|
|
namespace Weee.Supports
|
|
{
|
|
public class ExcelExporter
|
|
{
|
|
public IWorkbook CreateWorkbook()
|
|
{
|
|
return new XSSFWorkbook();
|
|
}
|
|
|
|
public ISheet CreateSheet(IWorkbook workbook, string sheetName)
|
|
{
|
|
return workbook.CreateSheet(sheetName);
|
|
}
|
|
|
|
public void CreateInsertableArea(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol)
|
|
{
|
|
for (var i = firstRow; i <= lastRow; i++)
|
|
{
|
|
sheet.CreateRow(i);
|
|
for (var j = firstCol; j <= lastCol; j++)
|
|
{
|
|
sheet.GetRow(i).CreateCell(j);
|
|
}
|
|
}
|
|
}
|
|
|
|
protected void SetCellStyle(ISheet sheet, ICellStyle style, int firstRow, int lastRow, int firstCol, int lastCol)
|
|
{
|
|
for (int i = firstRow; i <= lastRow; i++) {
|
|
for (int j = firstCol; j <= lastCol; j++) {
|
|
sheet.GetRow(i).GetCell(j).CellStyle = style;
|
|
}
|
|
}
|
|
|
|
for (int j = firstCol; j <= lastCol; j++) {
|
|
sheet.AutoSizeColumn(j);
|
|
}
|
|
}
|
|
|
|
protected void InsertHorizontalHeader(ISheet sheet, int firstRow, int lastRow, int firstCol, string[] header)
|
|
{
|
|
sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, 0, 0));
|
|
|
|
int col = firstCol;
|
|
foreach (var title in header) {
|
|
sheet.GetRow(firstRow).GetCell(col).SetCellValue(title);
|
|
col++;
|
|
}
|
|
}
|
|
|
|
protected void InsertVerticalHeader(ISheet sheet, int firstRow, int firstCol, int lastCol, string[] header)
|
|
{
|
|
int row = firstRow;
|
|
foreach (var title in header) {
|
|
sheet.AddMergedRegion(new CellRangeAddress(row, row, firstCol, lastCol));
|
|
sheet.GetRow(row).GetCell(firstCol).SetCellValue(title);
|
|
row++;
|
|
}
|
|
}
|
|
|
|
protected ICellStyle CreateCommonStyleWithBorder(IWorkbook workbook)
|
|
{
|
|
var style = (XSSFCellStyle)workbook.CreateCellStyle();
|
|
style.BorderTop = BorderStyle.Thin;
|
|
style.BorderRight = BorderStyle.Thin;
|
|
style.BorderBottom = BorderStyle.Thin;
|
|
style.BorderLeft = BorderStyle.Thin;
|
|
style.Alignment = HorizontalAlignment.Center;
|
|
style.VerticalAlignment = VerticalAlignment.Center;
|
|
style.WrapText = true;
|
|
|
|
return style;
|
|
}
|
|
|
|
protected ICellStyle CreateCommonStyleWithBorder(IWorkbook workbook, XSSFColor cellColor)
|
|
{
|
|
var style = (XSSFCellStyle)this.CreateCommonStyleWithBorder(workbook);
|
|
style.SetFillBackgroundColor(cellColor);
|
|
style.FillPattern = FillPattern.LeastDots;
|
|
|
|
return style;
|
|
}
|
|
|
|
protected ICellStyle CreateCommonStyleWithoutBorder(IWorkbook workbook)
|
|
{
|
|
var style = workbook.CreateCellStyle();
|
|
style.Alignment = HorizontalAlignment.Center;
|
|
style.VerticalAlignment = VerticalAlignment.Center;
|
|
style.WrapText = true;
|
|
|
|
return style;
|
|
}
|
|
}
|
|
} |