private XSSFCellStyle createTitleStyle(XSSFWorkbook workbook)
{
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.WrapText = false;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 9;
font.FontName = "宋体";
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
return(style);
}
private static void setTitleCellStyle(XSSFWorkbook workbook, ICell cell)
{
XSSFCellStyle fCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFFont ffont = (XSSFFont)workbook.CreateFont();
ffont.FontHeight = 5 * 5;
ffont.FontName = "宋体";
//ffont.Color = 10;
fCellStyle.SetFont(ffont);
fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; //垂直对齐
fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //水平对齐
fCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
fCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
fCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
fCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
cell.CellStyle = fCellStyle;
}
/// <summary>
/// Generate rows and styles for a sheet
/// </summary>
/// <param name="workbook">Workbook</param>
/// <param name="columnsOptions">List of ReportColumnOptions</param>
/// <returns>Dictionary of XSSFCellStyle</returns>
private Dictionary <string, XSSFCellStyle> SetupRowStyles(IWorkbook workbook, IEnumerable <ReportColumnOptions> columnsOptions)
{
if (columnsOptions == null)
{
return(new Dictionary <string, XSSFCellStyle>());
}
Dictionary <string, XSSFCellStyle> output = new Dictionary <string, XSSFCellStyle>();
foreach (var columnOptions in columnsOptions)
{
//Get style
XSSFCellStyle cellStyle = GetStyle(workbook, columnOptions.RowStyle);
//Output
output.Add(columnOptions.Name, cellStyle);
}
return(output);
}
/// <summary>
/// Get a specific cell style
/// </summary>
/// <param name="workbook">Workbook</param>
/// <param name="reportStyle">ReportStyleCell</param>
/// <returns>XSSFCellStyle</returns>
private XSSFCellStyle GetStyle(IWorkbook workbook, ReportStyleCell reportStyle)
{
ICreationHelper creationHelper = workbook.GetCreationHelper();
//Set up column style
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
if (reportStyle == null)
{
return(cellStyle);
}
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)reportStyle.FontWeight;
//DataFormat
if (!string.IsNullOrEmpty(reportStyle.DataFormat))
{
cellStyle.SetDataFormat(creationHelper.CreateDataFormat().GetFormat(reportStyle.DataFormat));
cellStyle.Alignment = reportStyle.HorizontalAlignment;
}
//BackgroundColor
if (!string.IsNullOrEmpty(reportStyle.BackgroundColor))
{
XSSFColor backgroundColor = new XSSFColor(Color.FromName(reportStyle.BackgroundColor));
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFillForegroundColor(backgroundColor);
}
//Foreground Color
if (!string.IsNullOrEmpty(reportStyle.FontColor))
{
XSSFColor foregroundColor = new XSSFColor(Color.FromName(reportStyle.FontColor));
font.SetColor(foregroundColor);
}
//Wrap Text
cellStyle.SetFont(font);
cellStyle.WrapText = reportStyle.WrapText;
return(cellStyle);
}
private XSSFCellStyle GetCellCommonStyle(IFont font, XSSFColor color, XSSFColor foregroundColor, BorderStyle borderRight)
{
XSSFCellStyle style = _wb.CreateCellStyle() as XSSFCellStyle;
style.SetFillForegroundColor(foregroundColor);
style.FillPattern = FillPattern.SolidForeground;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetRightBorderColor(color);
style.BorderRight = borderRight;
style.SetLeftBorderColor(color);
style.BorderLeft = BorderStyle.Thin;
style.SetBottomBorderColor(color);
style.BorderBottom = BorderStyle.Thin;
style.SetTopBorderColor(color);
style.BorderTop = BorderStyle.Thin;
style.SetFont(font);
return(style);
}
public void Bug58996_UsedToWorkIn3_11_ButNotIn3_13()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFCellStyle cellStyle = workbook.CreateCellStyle() as XSSFCellStyle;
cellStyle.FillForegroundColorColor = (null);
Assert.IsNull(cellStyle.FillForegroundColorColor);
cellStyle.FillBackgroundColorColor = (null);
Assert.IsNull(cellStyle.FillBackgroundColorColor);
cellStyle.FillPattern = FillPattern.NoFill;;
Assert.AreEqual(FillPattern.NoFill, cellStyle.FillPattern);
cellStyle.SetBottomBorderColor(null);
Assert.IsNull(cellStyle.BottomBorderXSSFColor);
cellStyle.SetTopBorderColor(null);
Assert.IsNull(cellStyle.TopBorderXSSFColor);
cellStyle.SetLeftBorderColor(null);
Assert.IsNull(cellStyle.LeftBorderXSSFColor);
cellStyle.SetRightBorderColor(null);
Assert.IsNull(cellStyle.RightBorderXSSFColor);
}
/// <summary>
/// 获取单元格样式
/// </summary>
/// <param name="xssfworkbook">Excel操作类</param>
/// <param name="font">单元格字体</param>
/// <param name="fillForegroundColor">图案的颜色</param>
/// <param name="fillPattern">图案样式</param>
/// <param name="fillBackgroundColor">单元格背景</param>
/// <param name="ha">垂直对齐方式</param>
/// <param name="va">垂直对齐方式</param>
/// <returns></returns>
public static ICellStyle GetCellStyle(XSSFWorkbook xssfworkbook, IFont font, XSSFColor fillForegroundColor, FillPattern fillPattern, XSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va)
{
XSSFCellStyle cellstyle = xssfworkbook.CreateCellStyle() as XSSFCellStyle;
cellstyle.FillPattern = fillPattern;
cellstyle.Alignment = ha;
cellstyle.VerticalAlignment = va;
if (fillForegroundColor != null)
{
cellstyle.SetFillForegroundColor(fillForegroundColor);
}
if (fillBackgroundColor != null)
{
cellstyle.SetFillBackgroundColor(fillBackgroundColor);
}
if (font != null)
{
cellstyle.SetFont(font);
}
return(cellstyle);
}
public void TestCloneStyleSameWB()
{
XSSFWorkbook wb = new XSSFWorkbook();
Assert.AreEqual(1, wb.NumberOfFonts);
XSSFFont fnt = (XSSFFont)wb.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wb.NumberOfFonts);
XSSFCellStyle orig = (XSSFCellStyle)wb.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (short)18;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(fnt, orig.GetFont());
Assert.AreEqual(18, orig.DataFormat);
XSSFCellStyle clone = (XSSFCellStyle)wb.CreateCellStyle();
Assert.AreNotEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreNotEqual(fnt, clone.GetFont());
Assert.AreNotEqual(18, clone.DataFormat);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual(fnt, clone.GetFont());
Assert.AreEqual(18, clone.DataFormat);
Assert.AreEqual(2, wb.NumberOfFonts);
clone.Alignment = HorizontalAlignment.Left;
clone.DataFormat = 17;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(18, orig.DataFormat);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
private XSSFCellStyle createWarningStyle(XSSFWorkbook workbook)
{
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.WrapText = false;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 9;
font.FontName = "宋体";
style.SetFont(font);
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
XSSFColor color = new XSSFColor(new byte[] { 255, 255, 0 });
style.FillForegroundColorColor = color;
style.FillPattern = FillPattern.SolidForeground;
return(style);
}
public XSSFCellStyle CsSet3(short size, bool x)
{
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontName = "微軟正黑體";
font.FontHeightInPoints = size;
XSSFCellStyle cs = (XSSFCellStyle)workbook.CreateCellStyle();
cs.SetFont(font);
cs.WrapText = true;
cs.VerticalAlignment = VerticalAlignment.Center;
cs.Alignment = HorizontalAlignment.Left;
if (x)
{
cs.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cs.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cs.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cs.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cs.WrapText = true;
}
return(cs);
}
public void TestGetFillForegroundColor()
{
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable styles = wb.GetStylesSource();
Assert.AreEqual(1, wb.NumCellStyles);
Assert.AreEqual(2, styles.GetFills().Count);
XSSFCellStyle defaultStyle = (XSSFCellStyle)wb.GetCellStyleAt((short)0);
Assert.AreEqual(IndexedColors.Automatic.Index, defaultStyle.FillForegroundColor);
Assert.AreEqual(null, defaultStyle.FillForegroundColorColor);
Assert.AreEqual(FillPattern.NoFill, defaultStyle.FillPattern);
XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();
customStyle.FillPattern = (FillPattern.SolidForeground);
Assert.AreEqual(FillPattern.SolidForeground, customStyle.FillPattern);
Assert.AreEqual(3, styles.GetFills().Count);
customStyle.FillForegroundColor = (IndexedColors.BrightGreen.Index);
Assert.AreEqual(IndexedColors.BrightGreen.Index, customStyle.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
for (int i = 0; i < 3; i++)
{
XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();
style.FillPattern = (FillPattern.SolidForeground);
Assert.AreEqual(FillPattern.SolidForeground, style.FillPattern);
Assert.AreEqual(4, styles.GetFills().Count);
style.FillForegroundColor = (IndexedColors.BrightGreen.Index);
Assert.AreEqual(IndexedColors.BrightGreen.Index, style.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
}
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void TestGetSetColDefaultStyle()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CT_Worksheet ctWorksheet = sheet.GetCTWorksheet();
ColumnHelper columnHelper = sheet.GetColumnHelper();
// POI column 3, OOXML column 4
CT_Col col = columnHelper.GetOrCreateColumn1Based(4, false);
Assert.IsNotNull(col);
Assert.IsNotNull(columnHelper.GetColumn(3, false));
columnHelper.SetColDefaultStyle(3, 2);
Assert.AreEqual(2, columnHelper.GetColDefaultStyle(3));
Assert.AreEqual(-1, columnHelper.GetColDefaultStyle(4));
StylesTable stylesTable = workbook.GetStylesSource();
CT_Xf cellXf = new CT_Xf();
cellXf.fontId = (0);
cellXf.fillId = (0);
cellXf.borderId = (0);
cellXf.numFmtId = (0);
cellXf.xfId = (0);
stylesTable.PutCellXf(cellXf);
CT_Col col_2 = ctWorksheet.GetColsArray(0).AddNewCol();
col_2.min = (10);
col_2.max = (12);
col_2.style = (1);
col_2.styleSpecified = true;
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(11));
XSSFCellStyle cellStyle = new XSSFCellStyle(0, 0, stylesTable, null);
columnHelper.SetColDefaultStyle(11, cellStyle);
Assert.AreEqual(0u, col_2.style);
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(10));
}
bool write2Exl(StageDef domain, IWorkbook workbook)
{
foreach (DGObjectDef objectDef in domain.DGObjectContainer)
{
XSSFCellStyle cellStyle1 = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFCellStyle cellStyle2 = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFColor color1 = new XSSFColor();
XSSFColor color2 = new XSSFColor();
//根据自己需要设置RGB
byte[] colorRgb1 = { (byte)214, (byte)220, (byte)228 };
byte[] colorRgb2 = { (byte)221, (byte)235, (byte)247 };
color1.SetRgb(colorRgb1);
color2.SetRgb(colorRgb2);
cellStyle1.FillForegroundColorColor = color1;
cellStyle1.FillPattern = FillPattern.SolidForeground;
cellStyle2.FillBackgroundColorColor = color2;
cellStyle2.FillPattern = FillPattern.SolidForeground;
string sheetName = objectDef.LangStr ?? objectDef.Code;
ISheet sheet = workbook.CreateSheet(sheetName);
writeDescription(sheet, objectDef);
wrtieTitle(sheet, objectDef, cellStyle1, cellStyle2);
}
return(true);
}
private void CreateTotalColumn(ISheet sheet, IList <IRow> employesLeavesRows, IRow dayNameRow, IRow dayNumberRow, int lastCellIndex)
{
CellRangeAddress range = new CellRangeAddress(1, 2, lastCellIndex, lastCellIndex);
XSSFCellStyle style = exportStyleManager.GetStyle("workday");
sheet.AddMergedRegion(range);
ICell totalCell = dayNameRow.CreateCell(lastCellIndex);
totalCell.SetCellValue("Totale");
SetTitleBorders(sheet, range);
totalCell.CellStyle = style;
dayNumberRow.CreateCell(lastCellIndex).CellStyle = style;
for (int i = 0; i < employes.Count; i++)
{
style = GetStyle("workday", i);
ICell cell = employesLeavesRows[i].CreateCell(lastCellIndex);
cell.CellStyle = style;
range = new CellRangeAddress(i + 3, i + 3, 1, lastCellIndex - 1);
String formula = "SUM(" + range.FormatAsString() + ")";
cell.SetCellType(CellType.Formula);
cell.CellFormula = formula;
}
}
public int WriteArray_To_Excel(int rowAvailableCell, int startingCol, string[,] infoArray)
{
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(defaultCellStyle);
for (int rowCounter = 0; rowCounter <= infoArray.GetUpperBound(0); rowCounter++)
{
for (int columnCounter = 0; columnCounter <= infoArray.GetUpperBound(1); columnCounter++)
{
try
{
XSSFCell cell = (XSSFCell)sheet.GetRow(rowAvailableCell + rowCounter).CreateCell(columnCounter + startingCol);
cell.SetCellValue(infoArray[rowCounter, columnCounter]);
cell.CellStyle = cellStyle;
}
catch (Exception ex)
{
logger.logException(ex);
consoleLogger.logError(ex.Message);
}
}
}
return(startingCol + infoArray.GetUpperBound(1) + 1);
}
public void AddRange(string data, int colLen, int rowLen, XSSFCellStyle cellStyle = null)
{
row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
ICell cell = row.CreateCell(colIndex);
cell.CellStyle = cellStyle ?? defaultCellStyle;
cell.SetCellValue(creationHelper.CreateRichTextString(data));
//Да не прави Range на една клетка, че има проблем с Wrap трябва да измислим и как да оправим Autofit-a на Merge cells/rows
if (colLen != 1 || rowLen != 1)
{
CellRangeAddress cra = new CellRangeAddress(rowIndex, rowIndex + rowLen - 1, colIndex, colIndex + colLen - 1);
sheet.AddMergedRegion(cra);
if (cellStyle != null)
{
RegionUtil.SetBorderTop((int)cellStyle.BorderTop, cra, sheet, workBook);
RegionUtil.SetBorderBottom((int)cellStyle.BorderBottom, cra, sheet, workBook);
RegionUtil.SetBorderLeft((int)cellStyle.BorderLeft, cra, sheet, workBook);
RegionUtil.SetBorderRight((int)cellStyle.BorderRight, cra, sheet, workBook);
}
}
//if (cellStyle != null && cellStyle.Alignment == HorizontalAlignment.Center)
// CellUtil.SetAlignment(cell, workBook, (int)HorizontalAlignment.Center);
}
/// <summary>
/// Generate columns and styles for a sheet
/// </summary>
/// <param name="sheet">Sheet</param>
/// <param name="rowIndex">Index of the row</param>
/// <param name="columnsOptions">List of ReportColumnOptions</param>
private void SetupColumns(ISheet sheet, int rowIndex, IEnumerable <ReportColumnOptions> columnsOptions)
{
IWorkbook workbook = sheet.Workbook;
IRow row = sheet.CreateRow(rowIndex);
foreach (var columnOptions in columnsOptions.OrderBy(p => p.ColumnIndex))
{
//Set up column style
XSSFCellStyle cellStyle = GetStyle(workbook, columnOptions.HeaderStyle);
//Set Hidden state
sheet.SetColumnHidden(columnOptions.ColumnIndex, columnOptions.IsHidden);
//Set Column Width
sheet.SetColumnWidth(columnOptions.ColumnIndex, GetColumnWidth(columnOptions.Width));
//Create Cell
ICell cell = row.CreateCell(columnOptions.ColumnIndex);
cell.SetCellValue(columnOptions.HeaderName);
cell.CellStyle = cellStyle;
}
//Add freezing Pane + Filtering
sheet.CreateFreezePane(0, rowIndex + 1);
sheet.SetAutoFilter(new CellRangeAddress(rowIndex, rowIndex, 0, columnsOptions.Count() - 1));
}
public static bool DataSetToExcel(DataSet ds, string Path)
{
bool result = false;
FileStream fs = null;
XSSFWorkbook workbook = new XSSFWorkbook();
for (int i = 0; i < ds.Tables.Count; i++)
{
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.Tables[i].TableName);
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
////自定义表头
string name = ds.Tables[i].TableName;
//for (var j = 0; j < dataList[name].ToArray().Count(); j++)
//{
// headerRow.CreateCell(j).SetCellValue(dataList[name].ToArray()[j]);
// headerRow.GetCell(j).CellStyle = headStyle;
//}
}
#endregion
rowIndex = 1;
}
#endregion
foreach (DataRow row in ds.Tables[i].Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
#region 填充内容
foreach (DataColumn column in ds.Tables[i].Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string type = row[column].GetType().FullName.ToString();
newCell.SetCellValue(GetValue(row[column].ToString(), type));
}
#endregion
rowIndex++;
}
}
using (fs = File.OpenWrite(Path))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
result = true;
}
return(result);
}
///
/// DataTable转换成Excel文档流
///
///
///
static MemoryStream DatagridviewToExcel(DataGridView myDgv, CE_SystemFileType fileType)
{
MemoryStream ms = new MemoryStream();
IWorkbook workbook = null;
ISheet sheet = null;
IRow headerRow = null;
try
{
if (fileType == CE_SystemFileType.Excel)
{
#region xls
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
int columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
headerRow.CreateCell(columnIndex).SetCellValue(column.HeaderText);//If Caption not set, returns the ColumnName value
columnIndex++;
}
// handling value.
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataGridViewRow row in myDgv.Rows)
{
IRow dataRow = sheet.CreateRow(row.Index + 1);
columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
string drValue = myDgv.Rows[row.Index].Cells[column.Index].Value == null || !column.Visible ? "" :
myDgv.Rows[row.Index].Cells[column.Index].Value.ToString();
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(columnIndex);
if (column.ValueType == null)
{
column.ValueType = typeof(string);
}
switch (column.ValueType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
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;
}
columnIndex++;
}
}
#endregion
}
else if (fileType == CE_SystemFileType.Excel2010)
{
#region xlsx
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
int columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
headerRow.CreateCell(columnIndex).SetCellValue(column.HeaderText);//If Caption not set, returns the ColumnName value
columnIndex++;
}
// handling value.
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataGridViewRow row in myDgv.Rows)
{
IRow dataRow = sheet.CreateRow(row.Index + 1);
columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
string drValue = myDgv.Rows[row.Index].Cells[column.Index].Value == null || !column.Visible ? "" :
myDgv.Rows[row.Index].Cells[column.Index].Value.ToString();
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(columnIndex);
if (column.ValueType == null)
{
column.ValueType = typeof(string);
}
switch (column.ValueType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
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;
}
columnIndex++;
}
}
#endregion
}
AutoSizeColumns(sheet);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public async Task <WeeklyScheduleReportRs> Run(WeeklyScheduleReportRq rq)
{
List <ScheduleDetailDTO> dtos = await _dbContext.ScheduleDetails
.Where(x => x.Date <= rq.End && x.Date >= rq.Start)
.ProjectTo <ScheduleDetailDTO>(_mappingConfig, dest => dest.Schedule.Class, dest => dest.Schedule.Trainer)
.ToListAsync();
List <WeeklyScheduleDTO> listSession = dtos
.GroupBy(s => new { s.TimeOfDay, s.Schedule.Branch, s.Schedule.StartTime })
.Aggregate(new List <WeeklyScheduleDTO>(), (result, group) =>
{
var dayGroups = group.GroupBy(s => s.Date.DayOfWeek);
if (dayGroups.Any(g => g.Count() > 1))
{
int numberSessions = group.Count();
int count = 0;
int skip = 0;
while (true)
{
var sessions = new List <ScheduleDetailDTO>();
foreach (IGrouping <DayOfWeek, ScheduleDetailDTO> dayGroup in dayGroups)
{
if (skip >= dayGroup.Count())
{
continue;
}
ScheduleDetailDTO session = dayGroup.Skip(skip).FirstOrDefault();
if (session != null)
{
sessions.Add(session);
}
}
result.Add(new WeeklyScheduleDTO
{
Branch = group.Key.Branch, TimeOfDay = group.Key.TimeOfDay, TimeStart = group.Key.StartTime, Sessions = sessions
});
count += sessions.Count();
if (count == numberSessions)
{
break;
}
skip++;
}
}
else
{
result.Add(new WeeklyScheduleDTO
{
Branch = group.Key.Branch, TimeOfDay = group.Key.TimeOfDay, TimeStart = group.Key.StartTime, Sessions = group.ToList()
});
}
return(result);
})
.OrderBy(dto => dto.TimeOfDay)
.ThenBy(dto => dto.Branch, new BranchComparer())
.ThenBy(dto => dto.TimeStart)
.ToList();
Assembly reportAssembly = Assembly.Load("Services");
string fullAssemblyPath = string.Format("Services.Report.Template.WeeklySchedule.xlsx");
using (Stream templateResource = reportAssembly.GetManifestResourceStream(fullAssemblyPath))
{
var workbook = new XSSFWorkbook(templateResource);
var sheet = workbook.GetSheet("WeeklySchedule");
var dateRow = sheet.GetRow(DATE_ROW);
for (int i = 1; i <= 7; i++)
{
dateRow.Cells[i].SetCellValue(rq.Start.AddDays(i - 1).ToString("dd/MM/yyyy"));
}
for (int i = 1; i < listSession.Count; i++)
{
sheet.CopyRow(TIME_ROW_BEGIN, TIME_ROW_BEGIN + i);
}
for (int i = 0; i < listSession.Count; i++)
{
WeeklyScheduleDTO dto = listSession[i];
IRow row = sheet.GetRow(TIME_ROW_BEGIN + i);
if (row == null)
{
break;
}
ICell timeCell = row.GetCell(0);
timeCell.SetCellValue(dto.TimeStart.ToString(@"hh\:mm") + " - " + dto.TimeEnd.ToString(@"hh\:mm"));
foreach (ScheduleDetailDTO session in dto.Sessions)
{
DayOfWeek dayOfWeek = session.Date.DayOfWeek;
int cellIndex = dayOfWeek != DayOfWeek.Sunday ? (int)dayOfWeek : 7;
ICell sessionCell = row.GetCell(cellIndex);
IRichTextString formattedCellContent = BuildFormattedSessionCellContent(workbook, session);
sessionCell.SetCellValue(formattedCellContent);
XSSFCellStyle cellStyle = BuidlSessionCellStyle(workbook, session);
sessionCell.CellStyle = cellStyle;
}
}
using (MemoryStream exportData = new MemoryStream())
{
workbook.Write(exportData);
string saveAsFileName = string.Format("Schedule-{0:d}.xlsx", DateTime.Now).Replace("/", "-");
var rs = new WeeklyScheduleReportRs
{
FileName = saveAsFileName,
ByteArray = exportData.ToArray()
};
return(rs);
}
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static MemoryStream ExportToMemorySteram(DataTable dtSource, string strHeaderText)
{
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet() as XSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "Lee"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "宋轶"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
var dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
var format = workbook.CreateDataFormat() as HSSFDataFormat;
if (dateStyle != null)
{
if (format != null)
{
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
}
//取得列宽
var arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length;
}
for (var i = 0; i < dtSource.Rows.Count; i++)
{
for (var j = 0; j < dtSource.Columns.Count; j++)
{
var intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
var rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as XSSFSheet;
}
#region 表头及样式
{
if (sheet != null)
{
var headerRow = sheet.CreateRow(0) as XSSFRow;
if (headerRow != null)
{
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
var headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
if (headStyle != null)
{
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont() as XSSFFont;
if (font != null)
{
font.FontHeightInPoints = 20;
font.Boldweight = 700;
font.IsBold = true;
headStyle.SetFont(font);
}
headerRow.GetCell(0).CellStyle = headStyle;
}
}
}
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
if (sheet != null)
{
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
if (sheet != null)
{
XSSFRow headerRow = sheet.CreateRow(1) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
if (headStyle != null)
{
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont() as XSSFFont;
if (font != null)
{
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
}
foreach (DataColumn column in dtSource.Columns)
{
if (headerRow != null)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
if (sheet != null)
{
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
var drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
}
#endregion
rowIndex++;
}
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
///
/// DataTable转换成Excel文档流
///
///
///
static MemoryStream RenderToExcel(DataTable table, CE_SystemFileType fileType)
{
MemoryStream ms = new MemoryStream();
using (table)
{
IWorkbook workbook = null;
ISheet sheet = null;
IRow headerRow = null;
if (fileType == CE_SystemFileType.Excel)
{
#region xls
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
}
// handling value.
int rowIndex = 1;
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
string drValue = row[column] == null ? "" : row[column].ToString();
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
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;
}
}
rowIndex++;
}
#endregion
}
else if (fileType == CE_SystemFileType.Excel2010)
{
#region xlsx
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
}
// handling value.
int rowIndex = 1;
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
string drValue = row[column] == null ? "" : row[column].ToString();
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
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;
}
}
rowIndex++;
}
#endregion
}
AutoSizeColumns(sheet);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
return ms;
}
/// <summary>
///
/// </summary>
/// <param name="sheet"></param>
/// <param name="startAddress"></param>
/// <param name="dataTable"></param>
/// <param name="iWorkbook"></param>
public void FillDataFromTable(XSSFWorkbook iWorkbook, XSSFSheet sheet, string startAddress, DataTable dataTable)
{
XSSFCellStyle headerCellStyle = (XSSFCellStyle)iWorkbook.CreateCellStyle();
CT_Color ctColor = new CT_Color();
ctColor.SetRgb(112, 173, 71);
XSSFColor xssfColor = new XSSFColor(ctColor);
headerCellStyle.SetFillBackgroundColor(xssfColor);
headerCellStyle.SetFillForegroundColor(xssfColor);
XSSFFont hssfFont = iWorkbook.CreateFont() as XSSFFont;
hssfFont.FontHeightInPoints = 10;
hssfFont.FontName = "宋体";
hssfFont.Boldweight = 700;
headerCellStyle.SetFont(hssfFont);
XSSFCellStyle contentCellStyle = (XSSFCellStyle)iWorkbook.CreateCellStyle();
XSSFFont contentHssfFont = iWorkbook.CreateFont() as XSSFFont;
contentHssfFont.FontHeightInPoints = 10;
contentHssfFont.FontName = "宋体";
contentCellStyle.SetFont(contentHssfFont);
string rowIndexStr = string.Empty;
string cellIndexStr = string.Empty;
int cellIndex = 0;
for (int i = 0; i < startAddress.Length; i++)
{
int tempNum;
if (int.TryParse(startAddress[i].ToString(), out tempNum))
{
rowIndexStr += "" + tempNum;
}
else
{
cellIndexStr += "" + startAddress[i];
}
}
var rowIndex = Convert.ToInt32(rowIndexStr);
for (int i = cellIndexStr.Length - 1; i >= 0; i--)
{
if (i == cellIndexStr.Length - 1)
{
cellIndex += cellIndexStr[i] - 65;
}
else
{
cellIndex += (cellIndexStr[i] - 64) * 26;
}
}
cellIndex = 0;
//textBox1.Text += "\r\n 共有数据:" + _DataTable.Rows.Count;
int tempCellIndex = cellIndex;
try
{
//sheet分开包含表头
if (!ckbSheet.Checked)
{
rowIndex = sheet.LastRowNum;
if (rowIndex != 0)
{
rowIndex++;
}
}
//是否包含表头
XSSFRow excelRow;
XSSFCell excelCell;
if (ckbIsIncludeHeader.Checked && rowIndex <= 1)
{
excelRow = sheet.GetRow(rowIndex) as XSSFRow ?? sheet.CreateRow(rowIndex) as XSSFRow;
excelRow.HeightInPoints = 20;
foreach (DataColumn dataColumn in dataTable.Columns)
{
excelCell = excelRow.GetCell(tempCellIndex) as XSSFCell;
if (excelCell == null)
{
excelCell = excelRow.CreateCell(tempCellIndex) as XSSFCell;
}
if (string.IsNullOrEmpty(dataColumn.ColumnName))
{
excelCell.SetCellType(CellType.Blank);
excelCell.CellStyle = headerCellStyle;
tempCellIndex++;
}
else
{
excelCell.SetCellType(CellType.String);
excelCell.SetCellValue(Convert.ToString(dataColumn.ColumnName));
excelCell.CellStyle = headerCellStyle;
tempCellIndex++;
}
}
rowIndex++;
tempCellIndex = cellIndex;
}
//填充数据
foreach (DataRow dataRow in dataTable.Rows)
{
excelRow = sheet.GetRow(rowIndex) as XSSFRow ?? sheet.CreateRow(rowIndex) as XSSFRow;
excelRow.HeightInPoints = 20;
foreach (DataColumn dataColumn in dataTable.Columns)
{
excelCell = excelRow.GetCell(tempCellIndex) as XSSFCell;
if (excelCell == null)
{
excelCell = excelRow.CreateCell(tempCellIndex) as XSSFCell;
}
if (dataRow[dataColumn] == DBNull.Value || string.IsNullOrEmpty(Convert.ToString(dataRow[dataColumn])))
{
excelCell.SetCellType(CellType.Blank);
excelCell.CellStyle = contentCellStyle;
tempCellIndex++;
continue;
}
if (dataRow[dataColumn] is decimal || dataRow[dataColumn] is int)
{
excelCell.SetCellType(CellType.Numeric);
excelCell.SetCellValue(Convert.ToDouble(dataRow[dataColumn]));
excelCell.CellStyle = contentCellStyle;
}
else
{
excelCell.SetCellType(CellType.String);
excelCell.SetCellValue(Convert.ToString(dataRow[dataColumn]));
excelCell.CellStyle = contentCellStyle;
}
tempCellIndex++;
}
tempCellIndex = cellIndex;
rowIndex++;
}
}
catch (Exception ex)
{
throw new Exception("行" + rowIndex + "列" + tempCellIndex + "_" + ex.Message, ex);
}
}
public void OutExcelData()
{
string ExportFileName = "EmpData.xlsx"; string ExportFileTitle = "Data"; Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
XSSFWorkbook NpoiWB = new XSSFWorkbook();
XSSFCellStyle xCellStyle = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFDataFormat NpoiFormat = (XSSFDataFormat)NpoiWB.CreateDataFormat();
xCellStyle.SetDataFormat(NpoiFormat.GetFormat("[DbNum2][$-804]0"));
XSSFCellStyle cellStyleFontColor = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFFont font1 = (XSSFFont)NpoiWB.CreateFont(); font1.Color = (short)10; font1.IsBold = true;
cellStyleFontColor.SetFont(font1);
/// 進行產生Excel檔案流程
ISheet xSheet = NpoiWB.CreateSheet(ExportFileTitle);
List <string> listColumn = edModel.listEmployeeColumn;
/// 建立標題列
IRow xRowT = xSheet.CreateRow(0); xRowT.HeightInPoints = 40;
for (int i = 0; i < listColumn.Count; i++)
{
ICell xCellT = xRowT.CreateCell(i); xCellT.SetCellValue(listColumn[i]);
}
/// 讀取資料庫資料
List <listEmployeeDetail> ListEmpData = new List <listEmployeeDetail>();
ListEmpData = edModel.ReListEmployeeDetail();
if (ListEmpData != null && ListEmpData.Count > 0)
{
for (int i = 0; i < ListEmpData.Count; i++)
{
listEmployeeDetail item = ListEmpData[i];
List <string> list = new List <string>();
list.Add(item.lEmpIndex.ToString());
list.Add(item.lEmpName.ToString());
list.Add(item.lEmpSex.ToString());
list.Add(item.lEmpEmail.ToString());
list.Add(item.lEmpAddress.ToString());
list.Add(item.lEmpMobile.ToString());
list.Add(item.lEmpPhone.ToString());
list.Add(item.lEmpNotation.ToString());
list.Add(item.lEmpRemark.ToString());
list.Add(item.lEmpStatus.ToString());
list.Add(item.lEmpJoinDate.ToString());
list.Add(item.lEmpLeaveDate.ToString());
IRow xRowD = xSheet.CreateRow(i + 1); xRowD.HeightInPoints = 30;
for (int b = 0; b < list.Count; b++)
{
ICell xCellData = xRowD.CreateCell(b); xCellData.SetCellValue(list[b]);
}
}
}
MemoryStream MS = new MemoryStream(); NpoiWB.Write(MS);
Response.AddHeader("Content-Disposition", "attachment; filename=" + ExportFileName + "");
Response.BinaryWrite(MS.ToArray());
// ----------------------------------------------------------------------------------------------
// 釋放記憶體參數
NpoiWB = null; MS.Close(); MS.Dispose();
Response.Flush(); Response.End();
}
public FileResult ExportExcel()
{
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet("UpassLog");
var headerRow = sheet.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("Processtime");
headerRow.CreateCell(1).SetCellValue("Category (id)");
headerRow.CreateCell(2).SetCellValue("Event (id)");
headerRow.CreateCell(3).SetCellValue("ProgramID");
headerRow.CreateCell(4).SetCellValue("InstitutionID");
headerRow.CreateCell(5).SetCellValue("GUID");
headerRow.CreateCell(6).SetCellValue("TaskID");
headerRow.CreateCell(7).SetCellValue("StateID");
headerRow.CreateCell(8).SetCellValue("CardSerialNumber");
headerRow.CreateCell(9).SetCellValue("URI");
headerRow.CreateCell(10).SetCellValue("URIType");
headerRow.CreateCell(11).SetCellValue("ErrorID");
headerRow.CreateCell(12).SetCellValue("Info/Error Description");
headerRow.RowStyle = workbook.CreateCellStyle();
headerRow.RowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
sheet.CreateFreezePane(0, 1, 0, 1);
int rowNumber = 1;
var row = sheet.CreateRow(rowNumber++);
foreach (var datarow in RetrievedResult)
{
row = sheet.CreateRow(rowNumber++);
row.CreateCell(0).SetCellValue(datarow.ProcessDatetime.ToString("MM/dd/yyyy HH:mm:ss.fff", CultureInfo.InvariantCulture));
row.CreateCell(1).SetCellValue(String.Format("{0} ({1})", datarow.Category, datarow.CategoryID));
row.CreateCell(2).SetCellValue(String.Format("{0} ({1})", datarow.Event, datarow.EventID));
row.CreateCell(3).SetCellValue(datarow.ProgramID);
row.CreateCell(4).SetCellValue(datarow.InstitutionID);
row.CreateCell(5).SetCellValue(datarow.GUID);
row.CreateCell(6).SetCellValue(String.Format("{0}", datarow.TaskID));
row.CreateCell(7).SetCellValue(String.Format("{0}", datarow.StateID));
row.CreateCell(8).SetCellValue(datarow.CardSerialNumber);
row.CreateCell(9).SetCellValue(datarow.URI);
row.CreateCell(10).SetCellValue(datarow.URIType);
row.CreateCell(11).SetCellValue(datarow.ProcessErrorID);
row.CreateCell(12).SetCellValue(datarow.ProcessErrorDescr);
}
//XSSFColor foreColor = new XSSFColor(Color.Red);
XSSFColor backColor = new XSSFColor(Color.Yellow);
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
style.SetFillForegroundColor(backColor);
style.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 12;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
for (int i = 0; i <= 12; i++)
{
XSSFCell cell = (XSSFCell)headerRow.GetCell(i);
cell.CellStyle = style;
cell.CellStyle.SetFont(font);
}
for (int i = 0; i <= 12; i++)
{
sheet.AutoSizeColumn(i);
}
MemoryStream output = new MemoryStream();
workbook.Write(output);
return(File(output.ToArray(), "application/vnd.ms-excel", "UpassLog.xlsx"));
}
public void TestThemesTableColors()
{
// Load our two test workbooks
XSSFWorkbook simple = XSSFTestDataSamples.OpenSampleWorkbook(testFileSimple);
XSSFWorkbook complex = XSSFTestDataSamples.OpenSampleWorkbook(testFileComplex);
// Save and re-load them, to check for stability across that
XSSFWorkbook simpleRS = XSSFTestDataSamples.WriteOutAndReadBack(simple) as XSSFWorkbook;
XSSFWorkbook complexRS = XSSFTestDataSamples.WriteOutAndReadBack(complex) as XSSFWorkbook;
// Fetch fresh copies to test with
simple = XSSFTestDataSamples.OpenSampleWorkbook(testFileSimple);
complex = XSSFTestDataSamples.OpenSampleWorkbook(testFileComplex);
// Files and descriptions
Dictionary <String, XSSFWorkbook> workbooks = new Dictionary <String, XSSFWorkbook>();
workbooks.Add(testFileSimple, simple);
workbooks.Add("Re-Saved_" + testFileSimple, simpleRS);
workbooks.Add(testFileComplex, complex);
workbooks.Add("Re-Saved_" + testFileComplex, complexRS);
// Sanity check
//Assert.AreEqual(rgbExpected.Length, rgbExpected.Length);
// For offline testing
bool createFiles = false;
// Check each workbook in turn, and verify that the colours
// for the theme-applied cells in Column A are correct
foreach (String whatWorkbook in workbooks.Keys)
{
XSSFWorkbook workbook = workbooks[whatWorkbook];
XSSFSheet sheet = workbook.GetSheetAt(0) as XSSFSheet;
int startRN = 0;
if (whatWorkbook.EndsWith(testFileComplex))
{
startRN++;
}
for (int rn = startRN; rn < rgbExpected.Length + startRN; rn++)
{
XSSFRow row = sheet.GetRow(rn) as XSSFRow;
Assert.IsNotNull(row, "Missing row " + rn + " in " + whatWorkbook);
String ref1 = (new CellReference(rn, 0)).FormatAsString();
XSSFCell cell = row.GetCell(0) as XSSFCell;
Assert.IsNotNull(cell,
"Missing cell " + ref1 + " in " + whatWorkbook);
int expectedThemeIdx = rn - startRN;
ThemeElement themeElem = ThemeElement.ById(expectedThemeIdx);
Assert.AreEqual(themeElem.name.ToLower(), cell.StringCellValue,
"Wrong theme at " + ref1 + " in " + whatWorkbook);
// Fonts are theme-based in their colours
XSSFFont font = (cell.CellStyle as XSSFCellStyle).GetFont();
CT_Color ctColor = font.GetCTFont().GetColorArray(0);
Assert.IsNotNull(ctColor);
Assert.AreEqual(true, ctColor.IsSetTheme());
Assert.AreEqual(themeElem.idx, ctColor.theme);
// Get the colour, via the theme
XSSFColor color = font.GetXSSFColor();
// Theme colours aren't tinted
Assert.AreEqual(color.HasTint, false);
// Check the RGB part (no tint)
Assert.AreEqual(rgbExpected[expectedThemeIdx], HexDump.EncodeHexString(color.RGB),
"Wrong theme colour " + themeElem.name + " on " + whatWorkbook);
long themeIdx = font.GetCTFont().GetColorArray(0).theme;
Assert.AreEqual(expectedThemeIdx, themeIdx,
"Wrong theme index " + expectedThemeIdx + " on " + whatWorkbook
);
if (createFiles)
{
XSSFCellStyle cs = row.Sheet.Workbook.CreateCellStyle() as XSSFCellStyle;
cs.SetFillForegroundColor(color);
cs.FillPattern = FillPatternType.SolidForeground;
row.CreateCell(1).CellStyle = (cs);
}
}
if (createFiles)
{
FileStream fos = new FileStream("Generated_" + whatWorkbook, FileMode.Create, FileAccess.ReadWrite);
workbook.Write(fos);
fos.Close();
}
}
}
public void WriteList_To_Excel(int rowAvailableCell, int startingCol, int startingReadColumn, int lastReadColumn, List <List <object> > data, int type)
{
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(defaultCellStyle);
var newDataFormat = workbook.CreateDataFormat();
for (int rowCounter = 0; rowCounter < data.Count; rowCounter++)
{
for (int columnCounter = 0, dataStartColumn = startingReadColumn; dataStartColumn <= lastReadColumn; columnCounter++, dataStartColumn++)
{
try
{
XSSFCell cell = (XSSFCell)sheet.GetRow(rowAvailableCell + rowCounter).CreateCell(columnCounter + startingCol);
if (data[rowCounter][dataStartColumn] != null)
{
switch (type)
{
case 0:
double value;
if (Double.TryParse(data[rowCounter][dataStartColumn].ToString(), out value))
{
cell.SetCellValue(value);
}
else
{
cell.SetCellValue(data[rowCounter][dataStartColumn].ToString());
}
cell.CellStyle = cellStyle;
break;
case 1:
try
{
cell.SetCellValue(data[rowCounter][dataStartColumn].ToString());
cell.CellStyle = cellStyle;
}
catch (Exception ex)
{
cell.SetCellValue("");
cell.CellStyle = cellStyle;
}
break;
case 2:
int dateValue;
DateTime dateTime;
cell.CellStyle = cellStyle;
cell.CellStyle.DataFormat = newDataFormat.GetFormat(defaultDateFormat);
if (int.TryParse(data[rowCounter][dataStartColumn].ToString(), out dateValue))
{
cell.SetCellValue(dateValue);
}
else if (DateTime.TryParse(data[rowCounter][dataStartColumn].ToString(), /*CultureInfo.CreateSpecificCulture("en-US"), DateTimeStyles.None*/ out dateTime))
{
cell.SetCellValue(dateTime);
}
else
{
cell.SetCellValue(data[rowCounter][dataStartColumn].ToString());
}
break;
}
}
}
catch (Exception ex)
{
logger.logException(ex);
}
}
}
}
public void TestGetSetColDefaultStyle()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CT_Worksheet ctWorksheet = sheet.GetCTWorksheet();
ColumnHelper columnHelper = sheet.GetColumnHelper();
// POI column 3, OOXML column 4
CT_Col col = columnHelper.GetOrCreateColumn1Based(4, false);
Assert.IsNotNull(col);
Assert.IsNotNull(columnHelper.GetColumn(3, false));
columnHelper.SetColDefaultStyle(3, 2);
Assert.AreEqual(2, columnHelper.GetColDefaultStyle(3));
Assert.AreEqual(-1, columnHelper.GetColDefaultStyle(4));
StylesTable stylesTable = workbook.GetStylesSource();
CT_Xf cellXf = new CT_Xf();
cellXf.fontId = (0);
cellXf.fillId = (0);
cellXf.borderId = (0);
cellXf.numFmtId = (0);
cellXf.xfId = (0);
stylesTable.PutCellXf(cellXf);
CT_Col col_2 = ctWorksheet.GetColsArray(0).AddNewCol();
col_2.min = (10);
col_2.max = (12);
col_2.style = (1);
col_2.styleSpecified = true;
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(11));
XSSFCellStyle cellStyle = new XSSFCellStyle(0, 0, stylesTable, null);
columnHelper.SetColDefaultStyle(11, cellStyle);
Assert.AreEqual(0u, col_2.style);
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(10));
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)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 = System.DateTime.Now;
//workbook.SummaryInformation = si;
}
#endregion
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//取得列宽
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;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (XSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(1);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 300);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
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++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
public void TestCloneStyleDiffWB()
{
XSSFWorkbook wbOrig = new XSSFWorkbook();
Assert.AreEqual(1, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFFont fnt = (XSSFFont)wbOrig.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFDataFormat fmt = (XSSFDataFormat)wbOrig.CreateDataFormat();
fmt.GetFormat("MadeUpOne");
fmt.GetFormat("MadeUpTwo");
XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (fmt.GetFormat("Test##"));
Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment);
Assert.IsTrue(fnt == orig.GetFont());
Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat);
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count);
// Now a style on another workbook
XSSFWorkbook wbClone = new XSSFWorkbook();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(1, wbClone.NumCellStyles);
XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat();
XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment);
Assert.IsFalse("TestingFont" == clone.GetFont().FontName);
clone.CloneStyleFrom(orig);
Assert.AreEqual(2, wbClone.NumberOfFonts);
Assert.AreEqual(2, wbClone.NumCellStyles);
Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual("TestingFont", clone.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
// Save it and re-check
XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone);
Assert.AreEqual(2, wbReload.NumberOfFonts);
Assert.AreEqual(2, wbReload.NumCellStyles);
Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count);
XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1);
Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment);
Assert.AreEqual("TestingFont", reload.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone));
}
public void AddCellStyle(String StyleName, String FontName = "Arial", Int16 FontSize = 8,
Boolean IsItalic = false, FontUnderlineType UnderlineType = FontUnderlineType.None,
FontBoldWeight BoldWeight = FontBoldWeight.None, HorizontalAlignment HorizontalAlign = HorizontalAlignment.Left,
VerticalAlignment VerticalAlign = VerticalAlignment.Top, BorderStyle TopBorder = BorderStyle.None,
BorderStyle BottomBorder = BorderStyle.None, BorderStyle RightBorder = BorderStyle.None,
BorderStyle LeftBorder = BorderStyle.None, IndexedColors FontColor = null,
IndexedColors BackgroundColor = null, short HSSFBackgroundColorIndex = 64, byte[] XSSFColorByte = null)
{
IFont font = this.hssworkbook.CreateFont();
font.Color = ((FontColor == null) ? IndexedColors.Black.Index : FontColor.Index);
font.FontName = FontName;
font.FontHeightInPoints = FontSize;
font.IsItalic = IsItalic;
if (font.Underline != FontUnderlineType.None)
{
font.Underline = UnderlineType;
}
font.Boldweight = (short)BoldWeight;
if (this.IsNewFormat)
{
XSSFCellStyle style = (XSSFCellStyle)this.hssworkbook.CreateCellStyle();
style.SetFont(font);
style.Alignment = HorizontalAlign;
style.VerticalAlignment = VerticalAlign;
style.BorderTop = TopBorder;
style.BorderBottom = BottomBorder;
style.BorderRight = RightBorder;
style.BorderLeft = LeftBorder;
if (BackgroundColor != null)
{
style.FillForegroundColor = BackgroundColor.Index;
style.FillPattern = FillPattern.SolidForeground;
}
if (XSSFColorByte != null)
{
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundXSSFColor = new XSSFColor(XSSFColorByte);
}
if (!this.XFontStyle.ContainsKey(StyleName))
{
this.XFontStyle.Add(StyleName, style);
}
else
{
this.XFontStyle[StyleName] = style;
}
}
else
{
HSSFCellStyle style2 = (HSSFCellStyle)this.hssworkbook.CreateCellStyle();
style2.SetFont(font);
style2.Alignment = HorizontalAlign;
style2.VerticalAlignment = VerticalAlign;
style2.BorderTop = TopBorder;
style2.BorderBottom = BottomBorder;
style2.BorderRight = RightBorder;
style2.BorderLeft = LeftBorder;
if (BackgroundColor != null)
{
style2.FillForegroundColor = BackgroundColor.Index;
style2.FillPattern = FillPattern.SolidForeground;
}
else if (HSSFBackgroundColorIndex != 64)
{
style2.FillPattern = FillPattern.SolidForeground;
style2.FillForegroundColor = HSSFBackgroundColorIndex;
}
if (!this.HFontStyle.ContainsKey(StyleName))
{
this.HFontStyle.Add(StyleName, style2);
}
else
{
this.HFontStyle[StyleName] = style2;
}
}
}
