//static string _ColLetter( int col /* 0 origin */) {
// // col = [0...25]
// if( col >= 0 && col <= 25 )
// return ((char)('A' + col)).ToString();
// return "";
//}
//static string ColLetter( int col /* 1 Origin */) {
// if( col < 1 || col > 256 )
// throw new ExcelBadUsageException( "Column out of range; must be between 1 and 256" ); // Excel limits
// col--; // make 0 origin
// // good up to col ZZ
// int col2 = (col / 26) - 1;
// int col1 = (col % 26);
// return _ColLetter( col2 ) + _ColLetter( col1 );
//}
#endregion
#region " RowValues "
private object[] RowValues(int rowNum, int startCol, int numberOfCols)
{
if (mSheet == null)
{
return(null);
}
if (numberOfCols == 1)
{
IRow row = HSSFCellUtil.GetRow(rowNum, (HSSFSheet)mSheet);
ICell cell = HSSFCellUtil.GetCell(row, startCol);
return(new object[] { NPOIUtils.GetCellValue(cell) });
}
else
{
CellRangeAddress range = new CellRangeAddress(rowNum, rowNum, startCol, startCol + numberOfCols - 1);
CellWalk cw = new CellWalk(mSheet, range);
cw.SetTraverseEmptyCells(true);
CellExtractor ce = new CellExtractor();
cw.Traverse(ce);
return(ce.CellValues);
}
}
/// <summary>
/// 生成Excel的表头
/// </summary>
/// <param name="sheet"></param>
/// <param name="cols"></param>
/// <param name="rowIndex"></param>
/// <param name="colIndex"></param>
/// <param name="style"></param>
/// <returns></returns>
private int MakeExcelHeader(HSSFSheet sheet, IEnumerable<IGridColumn<TModel>> cols, int rowIndex, int colIndex, ICellStyle style)
{
var row = sheet.CreateRow(rowIndex) as HSSFRow;
int maxLevel = cols.Select(x => x.MaxLevel).Max();
//循环所有列
foreach (var col in cols)
{
//添加新单元格
var cell = row.CreateCell(colIndex);
cell.CellStyle = style;
cell.SetCellValue(col.Title);
var bcount = col.BottomChildren.Count();
var rowspan = 0;
if (rowIndex == 0)
{
rowspan = maxLevel - col.MaxLevel;
}
var cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex + rowspan, colIndex, colIndex + bcount - 1);
sheet.AddMergedRegion(cellRangeAddress);
for (int i = cellRangeAddress.FirstRow; i <= cellRangeAddress.LastRow; i++)
{
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
var c = HSSFCellUtil.GetCell(HSSFCellUtil.GetRow(i, sheet), j);
c.CellStyle = style;
}
}
if (col.Children != null && col.Children.Count() > 0)
{
MakeExcelHeader(sheet, col.Children, rowIndex + rowspan + 1, colIndex, style);
}
colIndex += bcount;
}
return maxLevel;
}
public static void SetSumRow(string sheetName, string strSumText, int row, int col, int endrow, int endcol, int rowHeight, string value, bool isCreate)
{
ISheet sheet = workbook.GetSheet(sheetName);
IRow sRow;
if (isCreate)
{
sRow = sheet.CreateRow(row);
}
else
{
sRow = sheet.GetRow(row);
}
sRow.HeightInPoints = rowHeight;
sRow.CreateCell(col).SetCellValue(strSumText);
ICellStyle sumStyle = workbook.CreateCellStyle();
sumStyle.Alignment = HorizontalAlignment.Right;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = ((short)10);
font.Underline = FontUnderlineType.Single;
font.Boldweight = 700;
font.FontName = "宋体";
sumStyle.SetFont(font);
sumStyle.FillBackgroundColor = HSSFColor.Blue.Index;
sumStyle.FillPattern = FillPattern.SolidForeground;
sumStyle.FillForegroundColor = HSSFColor.Yellow.Index;
sumStyle.BorderBottom = BorderStyle.Thin;
sumStyle.BorderTop = BorderStyle.Thin;
sumStyle.BorderLeft = BorderStyle.Thin;
sumStyle.BorderRight = BorderStyle.Thin;
sRow.GetCell(col).CellStyle = sumStyle;
if (!ValidateHelper.IsNullOrEmpty(value))
{
ICell valCell = sRow.CreateCell(endcol + 1);
//应用单元格样式
valCell.CellStyle = sumStyle;
//设置单元格标题
valCell.SetCellValue(value);
}
//合并单元格
CellRangeAddress region = new CellRangeAddress(row, endrow, col, endcol);
sheet.AddMergedRegion(region);
//给每个合并的单元格赋值样式
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow row_reg = HSSFCellUtil.GetRow(i, (HSSFSheet)sheet);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row_reg, (short)j);
singleCell.CellStyle = sumStyle;
}
}
}
public void SetBorder(int firstRow, int lastRow, int firstCol, int lastCol)
{
for (int rowIndex = firstRow; rowIndex < lastRow; rowIndex++)
{
var row = HSSFCellUtil.GetRow(rowIndex, currentSheet);
for (int cellIndex = firstCol; cellIndex < lastCol; cellIndex++)
{
var cell = HSSFCellUtil.GetCell(row, cellIndex);
cell.CellStyle = GetThinBDRStyle();
}
}
}
public static void SetBorderStyle(CellRangeAddress rangeAddress, ICellStyle style, HSSFSheet sheet)
{
for (int i = rangeAddress.FirstRow; i <= rangeAddress.LastRow; i++)
{
IRow row1 = HSSFCellUtil.GetRow(i, sheet);
for (int j = rangeAddress.FirstColumn; j <= rangeAddress.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row1, (short)j);
singleCell.CellStyle = style;
}
}
}
public void CreateFormat()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
HSSFRow dataRow = sheet.CreateRow(1) as HSSFRow;
dataRow = sheet.CreateRow(1) as HSSFRow;
CellRangeAddress region = new CellRangeAddress(1, 1, 1, 2);
sheet.AddMergedRegion(region);
ICell cell = dataRow.CreateCell(1);
cell.SetCellValue("test");
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BottomBorderColor = HSSFColor.Black.Index;
style.LeftBorderColor = HSSFColor.Black.Index;
style.RightBorderColor = HSSFColor.Black.Index;
style.TopBorderColor = HSSFColor.Black.Index;
//cell.CellStyle = style;
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow row = HSSFCellUtil.GetRow(i, sheet);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
using (FileStream fs = new FileStream("C:\\TestConsole.xls", FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
public void SetFont(int firstRow, int lastRow, int firstCol, int lastCol)
{
for (int rowIndex = firstRow; rowIndex < lastRow; rowIndex++)
{
var row = HSSFCellUtil.GetRow(rowIndex, currentSheet);
for (int cellIndex = firstCol; cellIndex < lastCol; cellIndex++)
{
var cell = HSSFCellUtil.GetCell(row, cellIndex);
ICellStyle style = workbook.CreateCellStyle(); //务必创建新的CellStyle,否则所有单元格同样式
IFont font = workbook.CreateFont();
font.Boldweight = short.MaxValue;
style.SetFont(font);
cell.CellStyle = style;
}
}
}
/// <summary>
/// 设置表头信息及样式
/// </summary>
/// <param name="sheetName">表明</param>
/// <param name="strHeaderText">表头内容</param>
/// <param name="row">起始行号</param>
/// <param name="col">起始列号</param>
/// <param name="endrow">终止行号</param>
/// <param name="endcol">终止列号</param>
public static void SetHeaderRow(string sheetName, string strHeaderText, int row, int col, int endrow, int endcol)
{
ISheet sheet = workbook.GetSheet(sheetName);
IRow headerRow = sheet.CreateRow(row);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(col).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = ((short)20);
font.Boldweight = 700;
font.FontName = "宋体";
headStyle.SetFont(font);
headStyle.FillBackgroundColor = HSSFColor.Blue.Index;
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = HSSFColor.Yellow.Index;
headStyle.BorderBottom = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headerRow.GetCell(col).CellStyle = headStyle;
//单元格合并 - 起始行号，终止行号， 起始列号，终止列号
//起始行号 不可能大于 终止行号
//起始列号 不可能大于 终止列号
CellRangeAddress region = new CellRangeAddress(row, endrow, col, endcol);
sheet.AddMergedRegion(region);
//设置宽度
//sheet.SetColumnWidth(row, strHeaderText.Length * 256 * 2);
//给每个合并的单元格赋值样式
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow row_reg = HSSFCellUtil.GetRow(i, (HSSFSheet)sheet);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row_reg, (short)j);
singleCell.CellStyle = headStyle;
sheet.SetColumnWidth(i, ((strHeaderText.Length * 256) / endcol + 1) + 5 * 256);
}
}
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
/// <param name="cellStyle">单元格样式</param>
public static void MergedCell(ISheet sheet, int rowstart, int rowend, int colstart, int colend,
ICellStyle cellStyle = null)
{
var cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
if (cellStyle == null)
{
return;
}
for (var i = cellRangeAddress.FirstRow; i <= cellRangeAddress.LastRow; i++)
{
var row = HSSFCellUtil.GetRow(i, (HSSFSheet)sheet);
for (var j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
var cell = HSSFCellUtil.GetCell(row, (short)j);
if (cell != null)
{
cell.CellStyle = cellStyle;
}
}
}
}
private void DrawTableHeader(DateTime sTime, DateTime eTime, IWorkbook workbook, ISheet sheet, int reportType,
int startColumnIndex, int rowIndex, dynamic valuePair, string precision)
{
int startIndex = startColumnIndex;
IRow row = sheet.GetRow(rowIndex - 1);
IRow dateRow = sheet.GetRow(rowIndex - 2);
ICellStyle cellstyle = GetCellStyle(workbook, precision);
switch (reportType)
{
#region 年（每月）
case 1:
for (int i = 1; i <= 12; i++, startIndex++)
{
ICell dateCell = dateRow.CreateCell(startIndex);
dateCell.SetCellValue(i + "月");
dateCell.CellStyle = cellstyle;
dateCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
dateCell.CellStyle.FillPattern = FillPattern.SolidForeground;
ICell eCell = row.CreateCell(startIndex);
eCell.SetCellValue("能耗");
eCell.CellStyle = cellstyle;
eCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
eCell.CellStyle.FillPattern = FillPattern.SolidForeground;
}
break;
#endregion
#region 月（每日）
case 2:
var year = sTime.Year;
var month = sTime.Month;
var days = DateTime.DaysInMonth(year, month);
for (int i = 1; i <= days; i++, startIndex++)
{
ICell dateCell = dateRow.CreateCell(startIndex);
dateCell.SetCellValue(i + "号");
dateCell.CellStyle = cellstyle;
dateCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
dateCell.CellStyle.FillPattern = FillPattern.SolidForeground;
ICell eCell = row.CreateCell(startIndex);
eCell.SetCellValue("能耗");
eCell.CellStyle = cellstyle;
eCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
eCell.CellStyle.FillPattern = FillPattern.SolidForeground;
}
break;
#endregion
#region 比
case 3:
for (int i = 1; i <= 12; i++, startIndex++)
{
ICell dateCell = dateRow.CreateCell(startIndex);
dateCell.SetCellValue(i + "月");
CellRangeAddress dateCellRegion = new CellRangeAddress(rowIndex - 2, rowIndex - 2, startIndex, startIndex + 2);
sheet.AddMergedRegion(dateCellRegion);
dateCell.CellStyle = cellstyle;
dateCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
dateCell.CellStyle.FillPattern = FillPattern.SolidForeground;
ICell eCell = row.CreateCell(startIndex);
eCell.SetCellValue("今年");
eCell.CellStyle = cellstyle;
eCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
eCell.CellStyle.FillPattern = FillPattern.SolidForeground;
startIndex++;
ICell yeCell = row.CreateCell(startIndex);
yeCell.SetCellValue("去年");
yeCell.CellStyle = cellstyle;
yeCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
yeCell.CellStyle.FillPattern = FillPattern.SolidForeground;
startIndex++;
ICell qeCell = row.CreateCell(startIndex);
qeCell.SetCellValue("同比(%)");
qeCell.CellStyle = cellstyle;
qeCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
qeCell.CellStyle.FillPattern = FillPattern.SolidForeground;
}
ICell totalDateCell = dateRow.CreateCell(startIndex);
totalDateCell.SetCellValue("合计");
CellRangeAddress totalDateCellRegion = new CellRangeAddress(rowIndex - 2, rowIndex - 2, startIndex, startIndex + 2);
sheet.AddMergedRegion(totalDateCellRegion);
totalDateCell.CellStyle = cellstyle;
totalDateCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
totalDateCell.CellStyle.FillPattern = FillPattern.SolidForeground;
ICell yearCell = row.CreateCell(startIndex);
yearCell.SetCellValue("今年");
yearCell.CellStyle = cellstyle;
yearCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
yearCell.CellStyle.FillPattern = FillPattern.SolidForeground;
startIndex++;
ICell lastYearCell = row.CreateCell(startIndex);
lastYearCell.SetCellValue("去年");
lastYearCell.CellStyle = cellstyle;
lastYearCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
lastYearCell.CellStyle.FillPattern = FillPattern.SolidForeground;
startIndex++;
ICell totaleCell = row.CreateCell(startIndex);
totaleCell.SetCellValue("同比(%)");
totaleCell.CellStyle = cellstyle;
totaleCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
totaleCell.CellStyle.FillPattern = FillPattern.SolidForeground;
break;
#endregion
#region 环比
case 4:
List <string> hbHeaderList = new List <string> {
"昨日", "今日", "环比(%)", "本月", "上月", "环比(%)"
};
IRow hbRow = sheet.GetRow(rowIndex - 2);
foreach (var header in hbHeaderList)
{
ICell cell = hbRow.CreateCell(startIndex);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex - 2, rowIndex - 1, startIndex, startIndex));
cell.SetCellValue(header);
cell.CellStyle = cellstyle;
cell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
cell.CellStyle.FillPattern = FillPattern.SolidForeground;
startIndex++;
}
break;
#endregion
#region 日抄表能耗
case 5:
List <string> rcHeaderList = new List <string> {
"上一表底", "上一表底时间", "当前表底时间", "当前表底", "能耗"
};
IRow rcbRow = sheet.GetRow(rowIndex - 2);
foreach (var header in rcHeaderList)
{
ICell cell = rcbRow.CreateCell(startIndex);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex - 2, rowIndex - 1, startIndex, startIndex));
cell.SetCellValue(header);
cell.CellStyle = cellstyle;
cell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
cell.CellStyle.FillPattern = FillPattern.SolidForeground;
startIndex++;
}
break;
#endregion
#region 峰谷平
case 6:
var fgpSection = (List <string>)valuePair.sectionList;
string titleFormat = GetDateFormat(reportType);
int mergeStartIndex = startIndex;
for (DateTime issTime = sTime; issTime <= eTime; issTime = GetTimeStep(reportType, issTime, ref titleFormat), startIndex++)
{
foreach (var fgp in fgpSection)
{
ICell fgpCell = row.CreateCell(startIndex);
startIndex++;
fgpCell.SetCellValue(fgp);
fgpCell.CellStyle = cellstyle;
fgpCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
fgpCell.CellStyle.FillPattern = FillPattern.SolidForeground;
}
ICell eCell = row.CreateCell(startIndex);
eCell.SetCellValue("总能耗");
eCell.CellStyle = cellstyle;
eCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
eCell.CellStyle.FillPattern = FillPattern.SolidForeground;
ICell dateCell = dateRow.CreateCell(mergeStartIndex);
var region = new CellRangeAddress(rowIndex - 2, rowIndex - 2, mergeStartIndex, startIndex);
sheet.AddMergedRegion(region);
dateCell.SetCellValue(issTime.ToString(titleFormat));
dateCell.CellStyle = cellstyle;
dateCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
dateCell.CellStyle.FillPattern = FillPattern.SolidForeground;
sheet.SetColumnWidth(startIndex, 60 * 256);
mergeStartIndex = startIndex + 1;
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow rowRegion = HSSFCellUtil.GetRow(i, (HSSFSheet)sheet);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(rowRegion, (short)j);
singleCell.CellStyle = cellstyle;
dateCell.CellStyle.FillForegroundColor = HSSFColor.Aqua.Index;
dateCell.CellStyle.FillPattern = FillPattern.SolidForeground;
}
}
}
break;
#endregion
#region 时间跨范围
case 7:
#endregion
#region 班组能耗
case 8:
#endregion
#region 时间点抄表
case 9:
#endregion
#region 灵活式
case 10:
break;
#endregion
}
}
public static ICellStyle Getcellstyle(IWorkbook wb, stylexls str, NPOI.SS.Util.CellRangeAddress region, HSSFSheet sh)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 12;
font12.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font12.FontName = "微软雅黑";
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Black.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "微软雅黑";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //BorderLeft
if (region != null)
{
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow row = HSSFCellUtil.GetRow(i, sh);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = cellStyle;
}
}
}
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.RightBorderColor = HSSFColor.OliveGreen.Black.Index;
//背景图形，我没有用到过。感觉很丑
//cellStyle.FillBackgroundColor = HSSFColor.OliveGreen.Black.Index;
//cellStyle.FillForegroundColor = HSSFColor.OliveGreen.Black.Index;
//cellStyle.FillPattern = FillPatternType.NO_FILL;
//cellStyle.FillForegroundColor = HSSFColor.White.Index;
//cellStyle.FillBackgroundColor = HSSFColor.Black.Index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
//cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.头:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palette = ((HSSFWorkbook)wb).GetCustomPalette();
//HSSFColor newColor = palette.AddColor((byte)153, (byte)204, (byte)255);
palette.SetColorAtIndex((short)10, (byte)227, (byte)232, (byte)227);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
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 = HSSFDataFormat.GetBuiltinFormat("¥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = FontUnderlineType.None;
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.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.SetFont(font);
break;
case stylexls.商品导入头:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette pale = ((HSSFWorkbook)wb).GetCustomPalette();
pale.SetColorAtIndex((short)30, (byte)11, (byte)87, (byte)235);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
cellStyle.SetFont(font12);
break;
case stylexls.导入说明:
HSSFPalette pale1 = ((HSSFWorkbook)wb).GetCustomPalette();
pale1.SetColorAtIndex((short)61, (byte)227, (byte)232, (byte)227);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightCornflowerBlue.Index;
IFont font2 = wb.CreateFont();
font2.FontName = "微软雅黑";
font2.FontHeightInPoints = 9;
cellStyle.SetFont(font2);
break;
case stylexls.导入红字:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palebgss = ((HSSFWorkbook)wb).GetCustomPalette();
palebgss.SetColorAtIndex((short)62, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
IFont fontRed = wb.CreateFont();
fontRed.FontName = "微软雅黑";
fontRed.Color = HSSFColor.OliveGreen.Red.Index;
cellStyle.SetFont(fontRed);
break;
case stylexls.导入错误提示:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette paleError = ((HSSFWorkbook)wb).GetCustomPalette();
paleError.SetColorAtIndex((short)10, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
cellStyle.SetFont(font);
break;
case stylexls.导入背景色:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palebgs = ((HSSFWorkbook)wb).GetCustomPalette();
palebgs.SetColorAtIndex((short)62, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
/// <summary>
/// Excel 导出
/// </summary>
/// <param name="dataTable">数据源</param>
/// <param name="fileName">保存路径</param>
/// <param name="sheetName">sheet 名称</param>
/// <param name="titleName">标题</param>
public static void ExportExcel(DataTable dataTable, string fileName, string sheetName, string titleName)
{
//创建 Excel 文件
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//创建 Excel Sheet
HSSFSheet hssfSheet = hssfWorkbook.CreateSheet(sheetName);
//调色板实例
Color LevelThreeColor = Color.FromArgb(197, 217, 241);
hssfSheet.DefaultColumnWidth = 20;
hssfSheet.DefaultRowHeight = 10;
//下拉列表
CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 12, 12);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "未启动", "整改中", "已完成" });
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
hssfSheet.AddValidationData(dataValidate);
#region 合并单元格
//合并单元格 , 开始行 , 结束行 ,开始列 ,结束列
List <CellRangeAddress> cellRange = new List <CellRangeAddress>();
List <CellRangeAddress> cellRange2 = new List <CellRangeAddress>();
cellRange.Add(new CellRangeAddress(0, 0, 0, 13));
cellRange.Add(new CellRangeAddress(1, 1, 0, 8));
cellRange.Add(new CellRangeAddress(1, 1, 9, 13));
cellRange2.Add(new CellRangeAddress(2, 3, 0, 0));
cellRange2.Add(new CellRangeAddress(2, 3, 1, 1));
cellRange2.Add(new CellRangeAddress(2, 3, 2, 2));
cellRange2.Add(new CellRangeAddress(2, 3, 3, 3));
cellRange2.Add(new CellRangeAddress(2, 3, 4, 4));
cellRange2.Add(new CellRangeAddress(2, 3, 5, 5));
cellRange2.Add(new CellRangeAddress(2, 3, 6, 6));
cellRange2.Add(new CellRangeAddress(2, 3, 7, 7));
cellRange2.Add(new CellRangeAddress(2, 2, 8, 10));
cellRange2.Add(new CellRangeAddress(2, 3, 11, 11));
cellRange2.Add(new CellRangeAddress(2, 3, 12, 12));
cellRange2.Add(new CellRangeAddress(2, 3, 13, 13));
cellRange.Add(new CellRangeAddress(2, 3, 0, 0));
cellRange.Add(new CellRangeAddress(2, 3, 1, 1));
cellRange.Add(new CellRangeAddress(2, 3, 2, 2));
cellRange.Add(new CellRangeAddress(2, 3, 3, 3));
cellRange.Add(new CellRangeAddress(2, 3, 4, 4));
cellRange.Add(new CellRangeAddress(2, 3, 5, 5));
cellRange.Add(new CellRangeAddress(2, 3, 6, 6));
cellRange.Add(new CellRangeAddress(2, 3, 7, 7));
cellRange.Add(new CellRangeAddress(2, 2, 8, 10));
cellRange.Add(new CellRangeAddress(2, 3, 11, 11));
cellRange.Add(new CellRangeAddress(2, 3, 12, 12));
cellRange.Add(new CellRangeAddress(2, 3, 13, 13));
foreach (CellRangeAddress cell in cellRange)
{
hssfSheet.AddMergedRegion(cell);
}
#endregion
#region 南京分行信息科技工作检查问题整改跟踪信息表
//创建标题列头 xx分行信息科技工作检查问题整改跟踪信息表
HSSFRow head_1_HSSFRow = hssfSheet.CreateRow(0);
head_1_HSSFRow.Height = 200 * 5;
head_1_HSSFRow.CreateCell(0).SetCellValue(titleName);
//创建样式 Style Header
HSSFCellStyle hssfCellStyle = hssfWorkbook.CreateCellStyle();
//创建字体 Font Header
HSSFFont hssfFontHead = (HSSFFont)hssfWorkbook.CreateFont();
//字体
hssfFontHead.FontName = "宋体";
hssfFontHead.FontHeightInPoints = 16;
hssfFontHead.Color = HSSFColor.BLACK.index;
hssfCellStyle.SetFont(hssfFontHead);
head_1_HSSFRow.GetCell(0).CellStyle = hssfCellStyle;
#endregion
#region 检查基本信息 、整改落实情况跟踪
//创建标题列头 检查基本信息、 整改落实情况跟踪
HSSFRow head_2_HSSFRow = hssfSheet.CreateRow(1);
head_2_HSSFRow.HeightInPoints = 20;
head_2_HSSFRow.CreateCell(0).SetCellValue("检查基本信息");
head_2_HSSFRow.CreateCell(9).SetCellValue("整改落实情况跟踪");
//样式
HSSFCellStyle hssf_2_CellStyle = hssfWorkbook.CreateCellStyle();
HSSFFont hssf_2_FontHead = (HSSFFont)hssfWorkbook.CreateFont();
//字体
hssfFontHead.FontName = "宋体";
hssfFontHead.FontHeightInPoints = 9;
hssfFontHead.Color = HSSFColor.BLACK.index;
hssf_2_CellStyle.SetFont(hssf_2_FontHead);
head_2_HSSFRow.GetCell(0).CellStyle = hssf_2_CellStyle;
head_2_HSSFRow.GetCell(9).CellStyle = hssf_2_CellStyle;
#endregion
#region 表列头 、 列名
//样式
HSSFCellStyle hssf_3_CellStyle = hssfWorkbook.CreateCellStyle();
HSSFFont hssf_3_FontHead = (HSSFFont)hssfWorkbook.CreateFont();
hssf_3_CellStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND;
hssf_3_CellStyle.FillBackgroundColor = GetXLColour(hssfWorkbook, LevelThreeColor);
hssf_3_CellStyle.BorderBottom = CellBorderType.THIN;
hssf_3_CellStyle.BorderLeft = CellBorderType.THIN;
hssf_3_CellStyle.BorderRight = CellBorderType.THIN;
hssf_3_CellStyle.BorderTop = CellBorderType.THIN;
//字体
hssf_3_FontHead.FontName = "宋体";
hssf_3_FontHead.FontHeightInPoints = 9;
hssf_3_FontHead.Color = HSSFColor.BLACK.index;
hssf_3_CellStyle.SetFont(hssf_3_FontHead);
HSSFRow content_1_HSSFRow = hssfSheet.CreateRow(2);
content_1_HSSFRow.CreateCell(0).SetCellValue("序号");
content_1_HSSFRow.CreateCell(1).SetCellValue("分行名称");
content_1_HSSFRow.CreateCell(2).SetCellValue("检查开始时间");
content_1_HSSFRow.CreateCell(3).SetCellValue("检查结束时间");
content_1_HSSFRow.CreateCell(4).SetCellValue("检查项分类");
content_1_HSSFRow.CreateCell(5).SetCellValue("问题性质");
content_1_HSSFRow.CreateCell(6).SetCellValue("问题描述");
content_1_HSSFRow.CreateCell(7).SetCellValue("检查意见");
content_1_HSSFRow.CreateCell(8).SetCellValue("整改措施计划");
content_1_HSSFRow.CreateCell(11).SetCellValue("推进情况");
content_1_HSSFRow.CreateCell(12).SetCellValue("整改状态");
content_1_HSSFRow.CreateCell(13).SetCellValue("最终完成时间");
HSSFRow content_2_HSSFRow = hssfSheet.CreateRow(3);
content_2_HSSFRow.CreateCell(8).SetCellValue("整改措施");
content_2_HSSFRow.CreateCell(9).SetCellValue("整改完成标志");
content_2_HSSFRow.CreateCell(10).SetCellValue("计划完成时间");
content_2_HSSFRow.GetCell(8).CellStyle = hssf_3_CellStyle;
content_2_HSSFRow.GetCell(9).CellStyle = hssf_3_CellStyle;
content_2_HSSFRow.GetCell(10).CellStyle = hssf_3_CellStyle;
#endregion
#region 边框、背景颜色设置
//创建样式 Style Bord
HSSFCellStyle hssfCellStyleBord = hssfWorkbook.CreateCellStyle();
//创建字体 Font Bord
hssfCellStyleBord.Alignment = CellHorizontalAlignment.CENTER;
hssfCellStyleBord.VerticalAlignment = CellVerticalAlignment.CENTER;
hssfCellStyleBord.BorderBottom = CellBorderType.THIN;
hssfCellStyleBord.BorderLeft = CellBorderType.THIN;
hssfCellStyleBord.BorderRight = CellBorderType.THIN;
hssfCellStyleBord.BorderTop = CellBorderType.THIN;
HSSFFont hssfFontBord = (HSSFFont)hssfWorkbook.CreateFont();
hssfFontBord.FontName = "宋体";
hssfFontBord.FontHeightInPoints = 12;
hssfFontBord.Color = HSSFColor.BLACK.index;
hssfCellStyleBord.SetFont(hssfFontBord);
//边框
foreach (var cell in cellRange)
{
for (int i = cell.FirstRow; i <= cell.LastRow; i++)
{
HSSFRow row = HSSFCellUtil.GetRow(i, hssfSheet);
for (int j = cell.FirstColumn; j <= cell.LastColumn; j++)
{
HSSFCell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = hssfCellStyleBord;
}
}
}
//创建样式 Style Bord
HSSFCellStyle hssfCellStyleBord_2 = hssfWorkbook.CreateCellStyle();
hssfCellStyleBord_2.Alignment = CellHorizontalAlignment.CENTER;
hssfCellStyleBord_2.VerticalAlignment = CellVerticalAlignment.CENTER;
hssfCellStyleBord_2.BorderBottom = CellBorderType.THIN;
hssfCellStyleBord_2.BorderLeft = CellBorderType.THIN;
hssfCellStyleBord_2.BorderRight = CellBorderType.THIN;
hssfCellStyleBord_2.BorderTop = CellBorderType.THIN;
hssfCellStyleBord_2.FillPattern = CellFillPattern.SOLID_FOREGROUND;
hssfCellStyleBord_2.FillBackgroundColor = GetXLColour(hssfWorkbook, LevelThreeColor);
hssfCellStyleBord_2.SetFont(hssfFontBord);
foreach (var cell in cellRange2)
{
for (int i = cell.FirstRow; i <= cell.LastRow; i++)
{
HSSFRow row = HSSFCellUtil.GetRow(i, hssfSheet);
for (int j = cell.FirstColumn; j <= cell.LastColumn; j++)
{
HSSFCell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = hssfCellStyleBord_2;
}
}
}
#endregion
#region 数据内容
try
{
#region 内容样式
HSSFCellStyle hssf_i_CellStyle = hssfWorkbook.CreateCellStyle();
HSSFFont hssf_i_FontHead = (HSSFFont)hssfWorkbook.CreateFont();
hssf_i_CellStyle.Alignment = CellHorizontalAlignment.CENTER;
hssf_i_CellStyle.VerticalAlignment = CellVerticalAlignment.CENTER;
hssf_i_CellStyle.BorderBottom = CellBorderType.THIN;
hssf_i_CellStyle.BorderLeft = CellBorderType.THIN;
hssf_i_CellStyle.BorderRight = CellBorderType.THIN;
hssf_i_CellStyle.BorderTop = CellBorderType.THIN;
//字体
hssf_i_FontHead.FontName = "宋体";
hssf_i_FontHead.FontHeightInPoints = 12;
hssf_i_FontHead.Color = HSSFColor.BLACK.index;
hssf_i_CellStyle.SetFont(hssf_i_FontHead);
hssf_i_CellStyle.IsLocked = false;
#endregion
if (dataTable.Rows.Count > 0)
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
HSSFRow dataHSSFRow = hssfSheet.CreateRow(i + 4);
dataHSSFRow.HeightInPoints = 20;
dataHSSFRow.CreateCell(0).SetCellValue(dataTable.Rows[i][0].ToString());
dataHSSFRow.CreateCell(1).SetCellValue(dataTable.Rows[i][1].ToString());
dataHSSFRow.CreateCell(2).SetCellValue(dataTable.Rows[i][2].ToString());
dataHSSFRow.CreateCell(3).SetCellValue(dataTable.Rows[i][3].ToString());
dataHSSFRow.CreateCell(4).SetCellValue(dataTable.Rows[i][4].ToString());
dataHSSFRow.CreateCell(5).SetCellValue(dataTable.Rows[i][5].ToString());
dataHSSFRow.CreateCell(6).SetCellValue(dataTable.Rows[i][6].ToString());
dataHSSFRow.CreateCell(7).SetCellValue(dataTable.Rows[i][7].ToString());
dataHSSFRow.CreateCell(8).SetCellValue(string.Empty);
dataHSSFRow.CreateCell(9).SetCellValue(string.Empty);
dataHSSFRow.CreateCell(10).SetCellValue(string.Empty);
dataHSSFRow.CreateCell(11).SetCellValue(string.Empty);
dataHSSFRow.CreateCell(12).SetCellValue(string.Empty);
dataHSSFRow.CreateCell(13).SetCellValue(string.Empty);
for (int j = 0; j < 14; j++)
{
dataHSSFRow.GetCell(j).CellStyle = hssf_i_CellStyle;
}
}
}
hssfSheet.ProtectSheet("21");
}
catch (Exception ex)
{
throw new Exception("导出 Excel 错误 , " + ex.Message);
}
#endregion
//保存文件
using (FileStream fileStream = new FileStream(fileName, FileMode.Create))
{
hssfWorkbook.Write(fileStream);
}
}