public static ExcelStyles GetExcelStyles(IWorkbook workbook)
{
ICellStyle eurStyle = workbook.CreateCellStyle();
eurStyle.DataFormat = workbook.CreateDataFormat().GetFormat(EurMoneyFormat);
ICellStyle monthStyle = workbook.CreateCellStyle();
monthStyle.DataFormat = workbook.CreateDataFormat().GetFormat(MonthDateFormat);
ICellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat(DateFormat);
ICellStyle percentage = workbook.CreateCellStyle();
percentage.DataFormat = workbook.CreateDataFormat().GetFormat(PercentageFormat);
ICellStyle rightAlignment = workbook.CreateCellStyle();
rightAlignment.Alignment = HorizontalAlignment.Right;
rightAlignment.VerticalAlignment = VerticalAlignment.Top;
ICellStyle textStyle = workbook.CreateCellStyle();
textStyle.DataFormat = workbook.CreateDataFormat().GetFormat("text");
var styles = new ExcelStyles()
{
DateStyle = dateStyle,
EurStyle = eurStyle,
HeaderStyle = rightAlignment,
MonthStyle = monthStyle,
Percentage = percentage,
TextStyle = textStyle
};
return(styles);
}
/**
* Create a library of cell styles
*/
private static Dictionary <String, ICellStyle> CreateStyles(IWorkbook wb)
{
Dictionary <String, ICellStyle> styles = new Dictionary <String, ICellStyle>();
ICellStyle style;
IFont titleFont = wb.CreateFont();
titleFont.FontHeightInPoints = 18;
titleFont.IsBold = true;
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetFont(titleFont);
styles.Add("title", style);
IFont monthFont = wb.CreateFont();
monthFont.FontHeightInPoints = 11;
monthFont.Color = (IndexedColors.White.Index);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey50Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(monthFont);
style.WrapText = (true);
styles.Add("header", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.WrapText = (true);
style.BorderRight = BorderStyle.Thin;
style.RightBorderColor = (IndexedColors.Black.Index);
style.BorderLeft = BorderStyle.Thin;
style.LeftBorderColor = (IndexedColors.Black.Index);
style.BorderTop = BorderStyle.Thin;
style.TopBorderColor = (IndexedColors.Black.Index);
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = (IndexedColors.Black.Index);
styles.Add("cell", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = wb.CreateDataFormat().GetFormat("0.00");
styles.Add("formula", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey40Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = wb.CreateDataFormat().GetFormat("0.00");
styles.Add("formula_2", style);
return(styles);
}
/**
* Create a library of cell styles
*/
private static Dictionary <String, ICellStyle> CreateStyles(IWorkbook wb)
{
Dictionary <String, ICellStyle> styles = new Dictionary <String, ICellStyle>();
ICellStyle style;
IFont titleFont = wb.CreateFont();
titleFont.FontHeightInPoints = ((short)18);
titleFont.Boldweight = (short)FontBoldWeight.BOLD;
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.SetFont(titleFont);
styles.Add("title", style);
IFont monthFont = wb.CreateFont();
monthFont.FontHeightInPoints = ((short)11);
monthFont.Color = (IndexedColors.WHITE.Index);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.GREY_50_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.SetFont(monthFont);
style.WrapText = (true);
styles.Add("header", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = (true);
style.BorderRight = BorderStyle.THIN;
style.RightBorderColor = (IndexedColors.BLACK.Index);
style.BorderLeft = BorderStyle.THIN;
style.LeftBorderColor = (IndexedColors.BLACK.Index);
style.BorderTop = BorderStyle.THIN;
style.TopBorderColor = (IndexedColors.BLACK.Index);
style.BorderBottom = BorderStyle.THIN;
style.BottomBorderColor = (IndexedColors.BLACK.Index);
styles.Add("cell", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = (wb.CreateDataFormat().GetFormat("0.00"));
styles.Add("formula", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.GREY_40_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = wb.CreateDataFormat().GetFormat("0.00");
styles.Add("formula_2", style);
return(styles);
}
private ICellStyle GetDouleStyle()
{
//设置数据显示格式
IDataFormat dataFormatCustom = _wb.CreateDataFormat();
var dateStyle = _wb.CreateCellStyle();
dateStyle.CloneStyleFrom(_commonCellStyle);
dateStyle.DataFormat = dataFormatCustom.GetFormat("0.00");
return(dateStyle);
}
/// <summary>
/// 设置百分百：%
/// </summary>
/// <param name="workBook"></param>
/// <param name="rgbs"></param>
public static void SetPercent(this ICell cell)
{
if (cell == null)
{
return;
}
cell.DealParam();
IDataFormat sdf = workBook.CreateDataFormat();
cellStyle.DataFormat = sdf.GetFormat("0%");
cell.CellStyle = cellStyle;
}
///// <summary>
///// 取得Excel的所有工作表名
///// </summary>
///// <param name="excelPath">Excel文件绝对路径</param>
///// <returns></returns>
//public static List<string> GetExcelTablesName(string excelPath)
//{
// return ExcelToWorkbook(excelPath).Select(a => a.SheetName).ToList();
//}
//switch(cell.CellType)
//{
// case HSSFCellType.BLANK:
// dr[i] = "[null]";
// break;
// case HSSFCellType.BOOLEAN:
// dr[i] = cell.BooleanCellValue;
// break;
// case HSSFCellType.NUMERIC:
// dr[i] = cell.ToString(); //This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number.
// break;
// case HSSFCellType.STRING:
// dr[i] = cell.StringCellValue;
// break;
// case HSSFCellType.ERROR:
// dr[i] = cell.ErrorCellValue;
// break;
// case HSSFCellType.FORMULA:
// default:
// dr[i] = "="+cell.CellFormula;
// break;
//}
/// <summary>
/// 取得单元格的日期类型样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="dateFormat"></param>
/// <returns></returns>
private static ICellStyle GetDateCellStyle(IWorkbook workbook, string dateFormat = "yyyy-MM-dd")
{
ICellStyle style = workbook.CreateCellStyle();
style.DataFormat = workbook.CreateDataFormat().GetFormat(dateFormat);
return(style);
}
public void Set(IWorkbook wb, string alineamiento, string formato)
{
if (wb == null)
{
throw new ArgumentNullException("El libro al que pertenece el estilo no puede estar vacío.", "wb");
}
estilo = wb.CreateCellStyle();
switch (alineamiento)
{
case "Izquierda":
estilo.Alignment = HorizontalAlignment.LEFT;
break;
case "Centro":
estilo.Alignment = HorizontalAlignment.CENTER;
break;
case "Derecha":
estilo.Alignment = HorizontalAlignment.RIGHT;
break;
default:
estilo.Alignment = HorizontalAlignment.GENERAL;
break;
}
if (!string.IsNullOrEmpty(formato))
{
IDataFormat format = wb.CreateDataFormat();
estilo.DataFormat = format.GetFormat(formato);
}
}
private void SetCell(IWorkbook workbook, ICell newCell, ICellStyle dataStyle, string drValue, bool isDateTime)
{
if (isDateTime)
{
IDataFormat format = workbook.CreateDataFormat();
dataStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm");
DateTime dtV = DateTime.Parse(drValue);
newCell.SetCellValue(dtV);
newCell.CellStyle = dataStyle;
return;
}
double dvalue = 0.0;
bool isDouble = double.TryParse(drValue, out dvalue);
if (isDouble)
{
dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.000000");
newCell.SetCellValue(dvalue);
// newCell.CellStyle = dataStyle;
}
else
{
newCell.SetCellValue(drValue);
}
}
/// <summary>
/// 构造函数初始化
/// </summary>
/// <param name="dateFormate"></param>
public ExcelTool(string dateFormate = "yyyy-mm-dd")
{
workbook = new XSSFWorkbook();
TType = typeof(T);
//1:设置属性
properties = TType.GetProperties();
//2:设置Col的默认最大长度
MaxColumnLength = 50;
//3:默认的字体样式
_defaultStyle = workbook.CreateCellStyle();
_fontColor = workbook.CreateFont();
_fontColor.Color = HSSFColor.Black.Index;
_fontColor.FontHeightInPoints = 11;
//4:设置日期样式
_dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
_dateStyle.DataFormat = format.GetFormat(dateFormate);
_dateStyle.SetFont(_fontColor);
//2:检查是否有
if (typeof(IHasFontColor).IsAssignableFrom(TType))
{
IsHasColor = true;
}
}
/// <summary>
/// 获取单元格值的默认格式
/// </summary>
/// <param name="workbook"></param>
/// <param name="cellValue"></param>
/// <returns></returns>
public virtual short GetDefaultFormat(IWorkbook workbook, CellValueDefaultFormatEnum cellValue = CellValueDefaultFormatEnum.文本)
{
IDataFormat format = workbook.CreateDataFormat();
switch (cellValue)
{
case CellValueDefaultFormatEnum.日期:
return(format.GetFormat("yyyy-MM-dd HH:mm:ss"));
case CellValueDefaultFormatEnum.时间:
return(format.GetFormat("HH:mm:ss"));
case CellValueDefaultFormatEnum.数字:
return(format.GetFormat("0.00")); //小数点后有几个0表示精确到小数点后几位
case CellValueDefaultFormatEnum.金额:
return(format.GetFormat("￥#,##0.00_ "));
case CellValueDefaultFormatEnum.百分比:
return(format.GetFormat("0.00%"));
case CellValueDefaultFormatEnum.中文大写:
return(format.GetFormat("[DbNum2][$-804]0"));
case CellValueDefaultFormatEnum.科学计数法:
return(format.GetFormat("0.00E+00"));
case CellValueDefaultFormatEnum.文本:
return(HSSFDataFormat.GetBuiltinFormat("@"));
default:
return(HSSFDataFormat.GetBuiltinFormat("@"));
}
}
protected override void ExportCore(Stream stream, TBodyList tList)
{
workBook = NPOIExtension.CreateWorkbook(exportSetting.XlsFormat);
//----set summary
//DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
//dsi.Company = "BlackEyes";
//workBook.DocumentSummaryInformation = dsi;
//SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
//si.Subject = "HKH.Exchange.ExcelExport";
//workBook.SummaryInformation = si;
//----end
//----create dateformat
dateFormat = workBook.CreateDataFormat().GetFormat(dateFormatString);
//----end
sheet = workBook.CreateSheet(exportSetting.Sheet);
CustomPageHeader(sheet.Header);
CustomTableHeader(sheet);
WriteBody(tList);
CustomTableFooter(sheet);
workBook.Write(stream);
}
private void Fill <THeader>(TBodyList tList, THeader tHeader)
{
mode = ExportMode.Fill;
exportSetting = GetExport(ExportId);
exportSetting.CalculateColumnIndex(null);
dateFormatString = string.IsNullOrEmpty(exportSetting.DateFormat) ? DEFAULTDATEFORMATSTRING : exportSetting.DateFormat;
numberFormatSTring = string.IsNullOrEmpty(exportSetting.NumberFormat) ? DEFAULTNUMBERFORMATSTRING : exportSetting.NumberFormat;
//looks as sheet index if it is Integer, otherwise TableName
int sheetIndex = 0;
if (int.TryParse(exportSetting.Sheet, out sheetIndex))
{
sheet = workBook.GetSheetAt(sheetIndex);
}
else
{
sheet = workBook.GetSheet(exportSetting.Sheet);
}
//----create date format
dateFormat = workBook.CreateDataFormat().GetFormat(dateFormatString);
//----end
WriteBody(tList);
WriteHeader(tHeader);
}
public StyleHelper(IWorkbook workbook)
{
_cellStyle = workbook.CreateCellStyle();
_workbook = workbook;
_format = _workbook.CreateDataFormat();
_font = _workbook.CreateFont();
}
public ICellStyle GetStyle()
{
var style = workBook.CreateCellStyle();
style.DataFormat = workBook.CreateDataFormat().GetFormat("yyyy/mm/dd");
return(style);
}
private IDictionary <string, ICellStyle> CreateStyles(IWorkbook workbook)
{
var headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
var xssFont = new XSSFFont(new CT_Font());
xssFont.Boldweight = (short)FontBoldWeight.Bold;
xssFont.IsBold = true;
headerStyle.SetFont(xssFont);
headerStyle.FillForegroundColor = HSSFColor.LightBlue.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.BorderTop = BorderStyle.Thin;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.TopBorderColor = HSSFColor.Black.Index;
headerStyle.BottomBorderColor = HSSFColor.Black.Index;
var dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd HH:mm:ss");
return(new Dictionary <string, ICellStyle> {
{ "header", headerStyle },
{ "date", dateStyle },
});
}
private static ICellStyle CopyStyle(this ICellStyle dCellStyle, ICellStyle sCellStyle, IWorkbook dWb, IWorkbook sWb, List <ICellStyle> dCellStyles, List <IFont> dFonts)
{
ICellStyle currCellStyle = dCellStyle;
currCellStyle.Alignment = sCellStyle.Alignment;
currCellStyle.VerticalAlignment = sCellStyle.VerticalAlignment;
currCellStyle.BorderTop = sCellStyle.BorderTop;
currCellStyle.BorderBottom = sCellStyle.BorderBottom;
currCellStyle.BorderLeft = sCellStyle.BorderLeft;
currCellStyle.BorderRight = sCellStyle.BorderRight;
currCellStyle.TopBorderColor = sCellStyle.TopBorderColor;
currCellStyle.LeftBorderColor = sCellStyle.LeftBorderColor;
currCellStyle.RightBorderColor = sCellStyle.RightBorderColor;
currCellStyle.BottomBorderColor = sCellStyle.BottomBorderColor;
currCellStyle.Indention = sCellStyle.Indention;
currCellStyle.IsHidden = sCellStyle.IsHidden;
currCellStyle.IsLocked = sCellStyle.IsLocked;
currCellStyle.Rotation = sCellStyle.Rotation;
currCellStyle.ShrinkToFit = sCellStyle.ShrinkToFit;
currCellStyle.WrapText = sCellStyle.WrapText;
currCellStyle.DataFormat = dWb.CreateDataFormat().GetFormat(sWb.CreateDataFormat().GetFormat(sCellStyle.DataFormat));
IFont sFont = sCellStyle.GetFont(sWb);
IFont dFont = dWb.FindFont(sFont, dFonts) ?? dWb.CreateFont().CopyFont(sFont, dFonts);
currCellStyle.SetFont(dFont);
dCellStyles.Add(currCellStyle);
return(currCellStyle);
}
/// <summary>
/// 新規ブックを作成
/// </summary>
/// <returns>作成の成否</returns>
protected bool CreateBook()
{
try
{
if (book != null)
{
book.Close();
}
if (isOldMode)
{
book = new HSSFWorkbook();
}
else
{
book = new XSSFWorkbook();
}
styles.Clear();
fonts.Clear();
format = book.CreateDataFormat();
return(true);
}
catch (Exception)
{
book = null;
return(false);
}
}
public static void BuildXLSFile <T> (Stream stream, IWorkbook xls, string sheet, string[] headers, IEnumerable <T> data, double timezone) where T : IDataFileFormatConverter
{
var ss = xls.CreateSheet(sheet);
var header = ss.CreateRow(0);
var col = 0;
foreach (var field in headers)
{
var cell = header.CreateCell(col++);
cell.SetCellValue(field);
}
var dateformat = xls.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss");
var datestyle = xls.CreateCellStyle();
datestyle.DataFormat = dateformat;
var row = 1;
foreach (var record in data)
{
var line = ss.CreateRow(row);
record.FillXlsRow(xls, ss, headers, line, datestyle, timezone);
row++;
}
xls.Write(stream);
}
private static ICellStyle GetDateFormat(IWorkbook workbook)
{
ICellStyle style = workbook.CreateCellStyle();
style.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-mm-dd");
return(style);
}
private static ICellStyle GetStyleCellStyle(IWorkbook workbook, string columnName, string columnDataType)
{
var style = workbook.CreateCellStyle();
style.WrapText = true;//设置自动换行
style.VerticalAlignment = VerticalAlignment.Center;
SetCellBorder(style);
switch (columnDataType)
{
case "System.DateTime": //日期类型
style.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-mm-dd");
break;
case "System.Decimal": //浮点型
case "System.Double":
case "System.Single":
if (columnName.Substring(columnName.Length - 1, 1) == "%")
{
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0%");
}
else
{
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
}
style.Alignment = HorizontalAlignment.Right;
break;
default:
style.Alignment = HorizontalAlignment.Left;
break;
}
return(style);
}
/// <summary>
/// Generates a cell style for date formatting.
/// </summary>
/// <param name="book">excel sheet.</param>
/// <param name="options">excel sheet options.</param>
/// <returns>cell style.</returns>
private ICellStyle createCellStyleForDateTime(IWorkbook book, ExcelSpreadSheetOptions options)
{
var style = book.CreateCellStyle();
style.DataFormat = book.CreateDataFormat().GetFormat(options.DataTimeCellFormat.Format);
return(style);
}
private static void SetCell(IWorkbook hssfworkbook, ICell cell, PropertyInfo propertyInfo, Model model)
{
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
IDataFormat format = hssfworkbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
var value = propertyInfo.GetValue(model);
switch (value)
{
case string s:
cell.SetCellValue($"{s.Replace("\"", "\"\"")}");
break;
case DateTimeOffset dto:
cell.SetCellValue(dto.UtcDateTime);
cell.CellStyle = cellStyle;
break;
case DateTime dto:
cell.SetCellValue(dto);
break;
case bool b:
cell.SetCellValue(b);
break;
default:
cell.SetCellValue(Convert.ToString(value));
break;
}
}
public void TestBuiltinFormats()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
IDataFormat df = wb.CreateDataFormat();
List <String> formats = HSSFDataFormat.GetBuiltinFormats();
for (int idx = 0; idx < formats.Count; idx++)
{
String fmt = formats[idx];
Assert.AreEqual(idx, df.GetFormat(fmt));
}
//default format for new cells is General
ISheet sheet = wb.CreateSheet();
ICell cell = sheet.CreateRow(0).CreateCell(0);
Assert.AreEqual(0, cell.CellStyle.DataFormat);
Assert.AreEqual("General", cell.CellStyle.GetDataFormatString());
//create a custom data format
String customFmt = "#0.00 AM/PM";
//check it is not in built-in formats
Assert.AreEqual(-1, HSSFDataFormat.GetBuiltinFormat(customFmt));
int customIdx = df.GetFormat(customFmt);
//The first user-defined format starts at 164.
Assert.IsTrue(customIdx >= HSSFDataFormat.FIRST_USER_DEFINED_FORMAT_INDEX);
//read and verify the string representation
Assert.AreEqual(customFmt, df.GetFormat((short)customIdx));
}
/// <summary>
/// 获取时间格式
/// </summary>
/// <param name="wb"></param>
/// <returns></returns>
private static ICellStyle DataTimeStyle(IWorkbook wb)
{
var cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = wb.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss");
return(cellStyle);
}
private static ICellStyle CreateIntegerStyle(IWorkbook workbook)
{
var Result = workbook.CreateCellStyle();
Result.DataFormat = workbook.CreateDataFormat().GetFormat("###0");
return(Result);
}
/// <summary>
/// 插入数据行
/// </summary>
protected static void InsertRow(DataTable dtSource, IWorkbook excelWorkbook)
{
int sheetCount = 1;
ISheet newsheet = null;
//循环数据源导出数据集
newsheet = excelWorkbook.GetSheetAt(excelWorkbook.NumberOfSheets - 1);
int rowCount = newsheet.LastRowNum;
CreateHeader(newsheet, dtSource.Columns);
ICellStyle dateCellStyle = excelWorkbook.CreateCellStyle();
IDataFormat format = excelWorkbook.CreateDataFormat();
dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
foreach (DataRow dr in dtSource.Rows)
{
rowCount++;
//超出65530条数据 创建新的工作簿
if (rowCount == 65530)
{
rowCount = 1;
sheetCount++;
newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
CreateHeader(newsheet, dtSource.Columns);
}
IRow newRow = newsheet.CreateRow(rowCount);
InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook, dateCellStyle);
}
}
private static void BuildXLSFile(Stream stream, IWorkbook xls, string sheet, IEnumerable <string> headers, IEnumerable <LogEntry> data)
{
var ss = xls.CreateSheet(sheet);
var header = ss.CreateRow(0);
var col = 0;
foreach (var field in headers)
{
var cell = header.CreateCell(col++);
cell.SetCellValue(field);
}
var dateformat = xls.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss");
var datestyle = xls.CreateCellStyle();
datestyle.DataFormat = dateformat;
var row = 1;
foreach (var record in data.OrderBy(log => log.Time))
{
var line = ss.CreateRow(row);
record.FillXlsRow(xls, ss, line, datestyle);
row++;
}
xls.Write(stream);
}
/// <summary>
/// 设置值
/// </summary>
/// <param name="cell"></param>
/// <param name="value"></param>
/// <param name="colEntity"></param>
/// <param name="cellStyle"></param>
public static void SetCellValue(this ICell cell, object value, ExportColumnEntity colEntity, ICellStyle cellStyle)
{
IWorkbook workBook = cell.Sheet.Workbook;
if (value == null)
{
cell.SetCellValue(string.Empty);
cell.CellStyle = cellStyle;
return;
}
string strValue = value.ToString();
switch (colEntity.CellType)
{
case CellType.Blank:
cell.SetCellValue(strValue);
break;
case CellType.Boolean:
bool blValue = false;
bool.TryParse(strValue, out blValue);
cell.SetCellValue(blValue);
break;
case CellType.Error:
cell.SetCellValue(strValue);
break;
case CellType.Formula:
cell.SetCellFormula(strValue);
break;
case CellType.Numeric:
double dbValue = 0;
double.TryParse(strValue, out dbValue);
cell.SetCellValue(dbValue);
break;
case CellType.String:
cell.SetCellValue(strValue);
break;
case CellType.Unknown:
cell.SetCellValue(strValue);
break;
default:
cell.SetCellValue(strValue);
break;
}
cellStyle.Alignment = colEntity.HAlign;
cellStyle.VerticalAlignment = colEntity.VAlign;
if (!string.IsNullOrEmpty(colEntity.DataFormat))
{
IDataFormat df = workBook.CreateDataFormat();
cellStyle.DataFormat = df.GetFormat(colEntity.DataFormat);
}
cell.CellStyle = cellStyle;
}
/// <summary>
/// 创建单元格值
/// </summary>
/// <param name="rowType">需要填入单元格字段的值的类型</param>
/// <param name="newCell">单元格对象</param>
/// <param name="drValue">单元格需要填入的值</param>
/// <param name="workbook">工作簿对象</param>
private static void CellCreate(Type rowType, ICell newCell, string drValue, IWorkbook workbook)
{
switch (rowType.ToString())
{
case "System.String": //字符串类型
drValue = drValue.Replace("&", "&");
drValue = drValue.Replace(">", ">");
drValue = drValue.Replace("<", "<");
newCell.SetCellValue(drValue);
break;
case "System.Guid": //GUID类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
//格式化显示
ICellStyle cellStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
newCell.CellStyle = cellStyle;
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.ToString());
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
case "System.Collections.Generic.List`1[Mysoft.Clgyl.Utility.Model.Excel.RowFile]":
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue(drValue);
break;
}
}
private static ICellStyle GetDateFormat(this IWorkbook workbook)
{
IDataFormat format = workbook.CreateDataFormat();
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
return(cellStyle);
}
public override void RegisterCustomStyle(IWorkbook workbook)
{
var format = workbook.CreateDataFormat();
ICellStyle cellStyle = workbook.CreateCellStyle();
RegisterContentStyle(workbook, cellStyle);
cellStyle.DataFormat = format.GetFormat("0.00%");
ICellStyle cellStyle2 = workbook.CreateCellStyle();
RegisterContentStyle(workbook, cellStyle2);
cellStyle2.DataFormat = format.GetFormat("￥0.00");
}
/// <summary>
/// Applies the style by copying the fluent style to the NPOI style object,.
/// </summary>
/// <param name="workbook">The workbook.</param>
/// <param name="destination">The destination NPOI style object to apply the FluentStyle to.</param>
public void ApplyStyle(IWorkbook workbook, ICellStyle destination)
{
// If users sets format string this overrides the DataFormat property.
if (Format != null)
{
var dataFormat = workbook.CreateDataFormat();
DataFormat = dataFormat.GetFormat(Format);
}
if (Alignment != null) destination.Alignment = Alignment.Value;
if (BorderBottom != null) destination.BorderBottom = BorderBottom.Value;
if (BorderDiagonal != null) destination.BorderDiagonal = BorderDiagonal.Value;
if (BorderDiagonalColor != null) destination.BorderDiagonalColor = BorderDiagonalColor.Value;
if (BorderDiagonalLineStyle != null) destination.BorderDiagonalLineStyle = BorderDiagonalLineStyle.Value;
if (BorderLeft != null) destination.BorderLeft = BorderLeft.Value;
if (BorderRight != null) destination.BorderRight = BorderRight.Value;
if (BorderTop != null) destination.BorderTop = BorderTop.Value;
if (BottomBorderColor != null) destination.BottomBorderColor = BottomBorderColor.Value;
if (DataFormat != null) destination.DataFormat = DataFormat.Value;
if (FillBackgroundColor != null) destination.FillBackgroundColor = FillBackgroundColor.Value;
if (FillForegroundColor != null) destination.FillForegroundColor = FillForegroundColor.Value;
if (FillPattern != null) destination.FillPattern = FillPattern.Value;
if (Indention != null) destination.Indention = Indention.Value;
if (LeftBorderColor != null) destination.LeftBorderColor = LeftBorderColor.Value;
if (RightBorderColor != null) destination.RightBorderColor = RightBorderColor.Value;
if (Rotation != null) destination.Rotation = Rotation.Value;
if (ShrinkToFit != null) destination.ShrinkToFit = ShrinkToFit.Value;
if (TopBorderColor != null) destination.TopBorderColor = TopBorderColor.Value;
if (VerticalAlignment != null) destination.VerticalAlignment = VerticalAlignment.Value;
if (WrapText != null) destination.WrapText = WrapText.Value;
if (FontIsNeeded)
{
var font = workbook.CreateFont();
if (FontWeight != null) font.Boldweight = (short)FontWeight.Value;
if (Charset != null) font.Charset = Charset.Value;
if (Color != null) font.Color = Color.Value;
if (FontHeight != null) font.FontHeight = FontHeight.Value;
if (FontHeightInPoints != null) font.FontHeightInPoints = FontHeightInPoints.Value;
if (FontName != null) font.FontName = FontName;
if (Italic != null) font.IsItalic = Italic.Value;
if (Strikeout != null) font.IsStrikeout = Strikeout.Value;
if (SuperScript != null) font.TypeOffset = SuperScript.Value;
if (Underline != null) font.Underline = Underline.Value;
destination.SetFont(font);
}
}
//生成Dictionary<NickName, ICellStyle>， 每一列对应每一个ICellStyle，通过NickName来获取ICellStyle
private Dictionary<string, ICellStyle> GetContentStyles(IWorkbook workbook)
{
Dictionary<string, ICellStyle> NameToStyle = new Dictionary<string, ICellStyle>();
IDataFormat format = workbook.CreateDataFormat();
foreach (Tk5FieldInfoEx fieldInfo in fMetaData.Table.TableList)
{
ICellStyle cellStyle = BorderAndFontSetting(workbook, fieldInfo, Model.Content);
if (fieldInfo.Extension != null && !string.IsNullOrEmpty(fieldInfo.Extension.Format))
cellStyle.DataFormat = format.GetFormat("@");
else
{
switch (fieldInfo.DataType)
{
case TkDataType.Long:
case TkDataType.Int:
case TkDataType.Short:
case TkDataType.Byte:
case TkDataType.Double:
case TkDataType.Decimal:
case TkDataType.Money:
if (fieldInfo.DataType == TkDataType.Money)
cellStyle.DataFormat = format.GetFormat("￥#,##0");
else
cellStyle.DataFormat = format.GetFormat("0");
break;
case TkDataType.String:
case TkDataType.Text:
case TkDataType.Guid:
case TkDataType.Xml:
cellStyle.DataFormat = format.GetFormat("@");
break;
case TkDataType.DateTime:
case TkDataType.Date:
if (fieldInfo.DataType == TkDataType.DateTime)
cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm");
else
cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
break;
}
}
NameToStyle.Add(fieldInfo.NickName, cellStyle);
}
return NameToStyle;
}
public void doTest49928Core(IWorkbook wb)
{
DataFormatter df = new DataFormatter();
ISheet sheet = wb.GetSheetAt(0);
ICell cell = sheet.GetRow(0).GetCell(0);
ICellStyle style = cell.CellStyle;
String poundFmt = "\"\u00a3\"#,##0;[Red]\\-\"\u00a3\"#,##0";
// not expected normally, id of a custom format should be greater
// than BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX
short poundFmtIdx = 6;
Assert.AreEqual(poundFmt, style.GetDataFormatString());
Assert.AreEqual(poundFmtIdx, style.DataFormat);
Assert.AreEqual("\u00a31", df.FormatCellValue(cell));
IDataFormat dataFormat = wb.CreateDataFormat();
Assert.AreEqual(poundFmtIdx, dataFormat.GetFormat(poundFmt));
Assert.AreEqual(poundFmt, dataFormat.GetFormat(poundFmtIdx));
}
/**
* Create a library of cell styles
*/
private static Dictionary<String, ICellStyle> CreateStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
ICellStyle style;
IFont titleFont = wb.CreateFont();
titleFont.FontHeightInPoints = ((short)18);
titleFont.Boldweight = (short)FontBoldWeight.BOLD;
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.SetFont(titleFont);
styles.Add("title", style);
IFont monthFont = wb.CreateFont();
monthFont.FontHeightInPoints = ((short)11);
monthFont.Color = (IndexedColors.WHITE.Index);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.GREY_50_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.SetFont(monthFont);
style.WrapText = (true);
styles.Add("header", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = (true);
style.BorderRight = BorderStyle.THIN;
style.RightBorderColor = (IndexedColors.BLACK.Index);
style.BorderLeft = BorderStyle.THIN;
style.LeftBorderColor = (IndexedColors.BLACK.Index);
style.BorderTop = BorderStyle.THIN;
style.TopBorderColor = (IndexedColors.BLACK.Index);
style.BorderBottom = BorderStyle.THIN;
style.BottomBorderColor = (IndexedColors.BLACK.Index);
styles.Add("cell", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = (wb.CreateDataFormat().GetFormat("0.00"));
styles.Add("formula", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.GREY_40_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = wb.CreateDataFormat().GetFormat("0.00");
styles.Add("formula_2", style);
return styles;
}
public void Execute()
{
//set destination
if (File.Exists(_settings.ApplicationSettings.FileLocations.Destination))
{
File.Delete(_settings.ApplicationSettings.FileLocations.Destination);
}
//get source files
if (File.Exists(_settings.ApplicationSettings.FileLocations.Source))
{
_sourceFiles.Add(_settings.ApplicationSettings.FileLocations.Source);
}
else if (Directory.Exists(_settings.ApplicationSettings.FileLocations.Source))
{
GetSourceFiles(_settings.ApplicationSettings.FileLocations.Source);
}
_workbook = new XSSFWorkbook();
//create sheets
foreach (var sheet in _settings.Sheets)
{
if (_workbook.GetSheet(sheet.Name) == null)
{
_workbook.CreateSheet(sheet.Name);
}
}
var dateStyle = _workbook.CreateCellStyle();
dateStyle.DataFormat = _workbook.CreateDataFormat().GetFormat("dd-MM-yyyy");
//add columns
foreach (var sheet in _settings.Sheets)
{
var wbsheet = _workbook.GetSheet(sheet.Name);
sheet.ItemCount = 0;
foreach (var column in sheet.Elements.Columns)
{
ICell cell = wbsheet.GetOrCreateCell(sheet.HeaderRow, column.HeaderCol);
cell.SetCellValue(column.Header);
cell.CellStyle = dateStyle;
if (column.Width != 0)
{
wbsheet.SetColumnWidth(column.HeaderCol, column.Width);
}
wbsheet.GetRow(sheet.HeaderRow).RowStyle = dateStyle;
}
}
//run through all documents
for (int i = 0; i < _sourceFiles.Count; i++)
{
Console.Write(string.Format("{0}...", Path.GetFileName(_sourceFiles[i])));
var srcworkbook = WorkbookFactory.Create(_sourceFiles[i]);
foreach (var sheet in _settings.Sheets)
{
var wbsheet = _workbook.GetSheet(sheet.Name);
var srcwbsheet = srcworkbook.GetSheet(sheet.Name);
if (srcwbsheet != null)
{
//find first row by searching first column header name
var allheaders = (from a in sheet.Elements.Columns select a.Header.ToLower()).ToArray();
var indexes = new int?[allheaders.Length];
var rowenum = srcwbsheet.GetRowEnumerator();
while (rowenum.MoveNext())
{
var r = rowenum.Current as XSSFRow;
IRow ActiveRow = null;
var searchHeader = (from a in indexes where a != null select a).FirstOrDefault() == null;
if (!searchHeader)
{
ActiveRow = wbsheet.GetOrCreateRow(sheet.ItemCount + sheet.HeaderRow + 1);
sheet.ItemCount++;
ActiveRow.Height = r.Height;
}
foreach (var c in r.Cells)
{
if (searchHeader)
{
var index = Array.IndexOf(allheaders, (c.StringCellValue ?? "").ToLower());
if (index >= 0)
{
indexes[index] = c.ColumnIndex;
if (sheet.ItemCount == 0)
{
if (sheet.Elements.Columns[index].Width == 0)
{
wbsheet.SetColumnWidth(sheet.Elements.Columns[index].HeaderCol, Math.Max(srcwbsheet.GetColumnWidth(c.ColumnIndex), wbsheet.GetColumnWidth(sheet.Elements.Columns[index].HeaderCol)));
}
if (r.RowStyle != null)
{
wbsheet.GetRow(sheet.HeaderRow).RowStyle.CloneStyleFrom(r.RowStyle);
}
wbsheet.GetRow(sheet.HeaderRow).GetCell(sheet.Elements.Columns[index].HeaderCol).CellStyle.CloneStyleFrom(c.CellStyle);
}
}
}
else
{
var col = Array.IndexOf(indexes, c.ColumnIndex);
if (col >= 0)
{
var cell = ActiveRow.GetOrCreateCol(sheet.Elements.Columns[col].HeaderCol);
if (r.RowStyle != null)
{
ActiveRow.RowStyle = dateStyle;
ActiveRow.RowStyle.CloneStyleFrom(r.RowStyle);
}
cell.CellStyle = dateStyle;
switch (c.CellType)
{
case CellType.Numeric:
try
{
cell.SetCellValue(c.DateCellValue);
}
catch
{
try
{
cell.SetCellValue(c.NumericCellValue);
}
catch
{
}
}
break;
case CellType.Boolean:
cell.SetCellValue(c.BooleanCellValue);
break;
case CellType.String:
default:
cell.SetCellValue(c.StringCellValue);
break;
}
cell.SetCellType(c.CellType);
cell.CellStyle.CloneStyleFrom(c.CellStyle);
if (sheet.Elements.Columns[col].Width == 0)
{
wbsheet.SetColumnWidth(sheet.Elements.Columns[col].HeaderCol, Math.Max(srcwbsheet.GetColumnWidth(c.ColumnIndex), wbsheet.GetColumnWidth(sheet.Elements.Columns[col].HeaderCol)));
}
}
}
}
}
}
}
Console.WriteLine("gedaan");
}
using (FileStream stream = new FileStream(_settings.ApplicationSettings.FileLocations.Destination, FileMode.Create, FileAccess.Write))
{
_workbook.Write(stream);
}
}
private ICellStyle GetCellStyleDateTime(IWorkbook hssfworkbook)
{
ICellStyle cellStyleDateTime = hssfworkbook.CreateCellStyle();
CellStyleBasic(cellStyleDateTime);
cellStyleDateTime.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("yyyy/m/d h:mm:ss");
return cellStyleDateTime;
}
/**
* Create a library of cell styles
*/
private static Dictionary<String, ICellStyle> CreateStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
ICellStyle style;
IFont titleFont = wb.CreateFont();
titleFont.FontHeightInPoints = ((short)18);
titleFont.Boldweight = (short)FontBoldWeight.Bold;
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetFont(titleFont);
styles.Add("title", style);
IFont monthFont = wb.CreateFont();
monthFont.FontHeightInPoints = ((short)11);
monthFont.Color = (IndexedColors.White.Index);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey50Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(monthFont);
style.WrapText = (true);
styles.Add("header", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.WrapText = (true);
style.BorderRight = BorderStyle.Thin;
style.RightBorderColor = (IndexedColors.Black.Index);
style.BorderLeft = BorderStyle.Thin;
style.LeftBorderColor = (IndexedColors.Black.Index);
style.BorderTop = BorderStyle.Thin;
style.TopBorderColor = (IndexedColors.Black.Index);
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = (IndexedColors.Black.Index);
styles.Add("cell", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = (wb.CreateDataFormat().GetFormat("0.00"));
styles.Add("formula", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey40Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = wb.CreateDataFormat().GetFormat("0.00");
styles.Add("formula_2", style);
return styles;
}
public static void DataFormat(this ICellStyle style, IWorkbook wb, string v)
{
var df = wb.CreateDataFormat();
style.DataFormat = df.GetFormat(v); ;
}
private static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 14;
font12.FontName = "宋体";
IFont font = wb.CreateFont();
font.FontName = "宋体";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OLIVE_GREEN.BLUE.index;
fontcolorblue.IsItalic = true; //下划线
fontcolorblue.FontName = "宋体";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.HAIR;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.HAIR;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
cellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//背景图形，我没有用到过。感觉很丑
// cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.GREEN.index;
//cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
// cellStyle.FillForegroundColor = HSSFColor.WHITE.index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
cellStyle.FillBackgroundColor = HSSFColor.MAROON.index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
//自动换行
// cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.头:
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.SetFont(font12);
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = 1;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
}
return cellStyle;
}
private void GenerateReport(IWorkbook workbook)
{
JobTracker jobtracker = new JobTracker();
List<JobTracker> projectList = jobtracker.GetDistinctProjectListIncludingForApproval(Convert.ToDateTime(txtBoxFrom.Text+ " 00:00:00"), Convert.ToDateTime(txtBoxTo.Text+" 23:59:59"));
int currentrow = 1;
IDataFormat format = workbook.CreateDataFormat();
ICellStyle fontBoldNoBorder = CreateSheetStyle(workbook, false, false, false, false, true, false, false, false,false);
ICellStyle fontBoldAllBorder = CreateSheetStyle(workbook, true, true, true, true, true, false, false, false);
ICellStyle fontCurrencyBoldRigthBottom = CreateSheetStyle(workbook, false, false, true, true, true, false, false, false);
fontCurrencyBoldRigthBottom.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
ICellStyle fontBoldTopBottom = CreateSheetStyle(workbook, false, true, false, true, true, false, false, false);
fontCurrencyBoldRigthBottom.Alignment = HorizontalAlignment.Center;
fontCurrencyBoldRigthBottom.VerticalAlignment = VerticalAlignment.Center;
ICellStyle fontCurrencyBoldAllBorder = CreateSheetStyle(workbook, true, true, true, true, true, false, false, false);
fontBoldAllBorder.Alignment = HorizontalAlignment.Center;
fontCurrencyBoldAllBorder.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
ICellStyle fontNormalBorderLeftRight = CreateSheetStyle(workbook, true, false, true, false, false, false, false, false);
fontBoldAllBorder.VerticalAlignment = VerticalAlignment.Center;
ICellStyle fontNormalBorderLeftRightBottom = CreateSheetStyle(workbook, true, false, true,true, false, false, false, false);
ICellStyle fontCurrencyBorderLeftRight = CreateSheetStyle(workbook, true, false, true, false, false, false, false, false);
fontCurrencyBorderLeftRight.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
ICellStyle fontCurrencyBorderLeftRightBottom = CreateSheetStyle(workbook, true, false, true, true, false, false, false, false);
fontCurrencyBorderLeftRightBottom.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
//fontNormalBorderLeftRightBottom.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
//fontNormalBorderLeftRightBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//fontNormalBorderLeftRightBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//fontCurrencyBorderLeftRightBottom.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
//fontCurrencyBorderLeftRightBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//fontCurrencyBorderLeftRightBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//fontCurrencyBoldRigthBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
ISheet sheetReport = workbook.GetSheet("Report");
foreach (JobTracker project in projectList)
{
//Running Total Variable
double runningNTApproved = 0;
double runningNTForApproval = 0;
double runningOTApproved = 0;
double runningOTForApproval = 0;
double runningNTApprovedCost = 0;
double runningNTForApprovalCost = 0;
double runningOTApprovedCost = 0;
double runningOTForApprovalCost = 0;
string jobheader = project.HWNo == null ? "" : project.HWNo.Trim() == "" ? "" : "HW SO: " + project.HWNo;
jobheader += project.SWNo == null ? "" : project.SWNo.Trim() == "" ? "" : jobheader == "" ? "SW SO: " + project.SWNo : "; SW SO: " + project.SWNo;
jobheader += project.EvalNo == null ? "" : project.EvalNo.Trim() == "" ? "" : jobheader == "" ? "EVAL NO: " + project.EvalNo : "; EVAL NO: " + project.EvalNo;
currentrow += 2;
IRow row = sheetReport.CreateRow(currentrow++);
ICell cell = row.CreateCell(0);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue(jobheader);
for (int i = 1; i < 11; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = fontBoldAllBorder;
}
#region Header Upper Row
row = sheetReport.CreateRow(currentrow);
cell = row.CreateCell(0);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("JobStage");
cell = row.CreateCell(1);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Time");
for (int i = 2; i < 5; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = fontBoldAllBorder;
}
cell = row.CreateCell(5);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Labor Cost");
for (int i = 6; i < 9; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = fontBoldAllBorder;
}
cell = row.CreateCell(9);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Total");
cell = row.CreateCell(10);
cell.CellStyle = fontBoldAllBorder;
//Merging
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow -1, currentrow-1, 0, 10));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,1,4));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,5,8));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,9,10));
#endregion
#region Header Lower Row
row = sheetReport.CreateRow(++currentrow);
cell = row.CreateCell(0);
cell.CellStyle = fontBoldAllBorder;
for (int i = 0; i < 5; i+=4)
{
cell = row.CreateCell(1 + i);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Normal Time(Approved)");
cell = row.CreateCell(2 + i);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Normal Time(For Approval)");
cell = row.CreateCell(3 + i);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Overtime(Approved)");
cell = row.CreateCell(4 + i);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Overtime(For Approved)");
}
cell = row.CreateCell(9);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Time");
cell = row.CreateCell(10);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Cost");
//Merging
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow - 1, currentrow, 0, 0));
#endregion
#region Data
List<JobTracker> uniqueJobType = jobtracker.GetUniqueComputedJobType(project.HWNo == null ? "" : project.HWNo.Trim(), project.HWNo == null ? "" : project.SWNo.Trim());
#region DETAIL Page
ISheet detailSheet = workbook.CreateSheet(jobheader.Replace(":",""));
int detailRowCount = 0;
IRow detailRow = detailSheet.CreateRow(detailRowCount++);
detailRow.CreateCell(0).SetCellValue(jobheader);
detailRow.GetCell(0).CellStyle = fontBoldNoBorder;
detailRowCount++;
detailRow = detailSheet.CreateRow(detailRowCount++);
detailRow.CreateCell(0).SetCellValue("Labor Cost");
detailRow.GetCell(0).CellStyle = fontBoldNoBorder;
detailRow = detailSheet.CreateRow(detailRowCount++);
string[] detailHeader = { "Department","Name", "Job Task", "Start Time", "End Time","Date", "Remarks","Status", "Normal Time","Overtime", "Normal Time Cost","Overtime Cost" };
for (int i = 0; i < 12; i++)
{
ICell detailCell = detailRow.CreateCell(i);
detailCell.CellStyle = fontBoldAllBorder;
detailCell.SetCellValue(detailHeader[i]);
}
for (int i = 0; i < uniqueJobType.Count; i++)
{
row = sheetReport.CreateRow(++currentrow);
cell = row.CreateCell(0);
cell.SetCellValue(uniqueJobType[i].jobtype);
if (i < uniqueJobType.Count - 1)
{
cell.CellStyle = fontNormalBorderLeftRight;
}
else
{
cell.CellStyle = fontNormalBorderLeftRightBottom;
}
List<JobTracker> data = jobtracker.GetJobTrackerListWithJobTypeIdHWSO(Convert.ToInt32(uniqueJobType[i].JobTypeId), project.HWNo == null ? "" : project.HWNo.Trim(), project.SWNo == null ? "" : project.SWNo.Trim(), true);
double ntApproved = 0;
double ntForApproval = 0;
double otApproved = 0;
double otForApproval = 0;
double ntApprovedCost = 0;
double ntForApprovalCost = 0;
double otApprovedCost = 0;
double otForApprovalCost = 0;
for (int x = 0; x < data.Count;x++ )
{
detailRow = detailSheet.CreateRow(detailRowCount++);
if (data[x].Status == "Approved")
{
ntApproved += data[x].normalmins;
otApproved += data[x].otmins;
ntApprovedCost += data[x].normalcost;
otApprovedCost += data[x].otcost;
}
else if (data[x].Status == "For Approval")
{
ntForApproval += data[x].normalmins;
otForApproval += data[x].otmins;
ntForApprovalCost += data[x].normalcost;
otForApprovalCost += data[x].otcost;
}
for (int y = 0; y < 12; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue(data[x].department);
else if (y == 1)
detailCell.SetCellValue(data[x].fullname);
else if (y == 2)
detailCell.SetCellValue(data[x].jobtype);
else if (y == 3)
detailCell.SetCellValue(Convert.ToDateTime(data[x].StartTime).ToString("hh:mm tt"));
else if (y == 4)
detailCell.SetCellValue(Convert.ToDateTime(data[x].EndTime).ToString("hh:mm tt"));
else if (y == 5)
detailCell.SetCellValue(Convert.ToDateTime(data[x].ScheduleDate).ToString("dd-MMM-yyyy"));
else if (y == 6)
detailCell.SetCellValue(data[x].Remarks);
else if (y == 7)
detailCell.SetCellValue(data[x].Status);
else if (y == 8)
detailCell.SetCellValue(data[x].normalhours.Trim() == "0 min" ? "-" : data[x].normalhours);
else if (y == 9)
detailCell.SetCellValue(data[x].othours.Trim() == "0 min" ? "-" : data[x].othours);
else if (y == 10)
detailCell.SetCellValue(data[x].normalcost);
else if (y == 11)
detailCell.SetCellValue(data[x].otcost);
if (x == data.Count - 1 && i == uniqueJobType.Count -1)
{
if (y < 10)
detailCell.CellStyle = fontNormalBorderLeftRightBottom;
else
detailCell.CellStyle = fontCurrencyBorderLeftRightBottom;
}
else
{
if (y < 10)
detailCell.CellStyle = fontNormalBorderLeftRight;
else
detailCell.CellStyle = fontCurrencyBorderLeftRight;
}
}
}
#endregion
#region GETTING THE TIME
//Running Time
runningNTApproved += ntApproved;
runningNTForApproval += ntForApproval;
runningOTApproved += otApproved;
runningOTForApproval += otForApproval;
runningNTApprovedCost += ntApprovedCost;
runningNTForApprovalCost += ntForApprovalCost;
runningOTApprovedCost += otApprovedCost;
runningOTForApprovalCost += otForApprovalCost;
string NTApproved = GenerateTimeConsumed(ntApproved);
string NTForApproval = GenerateTimeConsumed(ntForApproval);
string OTApproved = GenerateTimeConsumed(otApproved);
string OTForApproval = GenerateTimeConsumed(otForApproval);
string StotalTime = GenerateTimeConsumed(ntApproved + otApproved + ntForApproval + otForApproval);
#endregion
for (int j = 1; j < 11; j++)
{
cell = row.CreateCell(j);
if (j == 1)
cell.SetCellValue(NTApproved.Trim() == "0 min" ? "-" : NTApproved.Trim());
else if (j == 2)
cell.SetCellValue(NTForApproval.Trim() == "0 min" ? "-" : NTForApproval.Trim());
else if (j == 3)
cell.SetCellValue(OTApproved.Trim() == "0 min" ? "-" : OTApproved.Trim());
else if (j == 4)
cell.SetCellValue(OTForApproval.Trim() == "0 min" ? "-" : OTForApproval.Trim());
else if (j == 5)
cell.SetCellValue(ntApprovedCost);
else if (j == 6)
cell.SetCellValue(ntForApprovalCost);
else if (j == 7)
cell.SetCellValue(otApprovedCost);
else if (j == 8)
cell.SetCellValue(otForApprovalCost);
else if (j == 9)
cell.SetCellValue(StotalTime.Trim() == "0 min" ? "-" : StotalTime.Trim());
else if (j == 10)
cell.SetCellValue(ntForApprovalCost + ntApprovedCost + otApprovedCost + otForApprovalCost);
if (i < uniqueJobType.Count - 1)
{
if(j < 5 || j == 9)
cell.CellStyle = fontNormalBorderLeftRight;
else
cell.CellStyle = fontCurrencyBorderLeftRight;
}
else
{
if (j < 5 || j == 9)
cell.CellStyle = fontNormalBorderLeftRightBottom;
else
cell.CellStyle = fontCurrencyBorderLeftRightBottom;
}
}
}
#endregion
#region TOTAL, MATERIAL COST, OUTSIDE SERVICE
string projNTApproved = GenerateTimeConsumed(runningNTApproved);
string projNTForApproval = GenerateTimeConsumed(runningNTForApproval);
string projOTApproved = GenerateTimeConsumed(runningOTApproved);
string projOTForApproval = GenerateTimeConsumed(runningOTForApproval);
string projTotalTime = GenerateTimeConsumed(runningNTApproved + runningNTForApproval + runningOTApproved + runningOTForApproval);
string projNTTotal = GenerateTimeConsumed(runningNTApproved + runningNTForApproval);
string projOTTotal = GenerateTimeConsumed(runningOTApproved + runningOTForApproval);
#region DETAIL TOTAL
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int y = 0; y < 12; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue("TOTAL");
else if (y == 8)
detailCell.SetCellValue(projNTTotal.Trim() == "0 min" ? "-" : projNTTotal.Trim());
else if (y == 9)
detailCell.SetCellValue(projOTTotal.Trim() == "0 min" ? "-" : projOTTotal.Trim());
else if (y == 10)
detailCell.SetCellValue(runningNTApprovedCost + runningNTForApprovalCost);
else if (y == 11)
detailCell.SetCellValue(runningOTApprovedCost + runningOTForApprovalCost);
if (y < 10)
detailCell.CellStyle = fontBoldAllBorder;
else
detailCell.CellStyle = fontCurrencyBoldAllBorder;
}
#endregion
#region MATERIAL COST
MaterialCost materialCost = new MaterialCost();
List<MaterialCost> projMaterialCost = new List<MaterialCost>();
projMaterialCost = materialCost.GetMaterialCost(project.HWNo == null ? "" : project.HWNo.Trim() == "" ? "" : project.HWNo, project.SWNo == null ? "" : project.SWNo.Trim() == "" ? "" : project.SWNo, project.EvalNo == null ? "" : project.EvalNo.Trim() == "" ? "" : project.EvalNo);
double totalmaterialBookedCost = 0;
double totalmaterialActualCost = 0;
if (projMaterialCost.Count > 0)
{
detailRowCount++; //Blank Row
#region HEADER
detailRow = detailSheet.CreateRow(detailRowCount++);
detailRow.CreateCell(0).SetCellValue("Material Cost");
detailRow.GetCell(0).CellStyle = fontBoldNoBorder;
string[] materialHeader = { "Item", "Description", "Part Number", "Booked Quantity", "Booked Unit Cost", "Booked Extended Cost", "Actual Quantity", "Actual Unit Cost", "Actual Extended Cost","Cost Overrun (Underrun)" };
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int i = 0; i < 10; i++)
{
ICell detailCell = detailRow.CreateCell(i);
detailCell.CellStyle = fontBoldAllBorder;
detailCell.SetCellValue(materialHeader[i]);
}
#endregion
#region DETAIL
for (int i = 0; i < projMaterialCost.Count; i++)
{
detailRow = detailSheet.CreateRow(detailRowCount++);
double bookcost = projMaterialCost[i].BookedQuantity * projMaterialCost[i].BookedUnitCost;
double actualcost = projMaterialCost[i].ActualUnitCost * projMaterialCost[i].ActualQuantity;
for (int y = 0; y < 10; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue(i + 1);
else if (y == 1)
detailCell.SetCellValue(projMaterialCost[i].PartDescription);
else if (y == 2)
detailCell.SetCellValue(projMaterialCost[i].PartNo);
else if (y == 3)
detailCell.SetCellValue(projMaterialCost[i].BookedQuantity);
else if (y == 4)
detailCell.SetCellValue(projMaterialCost[i].BookedUnitCost);
else if (y == 5)
detailCell.SetCellValue(bookcost);
else if (y == 6)
detailCell.SetCellValue(projMaterialCost[i].ActualQuantity);
else if (y == 7)
detailCell.SetCellValue(projMaterialCost[i].ActualUnitCost);
else if (y == 8)
detailCell.SetCellValue(actualcost);
else if (y == 9)
detailCell.SetCellValue(actualcost - bookcost);
if (i < projMaterialCost.Count - 1)
{
if (y < 4 || y == 6)
detailCell.CellStyle = fontNormalBorderLeftRight;
else
detailCell.CellStyle = fontCurrencyBorderLeftRight;
}
else
{
if (y < 4 || y == 6)
detailCell.CellStyle = fontNormalBorderLeftRightBottom;
else
detailCell.CellStyle = fontCurrencyBorderLeftRightBottom;
}
}
totalmaterialBookedCost += bookcost;
totalmaterialActualCost += actualcost;
}
#endregion
#region TOTAL
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int y = 0; y < 10; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue("TOTAL");
else if (y == 5)
detailCell.SetCellValue(totalmaterialBookedCost);
else if (y == 8)
detailCell.SetCellValue(totalmaterialActualCost);
else if (y == 9)
detailCell.SetCellValue(totalmaterialActualCost - totalmaterialBookedCost);
if (y == 5 || y == 8 || y == 9)
detailCell.CellStyle = fontCurrencyBoldAllBorder;
else
detailCell.CellStyle = fontBoldAllBorder;
}
#endregion
}
#endregion
#region OUTSIDE SERVICE
OutsideService outsideServiceCost = new OutsideService();
List<OutsideService> projOutServCost = new List<OutsideService>();
projOutServCost = outsideServiceCost.GetOutsideServiceCost(project.HWNo == null ? "" : project.HWNo.Trim() == "" ? "" : project.HWNo, project.SWNo == null ? "" : project.SWNo.Trim() == "" ? "" : project.SWNo, project.EvalNo == null ? "" : project.EvalNo.Trim() == "" ? "" : project.EvalNo);
double totalOutServBookedCost = 0;
double totalOutServActualCost = 0;
if (projOutServCost.Count > 0)
{
detailRowCount++; //Blank Row
#region HEADER
detailRow = detailSheet.CreateRow(detailRowCount++);
detailRow.CreateCell(0).SetCellValue("Outside Service Cost");
detailRow.GetCell(0).CellStyle = fontBoldNoBorder;
string[] outsideServiceHeader = { "Item","Type", "Description", "Vendor", "Booked Quantity", "Booked Unit Cost", "Booked Extended Cost", "Actual Quantity", "Actual Unit Cost", "Actual Extended Cost", "Cost Overrun (Underrun)" };
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int i = 0; i < 11; i++)
{
ICell detailCell = detailRow.CreateCell(i);
detailCell.CellStyle = fontBoldAllBorder;
detailCell.SetCellValue(outsideServiceHeader[i]);
}
#endregion
#region DETAIL
for (int i = 0; i < projOutServCost.Count; i++)
{
detailRow = detailSheet.CreateRow(detailRowCount++);
double bookcost = projOutServCost[i].BookedQuantity * projOutServCost[i].BookedUnitCost;
double actualcost = projOutServCost[i].ActualUnitCost * projOutServCost[i].ActualQuantity;
for (int y = 0; y < 11; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue(i + 1);
else if (y == 1)
detailCell.SetCellValue(projOutServCost[i].PartType);
else if (y == 2)
detailCell.SetCellValue(projOutServCost[i].PartDescription);
else if (y == 3)
detailCell.SetCellValue(projOutServCost[i].Vendor);
else if (y == 4)
detailCell.SetCellValue(projOutServCost[i].BookedQuantity);
else if (y == 5)
detailCell.SetCellValue(projOutServCost[i].BookedUnitCost);
else if (y == 6)
detailCell.SetCellValue(bookcost);
else if (y == 7)
detailCell.SetCellValue(projOutServCost[i].ActualQuantity);
else if (y == 8)
detailCell.SetCellValue(projOutServCost[i].ActualUnitCost);
else if (y == 9)
detailCell.SetCellValue(actualcost);
else if (y == 10)
detailCell.SetCellValue(actualcost - bookcost);
if (i < projMaterialCost.Count - 1)
{
if (y < 5 || y == 7)
detailCell.CellStyle = fontNormalBorderLeftRight;
else
detailCell.CellStyle = fontCurrencyBorderLeftRight;
}
else
{
if (y < 5 || y == 7)
detailCell.CellStyle = fontNormalBorderLeftRightBottom;
else
detailCell.CellStyle = fontCurrencyBorderLeftRightBottom;
}
}
totalOutServBookedCost += bookcost;
totalOutServActualCost += actualcost;
}
#endregion
#region TOTAL
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int y = 0; y < 11; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue("TOTAL");
else if (y == 6)
detailCell.SetCellValue(totalOutServBookedCost);
else if (y == 9)
detailCell.SetCellValue(totalOutServActualCost);
else if (y == 10)
detailCell.SetCellValue(totalOutServActualCost - totalOutServBookedCost);
if (y == 6 || y == 9 || y == 10)
detailCell.CellStyle = fontCurrencyBoldAllBorder;
else
detailCell.CellStyle = fontBoldAllBorder;
}
#endregion
}
#endregion
row = sheetReport.CreateRow(++currentrow);
for (int j = 0; j < 11; j++)
{
cell = row.CreateCell(j);
if (j == 0)
cell.SetCellValue("Total Labor Cost");
else if (j == 1)
cell.SetCellValue(projNTApproved.Trim() == "0 min" ? "-" : projNTApproved.Trim());
else if (j == 2)
cell.SetCellValue(projNTForApproval.Trim() == "0 min" ? "-" : projNTForApproval.Trim());
else if (j == 3)
cell.SetCellValue(projOTApproved.Trim() == "0 min" ? "-" : projOTApproved.Trim());
else if (j == 4)
cell.SetCellValue(projOTForApproval.Trim() == "0 min" ? "-" : projOTForApproval.Trim());
else if (j == 5)
cell.SetCellValue(runningNTApprovedCost);
else if (j == 6)
cell.SetCellValue(runningNTForApprovalCost);
else if (j == 7)
cell.SetCellValue(runningOTApprovedCost);
else if (j == 8)
cell.SetCellValue(runningOTForApprovalCost);
else if (j == 9)
cell.SetCellValue(projTotalTime.Trim() == "0 min" ? "-" : projTotalTime.Trim());
else if (j == 10)
cell.SetCellValue(runningNTApprovedCost + runningNTForApprovalCost + runningOTApprovedCost + runningOTForApprovalCost);
if (j < 5 || j == 9)
cell.CellStyle = fontBoldAllBorder;
else
cell.CellStyle = fontCurrencyBoldAllBorder;
}
row = sheetReport.CreateRow(++currentrow);
row.CreateCell(0).SetCellValue("Material Cost");
row.GetCell(0).CellStyle = fontBoldAllBorder;
for (int j = 1; j < 11; j++)
{
cell = row.CreateCell(j);
if (j == 1)
cell.SetCellValue("Booked Cost:");
else if (j == 2)
cell.SetCellValue(totalmaterialBookedCost);
else if (j == 4)
cell.SetCellValue("Actual Cost:");
else if (j == 5)
cell.SetCellValue(totalmaterialActualCost);
else if (j == 7)
cell.SetCellValue("Cost Overrun (Underrun)");
else if (j == 8)
cell.SetCellValue(totalmaterialActualCost - totalmaterialBookedCost);
if (j == 1 || j == 4 || j == 7)
cell.CellStyle = fontBoldTopBottom;
else
cell.CellStyle = fontCurrencyBoldRigthBottom;
}
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 2, 3));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 5, 6));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 8, 10));
row = sheetReport.CreateRow(++currentrow);
row.CreateCell(0).SetCellValue("Outside Service Cost");
row.GetCell(0).CellStyle = fontBoldAllBorder;
for (int j = 1; j < 11; j++)
{
cell = row.CreateCell(j);
if (j == 1)
cell.SetCellValue("Booked Cost:");
else if (j == 2)
cell.SetCellValue(totalOutServBookedCost);
else if (j == 4)
cell.SetCellValue("Actual Cost:");
else if (j == 5)
cell.SetCellValue(totalOutServActualCost);
else if (j == 7)
cell.SetCellValue("Cost Overrun (Underrun)");
else if (j == 8)
cell.SetCellValue(totalOutServActualCost - totalOutServBookedCost);
if (j == 1 || j == 4 || j == 7)
cell.CellStyle = fontBoldTopBottom;
else
cell.CellStyle = fontCurrencyBoldRigthBottom;
}
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 2, 3));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 5, 6));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 8, 10));
#endregion
}
}
private static void AddDateTimeValidations(WorkbookFormatter wf, IWorkbook wb)
{
wf.CreateSheet("Dates and Times");
IDataFormat dataFormat = wb.CreateDataFormat();
short fmtDate = dataFormat.GetFormat("m/d/yyyy");
short fmtTime = dataFormat.GetFormat("h:mm");
ICellStyle cellStyle_date = wb.CreateCellStyle();
cellStyle_date.DataFormat = (/*setter*/fmtDate);
ICellStyle cellStyle_time = wb.CreateCellStyle();
cellStyle_time.DataFormat = (/*setter*/fmtTime);
wf.CreateDVTypeRow("Date ( cells are already formated as date - m/d/yyyy)");
wf.CreateHeaderRow();
ValidationAdder va = wf.CreateValidationAdder(cellStyle_date, ValidationType.DATE);
va.AddValidation(OperatorType.BETWEEN, "2004/01/02", "2004/01/06", ERRORSTYLE.STOP, "Between 1/2/2004 and 1/6/2004 ", "Error box type = STOP", true, true, true);
va.AddValidation(OperatorType.NOT_BETWEEN, "2004/01/01", "2004/01/06", ERRORSTYLE.INFO, "Not between 1/2/2004 and 1/6/2004 ", "Error box type = INFO", false, true, true);
va.AddValidation(OperatorType.EQUAL, "2004/03/02", null, ERRORSTYLE.WARNING, "Equal to 3/2/2004", "Error box type = WARNING", false, false, true);
va.AddValidation(OperatorType.NOT_EQUAL, "2004/03/02", null, ERRORSTYLE.WARNING, "Not equal to 3/2/2004", "-", false, false, false);
va.AddValidation(OperatorType.GREATER_THAN, "=DATEVALUE(\"4-Jul-2001\")", null, ERRORSTYLE.WARNING, "Greater than DATEVALUE('4-Jul-2001')", "-", true, false, false);
va.AddValidation(OperatorType.LESS_THAN, "2004/03/02", null, ERRORSTYLE.WARNING, "Less than 3/2/2004", "-", true, true, false);
va.AddValidation(OperatorType.GREATER_OR_EQUAL, "2004/03/02", null, ERRORSTYLE.STOP, "Greater than or equal to 3/2/2004", "Error box type = STOP", true, false, true);
va.AddValidation(OperatorType.LESS_OR_EQUAL, "2004/03/04", null, ERRORSTYLE.STOP, "Less than or equal to 3/4/2004", "-", false, true, false);
// "Time" validation type
wf.CreateDVTypeRow("Time ( cells are already formated as time - h:mm)");
wf.CreateHeaderRow();
va = wf.CreateValidationAdder(cellStyle_time, ValidationType.TIME);
va.AddValidation(OperatorType.BETWEEN, "12:00", "16:00", ERRORSTYLE.STOP, "Between 12:00 and 16:00 ", "Error box type = STOP", true, true, true);
va.AddValidation(OperatorType.NOT_BETWEEN, "12:00", "16:00", ERRORSTYLE.INFO, "Not between 12:00 and 16:00 ", "Error box type = INFO", false, true, true);
va.AddValidation(OperatorType.EQUAL, "13:35", null, ERRORSTYLE.WARNING, "Equal to 13:35", "Error box type = WARNING", false, false, true);
va.AddValidation(OperatorType.NOT_EQUAL, "13:35", null, ERRORSTYLE.WARNING, "Not equal to 13:35", "-", false, false, false);
va.AddValidation(OperatorType.GREATER_THAN, "12:00", null, ERRORSTYLE.WARNING, "Greater than 12:00", "-", true, false, false);
va.AddValidation(OperatorType.LESS_THAN, "=1/2", null, ERRORSTYLE.WARNING, "Less than (1/2) -> 12:00", "-", true, true, false);
va.AddValidation(OperatorType.GREATER_OR_EQUAL, "14:00", null, ERRORSTYLE.STOP, "Greater than or equal to 14:00", "Error box type = STOP", true, false, true);
va.AddValidation(OperatorType.LESS_OR_EQUAL, "14:00", null, ERRORSTYLE.STOP, "Less than or equal to 14:00", "-", false, true, false);
}
/**
* cell styles used for formatting calendar sheets
*/
private static Dictionary<String, ICellStyle> CreateStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
ICellStyle style=null;
IFont titleFont = wb.CreateFont();
titleFont.FontHeightInPoints = (short)14;
titleFont.FontName = "Trebuchet MS";
style = wb.CreateCellStyle();
style.SetFont(titleFont);
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
styles.Add("title", style);
IFont itemFont = wb.CreateFont();
itemFont.FontHeightInPoints = (short)9;
itemFont.FontName = "Trebuchet MS";
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.LEFT);
style.SetFont(itemFont);
styles.Add("item_left", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.RIGHT;
style.SetFont(itemFont);
styles.Add("item_right", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.RIGHT;
style.SetFont(itemFont);
style.BorderRight = BorderStyle.DOTTED;
style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderLeft = BorderStyle.DOTTED;
style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderTop = BorderStyle.DOTTED;
style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.DataFormat = (wb.CreateDataFormat().GetFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
styles.Add("input_$", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.RIGHT;
style.SetFont(itemFont);
style.BorderRight = BorderStyle.DOTTED;
style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderLeft = BorderStyle.DOTTED;
style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderTop = BorderStyle.DOTTED;
style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.DataFormat = (wb.CreateDataFormat().GetFormat("0.000%"));
styles.Add("input_%", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.RIGHT;
style.SetFont(itemFont);
style.BorderRight = BorderStyle.DOTTED;
style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderLeft = BorderStyle.DOTTED;
style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderTop = BorderStyle.DOTTED;
style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.DataFormat =wb.CreateDataFormat().GetFormat("0");
styles.Add("input_i", style);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.CENTER);
style.SetFont(itemFont);
style.DataFormat =wb.CreateDataFormat().GetFormat("m/d/yy");
styles.Add("input_d", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.RIGHT;
style.SetFont(itemFont);
style.BorderRight = BorderStyle.DOTTED;
style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderLeft = BorderStyle.DOTTED;
style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderTop = BorderStyle.DOTTED;
style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.DataFormat =wb.CreateDataFormat().GetFormat("$##,##0.00");
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.FillForegroundColor = IndexedColors.GREY_25_PERCENT.Index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
styles.Add("formula_$", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.RIGHT;
style.SetFont(itemFont);
style.BorderRight = BorderStyle.DOTTED;
style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderLeft = BorderStyle.DOTTED;
style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderTop = BorderStyle.DOTTED;
style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.DataFormat =wb.CreateDataFormat().GetFormat("0");
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.FillForegroundColor = IndexedColors.GREY_25_PERCENT.Index;
style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
styles.Add("formula_i", style);
return styles;
}
private static Dictionary<String, ICellStyle> createStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
IDataFormat df = wb.CreateDataFormat();
ICellStyle style;
IFont headerFont = wb.CreateFont();
headerFont.Boldweight = (short)(FontBoldWeight.Bold);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(headerFont);
styles.Add("header", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(headerFont);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("header_date", style);
IFont font1 = wb.CreateFont();
font1.Boldweight = (short)(FontBoldWeight.Bold);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
styles.Add("cell_b", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
styles.Add("cell_b_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_b_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_g", style);
IFont font2 = wb.CreateFont();
font2.Color = (IndexedColors.Blue.Index);
font2.Boldweight = (short)(FontBoldWeight.Bold);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font2);
styles.Add("cell_bb", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_bg", style);
IFont font3 = wb.CreateFont();
font3.FontHeightInPoints = ((short)14);
font3.Color = (IndexedColors.DarkBlue.Index);
font3.Boldweight = (short)(FontBoldWeight.Bold);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font3);
style.WrapText = (true);
styles.Add("cell_h", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.WrapText = (true);
styles.Add("cell_normal", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.WrapText = (true);
styles.Add("cell_normal_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.WrapText = (true);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_normal_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.Indention = ((short)1);
style.WrapText = (true);
styles.Add("cell_indented", style);
style = CreateBorderedStyle(wb);
style.FillForegroundColor = (IndexedColors.Blue.Index);
style.FillPattern = FillPattern.SolidForeground;
styles.Add("cell_blue", style);
return styles;
}
/**
* create a library of cell styles
*/
private static Dictionary<String, ICellStyle> createStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
IDataFormat df = wb.CreateDataFormat();
ICellStyle style;
IFont headerFont = wb.CreateFont();
headerFont.Boldweight = (short)(FontBoldWeight.BOLD);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.LIGHT_CORNFLOWER_BLUE.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.SetFont(headerFont);
styles.Add("header", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.LIGHT_CORNFLOWER_BLUE.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.SetFont(headerFont);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("header_date", style);
IFont font1 = wb.CreateFont();
font1.Boldweight = (short)(FontBoldWeight.BOLD);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
styles.Add("cell_b", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
styles.Add("cell_b_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_b_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_g", style);
IFont font2 = wb.CreateFont();
font2.Color = (IndexedColors.BLUE.Index);
font2.Boldweight = (short)(FontBoldWeight.BOLD);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font2);
styles.Add("cell_bb", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_bg", style);
IFont font3 = wb.CreateFont();
font3.FontHeightInPoints = ((short)14);
font3.Color = (IndexedColors.DARK_BLUE.Index);
font3.Boldweight = (short)(FontBoldWeight.BOLD);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font3);
style.WrapText = (true);
styles.Add("cell_h", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = (true);
styles.Add("cell_normal", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = (true);
styles.Add("cell_normal_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = (true);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_normal_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.Indention = ((short)1);
style.WrapText = (true);
styles.Add("cell_indented", style);
style = CreateBorderedStyle(wb);
style.FillForegroundColor = (IndexedColors.BLUE.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
styles.Add("cell_blue", style);
return styles;
}
public static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font10 = wb.CreateFont();
font10.FontHeightInPoints = 10;
font10.FontName = "宋体";
font10.Boldweight = 700;
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 10;
font12.FontName = "宋体";
font12.Boldweight = 700;
IFont fontw = wb.CreateFont();
fontw.FontName = "宋体";
fontw.Boldweight = 700;
IFont font14 = wb.CreateFont();
font14.FontHeightInPoints = 14;
font14.FontName = "宋体";
font14.Boldweight = 700;
IFont font = wb.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "宋体";
IFont fontHT = wb.CreateFont();
fontHT.FontHeightInPoints = 10;
fontHT.FontName = "宋体";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "宋体";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;
//背景图形，我没有用到过。感觉很丑
//cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
//cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.投缴头:
cellStyle.FillForegroundColor = HSSFColor.PaleBlue.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font12);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.月报头:
cellStyle.FillForegroundColor = HSSFColor.Rose.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font10);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.月报底:
cellStyle.FillForegroundColor = HSSFColor.Rose.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font10);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case stylexls.月报内容:
cellStyle.FillForegroundColor = HSSFColor.White.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font10);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case stylexls.补缴头:
cellStyle.FillForegroundColor = HSSFColor.Rose.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font12);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.url:
fontcolorblue.Underline = FontUnderlineType.Single;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
case stylexls.表头:
cellStyle.SetFont(font14);
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.加粗:
cellStyle.SetFont(fontw);
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case stylexls.迟到早退标题:
cellStyle.SetFont(fontHT);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.迟到早退内容:
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case stylexls.不含底线:
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
break;
case stylexls.迟到早退标题加左线:
cellStyle.SetFont(fontHT);
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.迟到早退标题加左线左对齐:
cellStyle.SetFont(fontHT);
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
}
return cellStyle;
}
