//条件样式
public static HSSFCellStyle getConditionStytle(HSSFWorkbook book)
{
HSSFCellStyle cellstyle = (HSSFCellStyle)book.CreateCellStyle();
//居中
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
//边框
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
//背景颜色
cellstyle.FillPattern = FillPattern.Squares;
cellstyle.FillPattern = FillPattern.SolidForeground;
cellstyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index;
//字体
HSSFFont font = (NPOI.HSSF.UserModel.HSSFFont)book.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 14;
//font.Boldweight = 12 * 240;
cellstyle.SetFont(font);
return(cellstyle);
}
private HSSFCellStyle ApplyCellStyle(HSSFWorkbook workbook,
ExcelCellFormatting cellFormatting)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
if (cellFormatting.Centred)
{
cellStyle.Alignment = HorizontalAlignment.Center;
}
cellStyle.FillForegroundColor = cellFormatting.BackColorIndex;
cellStyle.FillPattern = FillPattern.SolidForeground;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontName = cellFormatting.FontName;
font.FontHeightInPoints = cellFormatting.FontSize;
if (cellFormatting.Bold)
{
font.Boldweight = (short)FontBoldWeight.Bold;
}
font.Color = cellFormatting.ForeColorIndex;
cellStyle.SetFont(font);
return(cellStyle);
}
private void SetStyleForRow()
{
#region 标题行
cellstyleForOneRow = (HSSFCellStyle)WorkBook.CreateCellStyle();
HSSFFont font = (HSSFFont)WorkBook.CreateFont();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 15;
cellstyleForOneRow.SetFont(font);
#endregion
#region 校验失败行
cellstyleForVerifyFailed = (HSSFCellStyle)WorkBook.CreateCellStyle();
cellstyleForVerifyFailed.FillForegroundColor = HSSFColor.LIGHT_ORANGE.index;
cellstyleForVerifyFailed.FillPattern = FillPatternType.SOLID_FOREGROUND;
HSSFFont font1 = (HSSFFont)WorkBook.CreateFont();
font1.Color = HSSFColor.BLACK.index;
font1.FontHeightInPoints = 12;
cellstyleForVerifyFailed.SetFont(font1);
#endregion
#region 校验成功行
cellstyleForVerifySuccess = (HSSFCellStyle)WorkBook.CreateCellStyle();
cellstyleForVerifySuccess.FillForegroundColor = HSSFColor.SKY_BLUE.index;
cellstyleForVerifySuccess.FillPattern = FillPatternType.SOLID_FOREGROUND;
HSSFFont font2 = (HSSFFont)WorkBook.CreateFont();
font2.Color = HSSFColor.BLACK.index;
font2.FontHeightInPoints = 12;
cellstyleForVerifySuccess.SetFont(font2);
#endregion
}
private void SetHeaderOptions(SimpleExcelExport.Column column, NPOI.SS.UserModel.IRow row, int columnNumber, ExportToExcel exportToExcel)
{
HSSFCellStyle style = (HSSFCellStyle)document.CreateCellStyle();
var font = document.CreateFont();
if (column.HFontBold)
{
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
}
if (!string.IsNullOrEmpty(column.HFontColor))
{
System.Drawing.Color fontColor = exportToExcel.GetColor(column.ColumnName, column.HFontColor, typeof(string));
if (!fontColor.IsEmpty)
{
font.Color = GetXLColour(fontColor);
}
}
if (!string.IsNullOrEmpty(column.HBackColor))
{
System.Drawing.Color backgroundColor = exportToExcel.GetColor(column.ColumnName, column.HBackColor, typeof(string));
if (!backgroundColor.IsEmpty)
{
style.FillForegroundColor = GetXLColour(backgroundColor);
style.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
}
}
style.SetFont(font);
row.Cells[columnNumber].CellStyle = style;
}
public async Task <HSSFWorkbook> GetWorkbook(List <ExcelTestClass> list, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "耐心的雪球有限公司";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "耐心"; //填加xls文件作者信息
si.LastAuthor = "耐心"; //填加xls文件最后保存者信息
si.Comments = "耐心"; //填加xls文件作者信息
si.Subject = "导出文件记录"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
await ListToSheet(workbook, list, headStyle, sheetName);
return(workbook);
}
public PictureToExcel()
{
InitializeWorkbook();
HSSFSheet sheet1 = hssfworkbook.CreateSheet("PictureSheet");
TechStoreDocumentsDT = new DataTable();
using (SqlDataAdapter DA = new SqlDataAdapter(@"SELECT TOP (2000) TechStoreName, TechStoreDocumentID FROM TechStore
LEFT JOIN TechStoreDocuments ON TechStore.TechStoreID = TechStoreDocuments.TechID AND DocType = 0
WHERE TechStoreSubGroupID = 30 ORDER BY TechStoreName", ConnectionStrings.CatalogConnectionString))
{
using (DataTable DT = new DataTable())
{
DA.Fill(TechStoreDocumentsDT);
}
}
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
HSSFCell Cell1;
HSSFFont HeaderF1 = hssfworkbook.CreateFont();
HeaderF1.FontHeightInPoints = 11;
HeaderF1.Boldweight = 11 * 256;
HeaderF1.FontName = "Calibri";
HSSFCellStyle ReportCS1 = hssfworkbook.CreateCellStyle();
ReportCS1.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
ReportCS1.BottomBorderColor = HSSFColor.BLACK.index;
ReportCS1.SetFont(HeaderF1);
int RowIndex = 0;
for (int i = 0; i < TechStoreDocumentsDT.Rows.Count; i++)
{
string TechStoreName = TechStoreDocumentsDT.Rows[i]["TechStoreName"].ToString();
Cell1 = sheet1.CreateRow(RowIndex).CreateCell(0);
Cell1.SetCellValue(TechStoreName);
Cell1.CellStyle = ReportCS1;
if (TechStoreDocumentsDT.Rows[i]["TechStoreDocumentID"] != DBNull.Value)
{
int TechStoreDocumentID = Convert.ToInt32(TechStoreDocumentsDT.Rows[i]["TechStoreDocumentID"]);
anchor = new HSSFClientAnchor(0, 0, 0, 255, 2, RowIndex, 5, RowIndex + 7)
{
AnchorType = 2
};
HSSFPicture picture = patriarch.CreatePicture(anchor, GetTechStoreImage(TechStoreDocumentID, hssfworkbook));
//picture.Resize();
picture.LineStyle = HSSFPicture.LINESTYLE_DASHDOTGEL;
}
RowIndex = RowIndex + 8;
}
WriteToFile();
}
private static HSSFCellStyle generate(HSSFWorkbook wb_, int index, int r, int g, int b, bool bold)
{
short colorIndex = (short)index;
HSSFCellStyle style = (HSSFCellStyle)(wb_.CreateCellStyle());
HSSFPalette palette = ((HSSFWorkbook)wb_).GetCustomPalette();
palette.SetColorAtIndex(colorIndex, (byte)r, (byte)g, (byte)b);
style.FillForegroundColor = palette.GetColor(colorIndex).Indexed;
style.FillPattern = FillPattern.SolidForeground;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//是否换行
style.WrapText = true;
if (bold)
{
IFont font = wb_.CreateFont();
font.Boldweight = short.MaxValue;
style.SetFont(font);
}
return(style);
}
public void AddFooterCellToCurrentRow(string text, int index, NPOI.SS.UserModel.HorizontalAlignment xAlign = NPOI.SS.UserModel.HorizontalAlignment.LEFT)
{
//HSSFCellStyle style = FooterCellStyle(xAlign);
int idx = (index < 0) ? columnIndex:index;
ManualAdjustColumnWidth(text, idx);
//HSSFCell cellFtr = (HSSFCell)_HSSFCurrentRow.GetCell(idx);
HSSFFont fFooter = (HSSFFont)workbook.CreateFont();
fFooter.FontName = "Calibri";
fFooter.Boldweight = (short)FontBoldWeight.BOLD;
fFooter.Color = HSSFColor.BLACK.index;
HSSFCellStyle cFooterStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cFooterStyle.SetFont(fFooter);
cFooterStyle.BorderTop = CellBorderType.THIN;
cFooterStyle.TopBorderColor = HSSFColor.GREY_50_PERCENT.index;
HSSFCell cellFtr = (HSSFCell)_HSSFCurrentRow.CreateCell(idx, CellType.STRING);
cellFtr.CellStyle = cFooterStyle;
cellFtr.CellStyle.BorderLeft = CellBorderType.NONE;
cellFtr.CellStyle.BorderRight = CellBorderType.NONE;
cellFtr.SetCellValue(text);
ManualAdjustColumnWidth(text, idx);
cellFtr.CellStyle.Alignment = xAlign;
columnIndex++;
}
/// <summary>
/// 设置单元格样式(数据格式为数字型)(红色)
/// </summary>
/// <param name="workbook">excel表格</param>
/// <param name="horizontal">水平布局</param>
/// <param name="vertical">垂直布局</param>
/// <param name="boldWeight">字体加粗</param>
/// <param name="fontPoint">字体大小</param>
/// <param name="isBorder">是否需要边框</param>
/// <param name="fontName">字体名称：宋体、黑体、微软雅黑，默认微软雅黑</param>
/// <returns>返回单元格样式</returns>
public static HSSFCellStyle CreateStyle3(HSSFWorkbook workbook, HorizontalAlignment horizontal, VerticalAlignment vertical, short boldWeight, short fontPoint, bool isBorder, string fontName = "微软雅黑")
{
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyle.Alignment = horizontal;
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.VerticalAlignment = vertical;
if (isBorder)
{
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
}
//创建字体
HSSFFont cellStyleFont = (HSSFFont)workbook.CreateFont();
//字体加粗
cellStyleFont.Boldweight = boldWeight;
cellStyleFont.FontHeightInPoints = fontPoint;
cellStyleFont.FontName = fontName;
cellStyleFont.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
cellStyle.SetFont(cellStyleFont);
return(cellStyle);
}
/// <summary>
/// 加边框
/// </summary>
/// <param Name="rowindex">1开始</param>
/// <param Name="cellIndex">1开始</param>
public void AddBorder(ISheet sheet, HSSFWorkbook workbook)
{
ICellStyle styel = workbook.CreateCellStyle();
styel.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font1 = workbook.CreateFont();
font1.FontHeightInPoints = 11;
font1.Boldweight = 600;
font1.FontName = "宋体";
styel.SetFont(font1);
for (int rowindex = 1; rowindex < sheet.LastRowNum + 1; rowindex++)
{
for (int cellIndex = 0; cellIndex < dcs.Count; cellIndex++)
{
sheet.GetRow(rowindex).RowStyle = styel;
ICell cell = sheet.GetRow(rowindex).GetCell(cellIndex);
HSSFCellStyle Style = workbook.CreateCellStyle() as HSSFCellStyle;
Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
Style.VerticalAlignment = VerticalAlignment.Center;
Style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
Style.DataFormat = 0;
Style.SetFont(font1);
cell.CellStyle = Style;
}
}
}
public MemoryStream ExportExcel(List <客戶資料> exportData, HSSFWorkbook book)
{
DataTable dt = new DataTable();
dt.Columns.Add("客戶名稱", typeof(string));
dt.Columns.Add("統一編號", typeof(string));
dt.Columns.Add("電話", typeof(string));
dt.Columns.Add("傳真", typeof(string));
dt.Columns.Add("地址", typeof(string));
dt.Columns.Add("Email", typeof(string));
dt.Columns.Add("客戶分類", typeof(string));
for (int i = 0; i < exportData.Count; i++)
{
dt.Rows.Add(exportData[i].客戶名稱, exportData[i].統一編號,
exportData[i].電話, exportData[i].傳真,
exportData[i].地址, exportData[i].Email,
exportData[i].客戶分類);
}
HSSFSheet sheet = (HSSFSheet)book.CreateSheet();
HSSFCellStyle headCellStyle = (HSSFCellStyle)book.CreateCellStyle();
HSSFCellStyle dataCellStyle = (HSSFCellStyle)book.CreateCellStyle();
HSSFFont font = (HSSFFont)book.CreateFont();
font.FontHeightInPoints = 12;
font.FontName = "微軟正黑體";
font.Color = HSSFColor.Blue.Index; //字的顏色
headCellStyle.SetFont(font);
HSSFFont dataFont = (HSSFFont)book.CreateFont();
font.FontName = "微軟正黑體";
dataCellStyle.SetFont(dataFont);
var hRow = sheet.CreateRow(0);
//表頭
for (int h = 0; h < dt.Columns.Count; h++)
{
HSSFRow r = (HSSFRow)sheet.GetRow(0);
r.CreateCell(h).SetCellValue(dt.Columns[h].ColumnName.ToString());
r.GetCell(h).CellStyle = headCellStyle;
}
//表身
for (int j = 0; j < dt.Rows.Count; j++)
{
var dRow = sheet.CreateRow(j + 1);
HSSFRow dr = (HSSFRow)sheet.GetRow(j + 1);
for (int k = 0; k < dt.Columns.Count; k++)
{
dr.CreateCell(k).SetCellValue(dt.Rows[j][k].ToString());
}
}
MemoryStream output = new MemoryStream();
book.Write(output);
return(output);
}
/// <summary>
/// render datatable to workbook stream
/// </summary>
/// <param name="sourceTable">datatable</param>
/// <returns>workbook stream</returns>
public static Stream RenderDataTableToExcel(DataTable sourceTable)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
//設定 Header Style
HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headerStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
headerStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
headerStyle.BorderTop = headerStyle.BorderLeft;
headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HSSFFont headerFont = (HSSFFont)workbook.CreateFont();
headerFont.Color = HSSFColor.White.Index;
headerFont.Boldweight = 1;
headerStyle.SetFont(headerFont);
// handling header.
for (int i = 0; i < sourceTable.Columns.Count; i++)
{
DataColumn column = sourceTable.Columns[i];
NPOI.SS.UserModel.ICell headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = headerStyle;
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
/// <summary>
/// 将List转换成excel
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="list">数据集合</param>
/// <param name="tableHeaders">表头 k--属性 v--属性显示名称</param>
/// <param name="sheet">sheet名称</param>
/// <returns>文件流</returns>
public static byte[] ExportDataListToExcel <T>(List <T> list, Dictionary <string, string> tableHeaders, string sheetPrefix) where T : class, new()
{
HSSFWorkbook hssWorkBook = new HSSFWorkbook(); // 创建工作薄
int sheetNums = (int)Math.Ceiling(list.Count / 50000.00);
for (int i = 0; i < sheetNums; i++)
{
ISheet sheet = hssWorkBook.CreateSheet(sheetPrefix + (i + 1)); // 创建sheet表
// 创建表头
IRow rowHead = sheet.CreateRow(0);
int rowId = 0;
foreach (KeyValuePair <string, string> kv in tableHeaders)
{
ICell cell = rowHead.CreateCell(rowId);
cell.SetCellValue(kv.Value);
// 设置样式 加粗居中
HSSFCellStyle cellStyle = (HSSFCellStyle)hssWorkBook.CreateCellStyle();
HSSFFont font = (HSSFFont)hssWorkBook.CreateFont();
font.Boldweight = 600;
cellStyle.SetFont(font);
cellStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle = cellStyle;
rowId++;
}
rowId = 1;
// 创建数据行
PropertyInfo[] properties = typeof(T).GetProperties();
// 计算每个sheet中记录数
var pagedList = list.Skip(i * 50000).Take(50000).ToList();
foreach (T item in pagedList)
{
IRow rowBody = sheet.CreateRow(rowId);
int cellIndex = 0;
foreach (KeyValuePair <string, string> kv in tableHeaders)
{
foreach (PropertyInfo property in properties)
{
if (string.Equals(property.Name, kv.Key, StringComparison.CurrentCultureIgnoreCase))
{
ICell cell = rowBody.CreateCell(cellIndex);
FillCellValue(cell, property.GetValue(item, null));
break;
}
}
cellIndex++;
}
rowId++;
}
// 冻结首行
sheet.CreateFreezePane(0, 1);
}
MemoryStream file = new MemoryStream();
hssWorkBook.Write(file);
return(file.GetBuffer());
}
/// <summary>
/// 由DataSet导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="sheetName">工作表名称</param>
/// <returns>Excel工作表</returns>
private static MemoryStream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
string[] sheetNames = sheetName.Split(',');
for (int i = 0; i < sheetNames.Length; i++)
{
ISheet sheet = workbook.CreateSheet(sheetNames[i]);
#region 列头
IRow headerRow = sheet.CreateRow(0);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//取得列宽
int[] arrColWidth = new int[sourceDs.Tables[i].Columns.Count];
foreach (DataColumn item in sourceDs.Tables[i].Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
// 处理列头
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
#endregion
#region 填充值
int rowIndex = 1;
foreach (DataRow row in sourceDs.Tables[i].Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
#endregion
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
return(ms);
}
private HSSFCellStyle GetDefaultHeaderStyle()
{
fontHeader.FontName = "Calibri";
fontHeader.Color = HSSFColor.BLACK.index;
fontHeader.FontHeightInPoints = 10;
fontHeader.Boldweight = 800;
HSSFCellStyle xCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
xCellStyle.SetFont(fontHeader);
return(xCellStyle);
}
public void SaveFileInSpecifiedPath(DataTable dt, string[] columns
, string fullPath)
{
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("Details");
// Set header name this header use for set name in excel first row
string[] headers = new[] { "File Name", "Name", "Date of Results",
"Ordered By", "Position", "PC/Location" };
var headerRow = sheet.CreateRow(0);
//HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
//HSSFFont font = (HSSFFont)workbook.CreateFont();
//font.IsBold = true;
//style.FillBackgroundColor =
// NPOI.SS.UserModel.IndexedColors.BrightGreen.Index;
//style.SetFont(font);
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)workbook.CreateFont();
style.SetFont(font);
font.IsBold = true;
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = IndexedColors.Lime.Index;
//headerRow.RowStyle = style;
//Below loop is create header
for (int i = 0; i < columns.Length; i++)
{
var cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
cell.CellStyle = style;
}
//Below loop is fill content
for (int i = 0; i < dt.Rows.Count; i++)
{
int rowIndex = i + 1;
var row = sheet.CreateRow(rowIndex);
for (int j = 0; j < columns.Length; j++)
{
var cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
// Declare one MemoryStream variable for write file in stream
var stream = new MemoryStream();
workbook.Write(stream);
FileStream file = new FileStream(fullPath, FileMode.Create, FileAccess.Write);
stream.WriteTo(file);
file.Close();
stream.Close();
}
private static void NewFoot(ref HSSFCellStyle headStyle, HSSFWorkbook workbook, short fontHeightInPoints, int Boldweight = 700, bool isBold = true)
{
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = fontHeightInPoints;
font.Boldweight = 700;
font.IsBold = isBold;
headStyle.FillForegroundColor = HSSFColor.Yellow.Index;
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.SetFont(font);
}
/// <summary>
/// 获取表头样式
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
public static HSSFCellStyle GetHeadStyle(this HSSFWorkbook workbook)
{
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
return(headStyle);
}
public void WriteTitle(string title)
{
fontTitle.FontName = "Calibri";
fontTitle.Color = HSSFColor.COLOR_NORMAL;
fontTitle.FontHeightInPoints = 12;
cellStyleTitle.SetFont(fontTitle);
sheet.CreateRow(rowIndex).CreateCell(0).SetCellValue(title);
sheet.GetRow(rowIndex).GetCell(0).CellStyle = cellStyleTitle;
sheet.GetRow(rowIndex).HeightInPoints = 18;
InsertEmptyRow();
}
private HSSFCellStyle CreateRedStyle(HSSFWorkbook workbook)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //创建列头单元格实例样式
var cellStyleFont = (HSSFFont)workbook.CreateFont(); //创建变色字体
cellStyleFont.Color = HSSFColor.Red.Index;
cellStyle.SetFont(cellStyleFont); //将字
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
cellStyle.WrapText = true; //自动换行
return(cellStyle);
}
private static HSSFCellStyle GetHeadStyle(HSSFWorkbook workbook)
{
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 14;
font.Boldweight = 500;
font.FontName = "宋体";
headStyle.SetFont(font);
headStyle.VerticalAlignment = VerticalAlignment.Center;
headStyle.Alignment = HorizontalAlignment.Center;//水平对齐
return(headStyle);
}
static HSSFCellStyle GetTitleStyle(string headFont, short headSize, short headColor
, HSSFCellStyle cellStyle, HSSFFont font)
{
cellStyle = workbook.CreateCellStyle() as HSSFCellStyle;
cellStyle.Alignment = HorizontalAlignment.CENTER;
font = workbook.CreateFont() as HSSFFont;
font.FontName = headFont;
font.FontHeightInPoints = headSize;
font.Color = headColor;
font.Boldweight = 700;
cellStyle.SetFont(font);
return(cellStyle);
}
private void SetCellHyperlink(HSSFWorkbook workbook, HSSFCell cell, String linkAddress)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)cell.CellStyle;
HSSFFont font = (HSSFFont)cellStyle.GetFont(workbook);
font.Underline = FontUnderlineType.Single;
cellStyle.SetFont(font);
HSSFHyperlink hyperlink = new HSSFHyperlink(HyperlinkType.Url);
hyperlink.Address = linkAddress;
cell.Hyperlink = hyperlink;
cell.CellStyle = cellStyle;
}
public HSSFCellStyle Style(IWorkbook wb, short size)
{
//Определение стиля шрифта для содержимого ячеек
HSSFFont hFont = (HSSFFont)wb.CreateFont();
hFont.FontHeightInPoints = size;
hFont.FontName = "Times New Roman";
HSSFCellStyle hStyle = (HSSFCellStyle)wb.CreateCellStyle();
hStyle.SetFont(hFont);
return(hStyle);
}
/// <summary>
/// 设置单元格样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="cell"></param>
public static void setCellStyle(HSSFWorkbook workbook, ICell cell)
{
HSSFCellStyle fCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont ffont = (HSSFFont)workbook.CreateFont();
//ffont.FontHeight = 20 * 20;
ffont.FontName = "宋体";
//ffont.Color = HSSFColor.Red.Index;
fCellStyle.SetFont(ffont);
fCellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直对齐
fCellStyle.Alignment = HorizontalAlignment.Center; //水平对齐
cell.CellStyle = fCellStyle;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dsSource"></param>
/// <param name="excelStream"></param>
/// <param name="SheetName"></param>
/// <remarks>齐鹏飞 2011.05.24</remarks>
public static void ExportExcel(DataSet ds, Stream excelStream, List <string> SheetName)
{
if (ds == null || SheetName == null)
{
return;
}
if (ds.Tables.Count != SheetName.Count)
{
return;
}
int rowCount = 0;
int cellIndex = 0;
HSSFWorkbook excelWorkbook = CreateExcelFile();
HSSFSheet newsheet = null;
for (int i = 0; i < ds.Tables.Count; i++)
{
//循环数据源导出数据集
newsheet = excelWorkbook.CreateSheet(SheetName[i]);
//创建标题
cellIndex = 0;
StarTech.NPOI.NPOIHelper.ListColumnsName = new SortedList(new StarTech.NPOI.NoSort());
//设置标题样式
HSSFCellStyle cellStyle = excelWorkbook.CreateCellStyle();
cellStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
HSSFFont mHSSFFont = excelWorkbook.CreateFont();
mHSSFFont.FontHeightInPoints = 12;//字号
mHSSFFont.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
cellStyle.SetFont(mHSSFFont);
//循环导出列
foreach (DataColumn dc in ds.Tables[i].Columns)
{
HSSFRow newRow = newsheet.CreateRow(0);
HSSFCell newCell = newRow.CreateCell(cellIndex);
newCell.SetCellValue(dc.ColumnName);
newCell.CellStyle = cellStyle;
StarTech.NPOI.NPOIHelper.ListColumnsName.Add(dc.ColumnName, dc.ColumnName);
cellIndex++;
}
rowCount = 0;
foreach (DataRow dr in ds.Tables[i].Rows)
{
++rowCount;
HSSFRow newRow = newsheet.CreateRow(rowCount);
InsertCell(ds.Tables[i], dr, newRow, newsheet, excelWorkbook);
}
}
SaveExcelFile(excelWorkbook, excelStream);
}
private void setCellStyle4(HSSFWorkbook workbook, ICell cell)
{
HSSFCellStyle fCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont ffont = (HSSFFont)workbook.CreateFont();
ffont.FontHeightInPoints = 24;
ffont.Color = HSSFColor.Red.Index;
ffont.IsBold = true;
fCellStyle.SetFont(ffont);
fCellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直对齐
fCellStyle.Alignment = HorizontalAlignment.Center; //水平对齐
fCellStyle.WrapText = true;
cell.CellStyle = fCellStyle;
}
/// <summary>
/// 将DataTable转换成流
/// </summary>
public static Stream RenderDataTableToExcelAndImg(DataTable sourceTable, string path)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet("Sheet1");
HSSFRow hssfRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
hssfRow.HeightInPoints = 120;
HSSFFont cellfont = (HSSFFont)workbook.CreateFont();
cellfont.FontHeightInPoints = 10;
cellStyle.SetFont(cellfont);
byte[] picByte = File.ReadAllBytes(path);
workbook.AddPicture(picByte, PictureType.PNG);
IRow headerRow = sheet.CreateRow(0);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
//picture.Resize();
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
if (column.Ordinal == 0)
{
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 10, 1023, 0, 2 + (column.Ordinal), 0, 2,
1 + (column.Ordinal));
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, 1);
dataRow.CreateCell(column.Ordinal).CellStyle = cellStyle;
}
else
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
/// <summary>
/// 设置单元格样式
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
private static HSSFCellStyle SetCellStyle(this HSSFWorkbook workbook)
{
HSSFCellStyle style = workbook.CreateCellStyle();
style.Alignment = HSSFCellStyle.ALIGN_CENTER;
style.BorderBottom = HSSFCellStyle.BORDER_THIN;
style.BorderLeft = HSSFCellStyle.BORDER_THIN;
style.BorderRight = HSSFCellStyle.BORDER_THIN;
style.BorderTop = HSSFCellStyle.BORDER_THIN;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = HSSFFont.BOLDWEIGHT_NORMAL;
style.SetFont(font);
return(style);
}
protected void BtnExportOnClick(object sender, EventArgs e)
{
var workbook = new HSSFWorkbook();
var ms = new MemoryStream();
var sheet = new HSSFSheet[1];// 增加sheet。
#region
sheet[0] = workbook.CreateSheet("手工发票模板" + DateTime.Now.ToString("yyyy-MM-dd"));//添加sheet名
sheet[0].DefaultColumnWidth = 30;
sheet[0].DefaultRowHeight = 20;
HSSFRow rowtitle = sheet[0].CreateRow(0);
HSSFCellStyle style = workbook.CreateCellStyle();
style.Alignment = HSSFCellStyle.ALIGN_CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Color = HSSFColor.BLACK.index;
font.Boldweight = 1;
style.SetFont(font);
#endregion
int index = 0;
for (var i = 0; i < RgInvoice.Columns.Count - 1; i++)
{
if (!RgInvoice.Columns[i].Display)
{
continue;
}
HSSFCell cell = rowtitle.CreateCell(index);
cell.SetCellValue(RgInvoice.Columns[i].HeaderText);
cell.CellStyle = style;
index++;
}
sheet[0].DisplayGridlines = true;
#region 输出
workbook.Write(ms);
Response.ContentEncoding = Encoding.GetEncoding("utf-8");
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("手工发票模板导出" + DateTime.Now.ToString("yyyyMMdd") + ".xls", Encoding.UTF8));
Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
GC.Collect();
#endregion
}
