//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 = mSheet.GetRow(rowNum);
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);
}
}
//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 = mSheet.GetRow(rowNum);
ICell cell = HSSFCellUtil.GetCell(row, startCol);
return(new object[] { NPOIUtils.GetCellValue(cell) });
}
else
{
var values = new List <object>();
CellRangeAddress range = new CellRangeAddress(rowNum, rowNum, startCol, startCol + numberOfCols - 1);
for (var i = startCol; i < numberOfCols; ++i)
{
var cell = mSheet.GetRow(rowNum).GetCell(i, MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (range.IsInRange(rowNum, i))
{
values.Add((cell != null) ? cell.ToString() : null);
}
}
return(values.ToArray());
}
}
/// <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;
}
}
}
static void Main(string[] args)
{
InitializeWorkbook();
ISheet sheet1=hssfworkbook.CreateSheet("Sheet1");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = hssfworkbook.CreateFont();
font1.Color = HSSFColor.Red.Index;
font1.IsItalic = true;
font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = hssfworkbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = hssfworkbook.CreateFont();
font2.Color = HSSFColor.OliveGreen.Index;
font2.IsStrikeout=true;
font2.FontHeightInPoints = 15;
font2.FontName = "宋体";
//bind font with style 2
ICellStyle style2 = hssfworkbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = HSSFCellUtil.CreateCell(sheet1.CreateRow(1), 1, "Hello World!");
cell1.CellStyle = style1;
ICell cell2 = HSSFCellUtil.CreateCell(sheet1.CreateRow(3), 1, "早上好！");
cell2.CellStyle = style2;
//cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
HSSFRichTextString richtext = new HSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = hssfworkbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3=hssfworkbook.CreateFont();
font3.TypeOffset = FontSuperScript.Super;
font3.IsItalic = true;
font3.Color = HSSFColor.Blue.Index;
font3.FontHeightInPoints=8;
richtext.ApplyFont(16, 18,font3);
cell3.SetCellValue(richtext);
WriteToFile();
}
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 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();
}
}
}
/// <summary>
/// 设置合并单元格的边框
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="region"></param>
/// <param name="stylexls"></param>
public static void RegionMethod(IWorkbook workbook, ISheet sheet, CellRangeAddress region, NOPIHelper.Stylexls stylexls)
{
for (int k = region.FirstRow; k <= region.LastRow; k++)
{
IRow row = sheet.GetRow(k);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = NOPIHelper.Getcellstyle(workbook, stylexls);
}
}
}
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;
}
}
}
}
public static void SetMergedCellStyle(XSSFWorkbook xssfworkbook, ISheet sheet, CellRangeAddress region, int fontSize)
{
ICellStyle style = xssfworkbook.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;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
//设置表头字体
IFont font1 = xssfworkbook.CreateFont();
font1.Boldweight = 700;
font1.FontHeight = fontSize;
font1.FontName = "微软雅黑";
style.SetFont(font1);//将设置好的字体样式设置给单元格样式对象。
//cell.CellStyle = style;
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow row = sheet.GetRow(i);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
}
}
public static bool Generate(Entities.ReportInfo info)
{
bool res = false;
try
{
string title = DomainModel.Application.
ResourceManager.GetText("rep_monthly_bill_title");
int colnum = 0;
int rownum = -1;
Sheet sheet;
Row row;
Cell head;
TypeDic typeDic = new TypeDic();
ServiceDic srvDic = new ServiceDic();
HeaderRow headRow = new HeaderRow();
// Create header
int index = 0;
// Create first col: day#
HeaderCell hc = new HeaderCell(index);
hc.Text = DomainModel.
Application.ResourceManager.GetText("rep_monthly_bill_col_day");
headRow.Add(hc);
index++;
// create service type cols
srvDic.Clear();
foreach (Entities.Service srv in DomainModel.Services.GetAll())
{
hc = new HeaderCell(index);
hc.Text = string.Format(" {0} {1} ({2}) ",
srv.ServiceType.Name,
srv.Name,
srv.PricePerPerson.FormattedValue);
headRow.Add(hc);
srvDic.Add(srv, new ServiceCell(index));
index++;
}
// Create payment type columns
foreach (Entities.GeneralType type in DomainModel.PaymentTypes.GetAll())
{
hc = new HeaderCell(index);
hc.Text = " " + type.Name + " ";
headRow.Add(hc);
typeDic.Add(type, new ServiceCell(index));
index++;
}
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
// Create theme
Font headFont = hssfworkbook.CreateFont();
headFont.Color = HSSFColor.WHITE.index;
headFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
CellStyle headstyle = hssfworkbook.CreateCellStyle();
headstyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
headstyle.SetFont(headFont);
//// Create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Ms. Anke Maass";
hssfworkbook.DocumentSummaryInformation = dsi;
//// Create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "Tour monthly bill";
hssfworkbook.SummaryInformation = si;
// For each month in report inf
for (DateTime date = info.StartTime; date.Month <= info.EndTime.Month; date = date.AddMonths(1))
{
colnum = 0;
string month = date.ToString("MMMM");
string sheetTitle = string.Format("{0} {1:00} {2:0000}",
title, month, DateTime.Now.Year);
// Create sheet
sheet = hssfworkbook.CreateSheet(month);
//sheet.DefaultColumnWidth = 25;
rownum = -1;
row = sheet.CreateRow(++rownum); // Title
row = sheet.CreateRow(++rownum); // Space
row = sheet.CreateRow(++rownum); // Col. headers
// Set columns titles
foreach (HeaderCell headCell in headRow)
{
row.CreateCell(colnum).SetCellValue(headCell.Text);
row.GetCell(colnum).CellStyle = headstyle;
sheet.AutoSizeColumn(colnum);
colnum++;
}
// Set sheet title (After columns for autowidth calculations)
row = sheet.GetRow(0);
head = HSSFCellUtil.CreateCell(row, 0, sheetTitle);
head.CellStyle = headstyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6));
row = sheet.GetRow(1);
head = HSSFCellUtil.CreateCell(row, 0, "");
head.CellStyle = headstyle;
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 6));
// Create count rows
for (DateTime dt = date; dt.Month <= date.Month; dt = dt.AddDays(1))
{
row = sheet.CreateRow(++rownum);
row.CreateCell(0).SetCellValue(dt.Day);
row.GetCell(0).CellStyle = headstyle;
Entities.TourCollection tours = new Entities.TourCollection();
DomainModel.Tours.LoadByDate(dt, tours);
// Reset counters
foreach (Entities.Service key in srvDic.Keys)
{
srvDic[key].Count = 0;
}
foreach (Entities.GeneralType key in typeDic.Keys)
{
typeDic[key].Value = 0;
}
// calc columns
foreach (Entities.Tour tour in tours)
{
foreach (Entities.TourGroup group in tour.Groups)
{
foreach (Entities.Abstract.ITourService srv in group.Services)
{
srvDic[srv.Detail].Count += srv.CostDetails.ServiceCount;
foreach (Entities.TourPayment payment in srv.Payments)
{
typeDic[payment.Type].Value += payment.Amount.Value;
}
}
}
}
// Set row cells
foreach (Entities.Service key in srvDic.Keys)
{
if (srvDic[key].Count != 0)
{
row.CreateCell(srvDic[key].Index).SetCellValue(srvDic[key].Count);
}
}
foreach (Entities.GeneralType key in typeDic.Keys)
{
if (typeDic[key].Value > 0.0M)
{
row.CreateCell(typeDic[key].Index).SetCellValue(
(double)typeDic[key].Value);
}
}
}
}
// Create footer
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(info.Path, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
res = true;
}
catch (Exception ex)
{
}
return(res);
}
/// <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);
}
}
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
}
}
private string BuildMergeSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtData)
{
string errorMessage = string.Empty;
// 设置开始行、列
int beginRow = 0 + helpEntity.SkipRowNum;
int beginCol = 0 + helpEntity.SkipColNum;
ISheet sheet = workBook.CreateSheet();
#region 标题处理
IRow rowTitle1 = sheet.CreateRow(beginRow);
rowTitle1.Height = helpEntity.THeight;
int colIndex = beginCol;
// 先赋值合并行标题
foreach (var mergeName in sheetEntity.MergeNames)
{
var temp = helpEntity.ExportColumns.Where(t => t.MergeName == mergeName);
var len = temp.Count();
// 获取合并标题开始列位置
int index = GetIndex(helpEntity.ExportColumns, mergeName);
colIndex = index;
ICell cellMerge = rowTitle1.CreateCell(colIndex);
cellMerge.SetCellValue(mergeName);
//cellMerge.CellStyle = cellStyleHead;
// 设置合并
sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, colIndex, colIndex + len - 1));
//for (int tempIndex = colIndex; tempIndex < colIndex + len; tempIndex++)
// HSSFCellUtil.GetCell(rowTitle1, tempIndex).CellStyle = cellStyleHead;
}
beginRow++;
IRow rowTitle2 = sheet.CreateRow(beginRow);
rowTitle2.Height = helpEntity.THeight;
colIndex = beginCol;
foreach (var item in helpEntity.ExportColumns)
{
ICell cellTitle = rowTitle2.CreateCell(colIndex);
cellTitle.SetCellValue(item.ExcelName);
cellTitle.CellStyle = item.CellStyle;
// 如果此标题在上述没有合并，则向上合并一列，保持美观
if (string.IsNullOrEmpty(item.MergeName))
{
sheet.AddMergedRegion(new CellRangeAddress(beginRow - 1, beginRow, colIndex, colIndex));
ICell cellTemp = HSSFCellUtil.GetCell(rowTitle1, colIndex);
//cellTemp.CellStyle = cellStyleHead;
cellTemp.SetCellValue(item.ExcelName);
}
sheet.SetColumnWidth(colIndex, item.Width);
colIndex++;
}
// 冻结标题行
if (helpEntity.FreezeTitleRow)
{
sheet.CreateFreezePane(beginCol + helpEntity.MergeColNum, 2, beginCol + helpEntity.MergeColNum, 2);
}
#endregion
beginRow++;
// 循环赋值列表内容
foreach (DataRow dr in dtData.Rows)
{
IRow rowContent = sheet.CreateRow(beginRow);
rowContent.Height = helpEntity.CHeight;
colIndex = beginCol;
ICell cell = null;
// 赋值内容
foreach (var item in helpEntity.ExportColumns)
{
if (item.Hidden)
{
continue;
}
cell = rowContent.CreateCell(colIndex);
object curValue = dr[item.ColumnName];
cell.SetCellValue(curValue, item, item.CellStyle);
colIndex++;
}
rowContent.Height = helpEntity.CHeight;
beginRow++;
}
// 筛选
if (helpEntity.AutoFilter)
{
CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum + 1, 0 + helpEntity.SkipRowNum + 1, beginCol, colIndex);
sheet.SetAutoFilter(c);
}
sheet.DisplayGridlines = helpEntity.ShowGridLine;
ProcessSheet(sheet, helpEntity);
return(errorMessage);
}
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);
}
