/// <summary>
/// 利用office组件导出Excel表格文件
/// </summary>
//public static void ExportExcelFile(DataTable ExcelTable)
//{
// try
// {
// string SaveExcelName = string.Empty;//保存的Excel文件名称
// SaveFileDialog SFDialog = new SaveFileDialog();
// SFDialog.DefaultExt = "xls";
// SFDialog.Filter = "Excel文件(*.xls)|*.xls";
// SFDialog.ShowDialog();
// SaveExcelName = SFDialog.FileName;//获取保存的Excel文件名称
// if (SaveExcelName.IndexOf(":") < 0) return;
// Microsoft.Office.Interop.Excel.Application XlsApp = new Microsoft.Office.Interop.Excel.Application();//创建Excel应用程序
// object missing = System.Reflection.Missing.Value;
// if (XlsApp == null)
// {
// MessageBoxEx.Show("无法创建Excel表格文件，您的电脑可能未安装Excel软件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// return;
// }
// else
// {
// Microsoft.Office.Interop.Excel.Workbooks WkBks = XlsApp.Workbooks;//获取工作簿对像
// Microsoft.Office.Interop.Excel.Workbook WkBk = WkBks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//添加Excel表格模板
// Microsoft.Office.Interop.Excel.Worksheet WkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WkBk.Worksheets[1];//获取工作表格1;
// Microsoft.Office.Interop.Excel.Range Ran;//声明Excel表格
// int TotalCount = ExcelTable.Rows.Count;
// //int rowRead = 0;//读取行数
// //float PercentRead = 0;//导入百分比
// //写入字段名
// for (int i = 0; i < ExcelTable.Columns.Count; i++)
// {
// WkSheet.Cells[1, i + 1] = ExcelTable.Columns[i].ColumnName.ToString();//获取表列名称
// Ran = (Microsoft.Office.Interop.Excel.Range)WkSheet.Cells[1, i + 1];//列名称写入单元格
// Ran.Interior.ColorIndex = 15;
// Ran.Font.Bold = true;//标题加粗
// }
// //ProgressBarMsg ProgBarMsg = new ProgressBarMsg();
// //ProgBarMsg.MaxNum = TotalCount;//获取总记录行项
// //ProgBarMsg.ShowDialog();//显示进度条
// //写字段值
// for (int j = 0; j < ExcelTable.Rows.Count; j++)
// {
// for (int k = 0; k < ExcelTable.Columns.Count; k++)
// {
// WkSheet.Cells[j + 2, k + 1] = ExcelTable.Rows[j][k].ToString();//写表格值
// }
// //rowRead++;
// //PercentRead = ((float)rowRead * 100) / TotalCount;//导入进度百分比
// //ProgressBarMsg.PercentMsg = rowRead;
// //Thread.Sleep(200);
// Application.DoEvents();//处理当前在消息队列中所有windows消息
// }
// WkSheet.SaveAs(SaveExcelName, missing, missing, missing, missing, missing, missing, missing, missing);
// Ran = WkSheet.get_Range((object)WkSheet.Cells[2, 1], (object)WkSheet.Cells[ExcelTable.Rows.Count + 1, ExcelTable.Columns.Count]);//给工作表指定区域
// //设置Excel表格边框样式
// Ran.BorderAround2(missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,
// Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, missing, missing);
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;//设置区域边框颜色
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//连续边框
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//边框浓度
// if (ExcelTable.Columns.Count > 1)
// {//设置垂直表格颜色索引
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
// }
// WkBk.Save();//保存Excel表数据
// //关闭表格对像，并退出应用程序域
// WkBk.Close(missing, missing, missing);
// XlsApp.Quit();
// XlsApp = null;
// GC.Collect();//强制关闭
// MessageBoxEx.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// }
// }
// catch (Exception ex)
// {
// MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
// }
//}
/// <summary>
/// 设置导出Excel标题样式
/// </summary>
/// <param name="HeaderRow">标题行</param>
/// <param name="WkBk">工作表</param>
/// <param name="ColNum">列序号</param>
private static void SetXlsHeaderStyle(HSSFRow HeaderRow, HSSFWorkbook WkBk, int ColNum)
{
try
{
HSSFCellStyle HeaderStyle = (HSSFCellStyle)WkBk.CreateCellStyle(); //创建Excel表格标题样式
HSSFFont fontStyle = (HSSFFont)WkBk.CreateFont(); //创建Excel表格字体样式
HeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //设置居中
HeaderStyle.FillForegroundColor = HSSFColor.Lime.Index; //设置背景色
HeaderStyle.FillPattern = FillPattern.SolidForeground; //设置填充样式
HeaderStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0"); //设置数据格式
fontStyle.FontHeightInPoints = 11;
fontStyle.Boldweight = 700;
HeaderStyle.SetFont(fontStyle);
HeaderRow.GetCell(ColNum).CellStyle = (ICellStyle)HeaderStyle;//设置列样式
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
/// <summary>
/// 导出Excel包含单元格样式的方法
/// 注：要求在需要有样式的单元格加上 @?@，字体颜色变红（特用）
/// </summary>
/// <param name="dataset"></param>
/// <param name="fileName"></param>
public static void ExportExcelFileContainCss(DataSet dataset, string fileName)
{
InitializeWorkbook();
foreach (DataTable dt in dataset.Tables)
{
HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet(dt.TableName);
//设置标题行样式
HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();
font.Color = 10;//代表红色
style.SetFont(font);
HSSFRow row;
HSSFCell cell;
HSSFRow rowCap;
//首先设置表列名称
rowCap = (HSSFRow)sheet1.CreateRow(0);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = (HSSFCell)rowCap.CreateCell(j);
cell.SetCellValue(dt.Columns[j].Caption);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
row = (HSSFRow)sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = (HSSFCell)row.CreateCell(j);
if (dr[j].ToString().Contains("@?@"))
{
dr[j] = dr[j].ToString().Replace("@?@", "");
cell.CellStyle = style;
}
cell.SetCellValue(dr[j].ToString());
}
}
}
SaveToFile(fileName);
}
/// <summary>
/// DataTable转Excel 读取DataTable Columns做为表头
/// </summary>
/// <param name="dt">DataTable</param>
/// <returns></returns>
public static byte[] TableToExcel(DataTable dt)
{
IWorkbook wk = new HSSFWorkbook();
ISheet sheet = wk.CreateSheet();
IRow headerRow = sheet.CreateRow(0); // 表头
// 处理标题
foreach (DataColumn column in dt.Columns)
{
ICell cell = headerRow.CreateCell(column.Ordinal);
cell.SetCellValue(column.ColumnName);
// 设置样式 加粗居中
HSSFCellStyle cellStyle = (HSSFCellStyle)wk.CreateCellStyle();
HSSFFont font = (HSSFFont)wk.CreateFont();
font.Boldweight = 600;
cellStyle.SetFont(font);
cellStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle = cellStyle;
}
// 处理body部分
int rowIndex = 1;
foreach (DataRow row in dt.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column.ColumnName].ToString());
}
rowIndex++;
}
// 冻结首行
sheet.CreateFreezePane(0, 1);
MemoryStream ms = new MemoryStream();
wk.Write(ms);
sheet = null;
headerRow = null;
return(ms.GetBuffer());
}
/// <summary>
/// 自定义单元格样式
/// </summary>
/// <returns></returns>
public HSSFCellStyle CreateStyle(HSSFWorkbook hssfworkbook, short fontHeight = 13 * 14,
BorderStyle borderTop = BorderStyle.None, BorderStyle borderBottom = BorderStyle.None, BorderStyle borderLeft = BorderStyle.None, BorderStyle borderRight = BorderStyle.None,
HorizontalAlignment alignment = HorizontalAlignment.Center, VerticalAlignment verticalAlignment = VerticalAlignment.Center,
bool wrapText = false, string fontName = "新宋体", FontBoldWeight boldweight = FontBoldWeight.Normal
)
{
HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();
font.FontName = fontName;
font.FontHeight = fontHeight; //设置字体大小
font.Boldweight = (short)boldweight;
style.SetFont(font); //样式中加载字体样式
style.Alignment = alignment; //水平居中
style.VerticalAlignment = verticalAlignment; //居中
style.BorderBottom = borderBottom; //设置单元格边框
style.BorderRight = borderRight;
style.BorderLeft = borderLeft;
style.BorderTop = borderTop;
style.WrapText = wrapText;
return(style);
}
/// <summary>
/// 获取通用列样式
/// </summary>
/// <param name="hs"></param>
/// <returns></returns>
public static ICellStyle GetCommonStyle(HSSFWorkbook hs)
{
//样式
ICellStyle commonCellStyle = hs.CreateCellStyle(); //创建样式
commonCellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
//commonCellStyle.Alignment = HorizontalAlignment.CenterSelection;//设置居中
commonCellStyle.Alignment = HorizontalAlignment.Center; //设置居中
HSSFFont commonFont = (HSSFFont)hs.CreateFont(); //创建字体
commonFont.FontName = "宋体";
commonFont.FontHeight = 10;
commonFont.FontHeightInPoints = 10;
commonCellStyle.SetFont(commonFont);
commonCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //下边框线
commonCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //左边框线
commonCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //右边框线
commonCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //上边框线
return(commonCellStyle);
}
private void GenerateHeader(HSSFSheet sheet, HSSFWorkbook workbook, int startRow, int cellIndex)
{
FontRecord rec = new FontRecord()
{
FontHeight = 16
};
rec.BoldWeight = short.MaxValue;
HSSFFont font = new HSSFFont(10, rec);
//font.Index = 16;
var style = workbook.CreateCellStyle();
SetCellStyle(style);
style.SetFont(font);
foreach (var header in _headers)
{
HSSFRow row = sheet.CreateRow(startRow);
var cell = row.CreateCell(cellIndex++);
cell.SetCellValue(Resource.GetString(header));
cell.CellStyle = style;
}
}
/// <summary>
/// 获取头部样式
/// </summary>
/// <param name="hs"></param>
/// <returns></returns>
public static ICellStyle GetHeaderStyle(HSSFWorkbook hs)
{
//样式
ICellStyle headerStyle = hs.CreateCellStyle(); //创建样式
headerStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 方法1
//style.Alignment = HorizontalAlignment.CenterSelection;//设置居中 方法2
headerStyle.Alignment = HorizontalAlignment.Center; //设置居中 方法3
HSSFFont headerfont = (HSSFFont)hs.CreateFont(); //创建字体
headerfont.Color = HSSFColor.Black.Index; //给字体设置颜色
headerfont.FontName = "宋体";
headerfont.IsBold = true;
headerfont.FontHeight = 11;
headerfont.FontHeightInPoints = 11;
headerStyle.SetFont(headerfont);
headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //下边框线
headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //左边框线
headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //右边框线
headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //上边框线
return(headerStyle);
}
static HSSFCellStyle GetColumnStyle(string colHeadFont, short colHeadSize, short colHeadColor, bool colHeadBorder
, HSSFCellStyle cellStyle, HSSFFont font)
{
cellStyle = workbook.CreateCellStyle() as HSSFCellStyle;
cellStyle.Alignment = HorizontalAlignment.CENTER; //居中
if (colHeadBorder == true)
{
//边框
cellStyle.BorderBottom = BorderStyle.THIN;
cellStyle.BorderLeft = BorderStyle.THIN;
cellStyle.BorderRight = BorderStyle.THIN;
cellStyle.BorderTop = BorderStyle.THIN;
}
//font
font = workbook.CreateFont() as HSSFFont;
font.FontName = colHeadFont;
font.FontHeightInPoints = colHeadSize;
font.Color = colHeadColor;
font.Boldweight = 700;
cellStyle.SetFont(font);
return(cellStyle);
}
/// <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 CreateStyle(HSSFWorkbook workbook, HorizontalAlignment horizontal, VerticalAlignment vertical, short boldWeight, short fontPoint, bool isBorder, string fontName = "微软雅黑")
{
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyle.Alignment = horizontal;
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;
cellStyle.SetFont(cellStyleFont);
return(cellStyle);
}
/// <summary>
/// 表头的样色设置
/// </summary>
/// <param name="hssfworkbookDown"></param>
/// <returns></returns>
private static ICellStyle TopRow(HSSFWorkbook hssfworkbookDown)
{
///字体
HSSFFont fontS9 = (HSSFFont)hssfworkbookDown.CreateFont();
fontS9.FontName = "黑体";
fontS9.FontHeightInPoints = 14;
fontS9.Boldweight = (short)FontBoldWeight.Normal;
//样式
ICellStyle table0 = hssfworkbookDown.CreateCellStyle();
table0.BorderLeft = BorderStyle.Thin;
table0.BorderTop = BorderStyle.Thin;
table0.BorderBottom = BorderStyle.Thin;
table0.BorderRight = BorderStyle.Thin;
table0.WrapText = true;
table0.FillForegroundColor = 47;
table0.FillPattern = FillPattern.SolidForeground;
table0.VerticalAlignment = VerticalAlignment.Center; //垂直对齐
table0.Alignment = HorizontalAlignment.Center; //水平对齐
table0.SetFont(fontS9);
return(table0);
}
/// <summary>
/// 设置数据格式
/// </summary>
protected virtual void SetDataCellStyle()
{
//设置窗格的格式
mDateStyle = mWorkbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = mWorkbook.CreateDataFormat() as HSSFDataFormat;
mDateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//表头及样式
mTableHeadStyle = mWorkbook.CreateCellStyle() as HSSFCellStyle;
mTableHeadStyle.Alignment = HorizontalAlignment.CENTER;
HSSFFont font = mWorkbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
mTableHeadStyle.SetFont(font);
//列头及样式
mColumnHeadStyle = mWorkbook.CreateCellStyle() as HSSFCellStyle;
mColumnHeadStyle.Alignment = HorizontalAlignment.CENTER;
font = mWorkbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
mColumnHeadStyle.SetFont(font);
}
private HSSFCellStyle PhaseCellStyle(CellStyle paramCellStyle)
{
HSSFCellStyle style = null;
//try
//{
style = (HSSFCellStyle)_workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)_workbook.CreateFont();
font.FontName = paramCellStyle.FontName.ToString();
font.FontHeightInPoints = paramCellStyle.FontHeightInPoints;
if (paramCellStyle.FontBoldFlag)
{
font.Boldweight = 700;
}
style.SetFont(font);
//设置水平位置
switch (paramCellStyle.HorizontalAlignment.ToString())
{
case "Center":
style.Alignment = HorizontalAlignment.Center;
break;
case "Left":
style.Alignment = HorizontalAlignment.Left;
break;
case "Right":
style.Alignment = HorizontalAlignment.Right;
break;
default:
style.Alignment = HorizontalAlignment.Center;
break;
}
//设置垂直位置
switch (paramCellStyle.VerticalAlignment.ToString())
{
case "Center":
style.VerticalAlignment = VerticalAlignment.Center;
break;
case "Top":
style.VerticalAlignment = VerticalAlignment.Top;
break;
case "Botton":
style.VerticalAlignment = VerticalAlignment.Bottom;
break;
default:
style.VerticalAlignment = VerticalAlignment.Center;
break;
}
//设置表格线样式宽度
switch (paramCellStyle.BorderStyle.ToString())
{
case "Thin":
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
break;
default:
break;
}
//设置表格线颜色
switch (paramCellStyle.BorderStyle.ToString())
{
case "Black":
style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
break;
default:
break;
}
//设置单元格是否换行
#region 设置为文本
style.WrapText = paramCellStyle.WrapText;
HSSFDataFormat format = (HSSFDataFormat)_workbook.GetCreationHelper().CreateDataFormat();
short index = format.GetFormat("@");
style.DataFormat = index;
#endregion
//}
//catch(Exception ex)
//{
// string ss = ex.Message;
// return null;
//}
return(style);
}
/// <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, List <ExcelTitle> tableHeaders, string sheetPrefix) where T : class, new()
{
HSSFWorkbook hssWorkBook = new HSSFWorkbook(); // 创建工作薄
int sheetNums = (int)Math.Ceiling(list.Count / 50000.00);
ICellStyle cellStyle = hssWorkBook.CreateCellStyle();
Dictionary <string, ICellStyle> dataFormatMap = new Dictionary <string, ICellStyle>();
for (int i = 0; i < sheetNums; i++)
{
ISheet sheet = hssWorkBook.CreateSheet(sheetPrefix + (i + 1)); // 创建sheet表
// 创建表头
IRow rowHead = sheet.CreateRow(0);
int rowId = 0;
foreach (var title in tableHeaders)
{
ICell cell = rowHead.CreateCell(rowId);
cell.SetCellValue(title.Title);
// 设置样式 加粗居中
HSSFFont font = (HSSFFont)hssWorkBook.CreateFont();
font.Boldweight = 600;
cellStyle.SetFont(font);
cellStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle = cellStyle;
sheet.AutoSizeColumn(rowId);
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 (var title in tableHeaders)
{
foreach (PropertyInfo property in properties)
{
if (string.Equals(property.Name, title.PropertyName, StringComparison.CurrentCultureIgnoreCase))
{
ICell cell = rowBody.CreateCell(cellIndex);
FillCellValue(cell, property.GetValue(item, null));
if (!string.IsNullOrWhiteSpace(title.DataFormat))
{
if (dataFormatMap.ContainsKey(title.DataFormat))
{
SetCellDataFormat(dataFormatMap[title.DataFormat], cell, title.DataFormat);
}
else
{
ICellStyle cellFormat = hssWorkBook.CreateCellStyle();
dataFormatMap.Add(title.DataFormat, cellFormat);
SetCellDataFormat(cellFormat, cell, title.DataFormat);
}
}
break;
}
}
cellIndex++;
}
rowId++;
}
// 冻结首行
sheet.CreateFreezePane(0, 1);
}
MemoryStream file = new MemoryStream();
hssWorkBook.Write(file);
return(file.GetBuffer());
}
/// <summary>
/// list转Excel
/// </summary>
/// <typeparam name="T">传入list 类型 </typeparam>
/// <param name="SheetName">sheet名称</param>
/// <param name="list">list集合</param>
/// <param name="FiedNames">名称序列</param>
/// <returns></returns>
public static HSSFWorkbook ListtoExcel <T>(string SheetName, List <T> list, Dictionary <string, string> FiedNames)
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(SheetName); //创建工作表
sheet.CreateFreezePane(0, 1); //冻结列头行
HSSFRow row_Title = (HSSFRow)sheet.CreateRow(0); //创建列头行
row_Title.HeightInPoints = 30.5F; //设置列头行高
HSSFCellStyle cs_Title = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
HSSFFont cs_Title_Font = (HSSFFont)wb.CreateFont(); //创建字体
cs_Title_Font.IsBold = true; //字体加粗
cs_Title_Font.FontHeightInPoints = 14; //字体大小
cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式
#region 生成列头
int ii = 0;
foreach (string key in FiedNames.Keys)
{
HSSFCell cell_Title = (HSSFCell)row_Title.CreateCell(ii); //创建单元格
cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
cell_Title.SetCellValue(key);
sheet.SetColumnWidth(ii, 25 * 256); //设置列宽
ii++;
}
#endregion
//获取 实体类 类型对象
Type t = typeof(T); // model.GetType();
//获取 实体类 所有的 公有属性
List <PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
//创建 实体属性 字典集合
Dictionary <string, PropertyInfo> dictPros = new Dictionary <string, PropertyInfo>();
//将 实体属性 中要修改的属性名 添加到 字典集合中 键：属性名 值：属性对象
proInfos.ForEach(p =>
{
if (FiedNames.Values.Contains(p.Name))
{
dictPros.Add(p.Name, p);
}
});
HSSFCellStyle cs_Content = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
cs_Content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
cs_Content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
for (int i = 0; i < list.Count; i++)
{
HSSFRow row_Content = (HSSFRow)sheet.CreateRow(i + 1); //创建行
row_Content.HeightInPoints = 20;
int jj = 0;
foreach (string proName in FiedNames.Values)
{
if (dictPros.ContainsKey(proName))
{
HSSFCell cell_Conent = (HSSFCell)row_Content.CreateCell(jj); //创建单元格
cell_Conent.CellStyle = cs_Content;
//如果存在，则取出要属性对象
PropertyInfo proInfo = dictPros[proName];
//获取对应属性的值
object value = proInfo.GetValue(list[i], null); //object newValue = model.uName;
string cell_value = value == null ? "" : value.ToString();
cell_Conent.SetCellValue(cell_value);
jj++;
}
}
}
return(wb);
}
public void TestOptimiseStyles()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Two fonts
Assert.AreEqual(4, wb.NumberOfFonts);
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
Assert.AreEqual(6, wb.NumberOfFonts);
// Several styles
Assert.AreEqual(21, wb.NumCellStyles);
NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f2);
NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f1);
cs4.Alignment = HorizontalAlignment.CenterSelection;// ((short)22);
NPOI.SS.UserModel.ICellStyle cs5 = wb.CreateCellStyle();
cs5.SetFont(f2);
cs5.Alignment = HorizontalAlignment.Fill; //((short)111);
NPOI.SS.UserModel.ICellStyle cs6 = wb.CreateCellStyle();
cs6.SetFont(f2);
Assert.AreEqual(27, wb.NumCellStyles);
// Use them
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
r.CreateCell(0).CellStyle = (cs1);
r.CreateCell(1).CellStyle = (cs2);
r.CreateCell(2).CellStyle = (cs3);
r.CreateCell(3).CellStyle = (cs4);
r.CreateCell(4).CellStyle = (cs5);
r.CreateCell(5).CellStyle = (cs6);
r.CreateCell(6).CellStyle = (cs1);
r.CreateCell(7).CellStyle = (cs2);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
Assert.AreEqual(26, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// Optimise
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(6, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(1)).CellValueRecord.XFIndex);
Assert.AreEqual(22, r.GetCell(1).CellStyle.GetFont(wb).FontHeight);
// cs3 = cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(2)).CellValueRecord.XFIndex);
// cs4 --> 24 -> 23
Assert.AreEqual(23, ((HSSFCell)r.GetCell(3)).CellValueRecord.XFIndex);
// cs5 --> 25 -> 24
Assert.AreEqual(24, ((HSSFCell)r.GetCell(4)).CellValueRecord.XFIndex);
// cs6 = cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(7)).CellValueRecord.XFIndex);
// Add a new duplicate, and two that aren't used
HSSFCellStyle csD = (HSSFCellStyle)wb.CreateCellStyle();
csD.SetFont(f1);
r.CreateCell(8).CellStyle = (csD);
HSSFFont f3 = (HSSFFont)wb.CreateFont();
f3.FontHeight = ((short)23);
f3.FontName = ("Testing 3");
HSSFFont f4 = (HSSFFont)wb.CreateFont();
f4.FontHeight = ((short)24);
f4.FontName = ("Testing 4");
HSSFCellStyle csU1 = (HSSFCellStyle)wb.CreateCellStyle();
csU1.SetFont(f3);
HSSFCellStyle csU2 = (HSSFCellStyle)wb.CreateCellStyle();
csU2.SetFont(f4);
// Check before the optimise
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(28, wb.NumCellStyles);
// Optimise, should remove the two un-used ones and the one duplicate
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// csD -> cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(8)).CellValueRecord.XFIndex);
}
/// <summary>
/// 使用模版导出EXCEL
/// </summary>
/// <param name="dt1"></param>
/// <param name="dt2"></param>
/// <param name="excelTemplatePath"></param>
/// <param name="sheetName1"></param>
/// <param name="sheetName2"></param>
/// <returns></returns>
public static MemoryStream ExportFromTemplate(DataTable dt1, DataTable dt2
, string excelTemplatePath
, string sheetName1, string sheetName2)
{
THOK.Common.NPOI.Common.ExportExcelHelper.BrowserLoad(sheetName1);
FileStream file = new FileStream(excelTemplatePath, FileMode.Open, FileAccess.Read);//读入excel模板
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet(sheetName1);
HSSFSheet sheet2 = (HSSFSheet)hssfworkbook.GetSheet(sheetName2);
sheet1.PrintSetup.FitHeight = 0;
sheet2.PrintSetup.FitHeight = 0;
string exportDate = "导出时间：" + System.DateTime.Now.ToString("yyyy-MM-dd");
sheet1.GetRow(0).GetCell(0).SetCellValue(sheetName1);
sheet1.GetRow(1).GetCell(0).SetCellValue(exportDate);
sheet2.GetRow(0).GetCell(0).SetCellValue(sheetName2);
sheet2.GetRow(1).GetCell(0).SetCellValue(exportDate);
#region 取得列宽 表一
int[] arrColWidth = new int[dt1.Columns.Count];
foreach (DataColumn item in dt1.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;//936是指GB2312编码
}
for (int i = 0; i < dt1.Rows.Count; i++)
{
for (int j = 0; j < dt1.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt1.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
#endregion
#region 取得列宽 表二
int[] arrColWidth2 = new int[0];
if (dt2 != null && sheetName2 != null)
{
arrColWidth2 = new int[dt2.Columns.Count];
foreach (DataColumn item in dt2.Columns)
{
arrColWidth2[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dt2.Rows.Count; i++)
{
for (int j = 0; j < dt2.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt2.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth2[j])
{
arrColWidth2[j] = intTemp;
}
}
}
}
#endregion
#region dt1
int rowIndex1 = 2, colIndex1 = 0;
foreach (DataRow row in dt1.Rows)
{
rowIndex1++;
colIndex1 = 0;
HSSFRow xlsRow = sheet1.CreateRow(rowIndex1) as HSSFRow;
HSSFCellStyle contentStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
foreach (DataColumn column in dt1.Columns)
{
HSSFCell newCell = xlsRow.CreateCell(column.Ordinal) as HSSFCell;
contentStyle.BorderBottom = BorderStyle.THIN;
contentStyle.BorderLeft = BorderStyle.THIN;
contentStyle.BorderRight = BorderStyle.THIN;
contentStyle.BorderTop = BorderStyle.THIN;
xlsRow.GetCell(column.Ordinal).CellStyle = contentStyle;
FillContent(row, column, xlsRow, newCell);
sheet1.SetColumnWidth(column.Ordinal, Convert.ToInt32((arrColWidth[column.Ordinal] + 0.5) * 256));
colIndex1++;
}
}
#endregion
#region dt2
int rowIndex2 = 2, colIndex2 = 0;
foreach (DataRow row in dt2.Rows)
{
rowIndex2++;
colIndex2 = 0;
HSSFRow xlsRow = sheet2.CreateRow(rowIndex2) as HSSFRow;
HSSFCellStyle contentStyle1 = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
HSSFCellStyle contentStyle2 = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
HSSFFont font = hssfworkbook.CreateFont() as HSSFFont;
foreach (DataColumn column in dt2.Columns)
{
HSSFCell newCell = xlsRow.CreateCell(column.Ordinal) as HSSFCell;
if (column.Ordinal == 5 || column.Ordinal == 7 || column.Ordinal == 10)
{
contentStyle2.BorderBottom = BorderStyle.THIN;
contentStyle2.BorderLeft = BorderStyle.THIN;
contentStyle2.BorderRight = BorderStyle.THIN;
contentStyle2.BorderTop = BorderStyle.THIN;
font.Color = HSSFColor.RED.index;
contentStyle2.SetFont(font);
xlsRow.GetCell(column.Ordinal).CellStyle = contentStyle2;
}
else
{
contentStyle1.BorderBottom = BorderStyle.THIN;
contentStyle1.BorderLeft = BorderStyle.THIN;
contentStyle1.BorderRight = BorderStyle.THIN;
contentStyle1.BorderTop = BorderStyle.THIN;
xlsRow.GetCell(column.Ordinal).CellStyle = contentStyle1;
}
FillContent(row, column, xlsRow, newCell);
sheet2.SetColumnWidth(column.Ordinal, Convert.ToInt32((arrColWidth2[column.Ordinal] + 0.5) * 256));
colIndex2++;
}
}
#endregion
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
ms.Flush();
ms.Position = 0;
file.Close();
return(ms);
}
void OnGUI()
{
GUIStyle style = new GUIStyle();
GUI.Label(new Rect(10, 10, 100, 30), "Sheet Name:");
MySheetName = GUI.TextField(new Rect(90, 10, 200, 30), MySheetName, 25);
style.richText = true;
FileStream MyAddress = new FileStream(Application.dataPath + "1.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
if (GUI.Button(new Rect(300, 10, 200, 30), "Create Excel Files With " + "<color=red>NPOI</color>"))
{
HSSFWorkbook MyWorkbook = new HSSFWorkbook();
HSSFSheet Sheet01 = (HSSFSheet)MyWorkbook.CreateSheet(MySheetName);
for (int i = 0; i < 5; i++)
{
HSSFRow Row = (HSSFRow)Sheet01.CreateRow((short)i);
HSSFCell cell = (HSSFCell)Row.CreateCell((short)0);
cell.SetCellValue(MyCellArray[i]);
if (i < MyCellArray02.Count)
{
HSSFCell cell02 = (HSSFCell)Row.CreateCell((short)1);
cell02.SetCellValue(MyCellArray02[i]);
}
else
{
HSSFCell cell02 = (HSSFCell)Row.CreateCell((short)1);
cell02.SetCellValue("");
}
Row.RowStyle = MyWorkbook.CreateCellStyle();
Row.RowStyle.BorderBottom = BorderStyle.Double;
cell.CellStyle = MyWorkbook.CreateCellStyle();
cell.CellStyle.BorderRight = BorderStyle.Thin;
cell.CellStyle.BorderBottom = BorderStyle.Dashed;
cell.CellStyle.BottomBorderColor = HSSFColor.Red.Index;
HSSFFont MyFont = (HSSFFont)MyWorkbook.CreateFont();
MyFont.FontName = "Tahoma";
MyFont.FontHeightInPoints = 14;
MyFont.Color = HSSFColor.Gold.Index;
MyFont.Boldweight = (short)FontBoldWeight.Bold;
cell.CellStyle.SetFont(MyFont);
//PrefabUtility.CreateEmptyPrefab("aq");
}
MyWorkbook.Write(MyAddress);
MyWorkbook.Close();
}
if (GUI.Button(new Rect(60, 120, 200, 30), "Reading Excel Files With " + "<color=yellow>NPOI</color>"))
{
ReadExcelEnable_NPOI = true;
ReadExcelEnable_ExcelDataReader = false;
MyString.Clear();
}
if (ReadExcelEnable_NPOI == true)
{
HSSFWorkbook MyBook;
using (FileStream MyAddress_Read = new FileStream(Application.dataPath + "/1.xls", FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
MyBook = new HSSFWorkbook(MyAddress_Read);
}
ISheet Sheet_Read = MyBook.GetSheet(MySheetName);
for (int row = 0; row <= Sheet_Read.LastRowNum; row++)
{
IRow Row_Read = Sheet_Read.GetRow(row);
for (int cells = 0; cells < Row_Read.LastCellNum; cells++)
{
GUI.Label(new Rect(400 + cells * 200, 100 + row * 40, 200, 20), Row_Read.GetCell(cells).ToString());
//Arabic And Persian Support
//GUI.Label(new Rect(400 + cells * 200,100+ row * 40, 200, 20),ArabicFixer.Fix(Row_Read.GetCell(cells).ToString()));
}
}
}
if (GUI.Button(new Rect(10, 160, 300, 30), "Reading Excel Files With " + "<color=yellow>ExcelDataReader</color>"))
{
ReadExcelEnable_ExcelDataReader = true;
ReadExcelEnable_NPOI = false;
MyString.Clear();
}
if (ReadExcelEnable_ExcelDataReader == true)
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(MyAddress);
DataSet Result = excelReader.AsDataSet();
excelReader.IsFirstRowAsColumnNames = true;
int NumberOfRows = Result.Tables[0].Rows.Count;
int NumberOfCols = Result.Tables[0].Columns.Count;
for (int i = 0; i < NumberOfRows; i++)
{
for (int j = 0; j < NumberOfCols; j++)
{
MyString.Add(Result.Tables[0].Rows[i][j].ToString());
}
}
ReadExcelEnable_ExcelDataReader = false;
}
for (int b = 0; b < MyString.Count; b++)
{
GUI.Label(new Rect(400, 100 + 20 * b, 200, 20), MyString[b]);
//Arabic And Persian Support
//GUI.Label(new Rect(400, 100 + 20 * b, 200, 20), ArabicFixer.Fix(MyString[b]));
}
#if UNITY_EDITOR
AssetDatabase.Refresh();
#endif
}
public void Report(string FileName)
{
int pos = 0;
//Export to excel
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
#region Create fonts and styles
HSSFFont HeaderF1 = hssfworkbook.CreateFont();
HeaderF1.FontHeightInPoints = 11;
HeaderF1.Boldweight = 11 * 256;
HeaderF1.FontName = "Calibri";
HSSFFont HeaderF2 = hssfworkbook.CreateFont();
HeaderF2.FontHeightInPoints = 10;
HeaderF2.Boldweight = 10 * 256;
HeaderF2.FontName = "Calibri";
HSSFFont HeaderF3 = hssfworkbook.CreateFont();
HeaderF3.FontHeightInPoints = 9;
HeaderF3.Boldweight = 9 * 256;
HeaderF3.FontName = "Calibri";
HSSFFont SimpleF = hssfworkbook.CreateFont();
SimpleF.FontHeightInPoints = 10;
SimpleF.FontName = "Calibri";
HSSFCellStyle SimpleCS = hssfworkbook.CreateCellStyle();
SimpleCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SimpleCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SimpleCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SimpleCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SimpleCS.TopBorderColor = HSSFColor.BLACK.index;
SimpleCS.SetFont(SimpleF);
HSSFCellStyle CountCS = hssfworkbook.CreateCellStyle();
CountCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
CountCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountCS.BottomBorderColor = HSSFColor.BLACK.index;
CountCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountCS.LeftBorderColor = HSSFColor.BLACK.index;
CountCS.BorderRight = HSSFCellStyle.BORDER_THIN;
CountCS.RightBorderColor = HSSFColor.BLACK.index;
CountCS.BorderTop = HSSFCellStyle.BORDER_THIN;
CountCS.TopBorderColor = HSSFColor.BLACK.index;
CountCS.SetFont(SimpleF);
HSSFCellStyle WeightCS = hssfworkbook.CreateCellStyle();
WeightCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
WeightCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
WeightCS.BottomBorderColor = HSSFColor.BLACK.index;
WeightCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
WeightCS.LeftBorderColor = HSSFColor.BLACK.index;
WeightCS.BorderRight = HSSFCellStyle.BORDER_THIN;
WeightCS.RightBorderColor = HSSFColor.BLACK.index;
WeightCS.BorderTop = HSSFCellStyle.BORDER_THIN;
WeightCS.TopBorderColor = HSSFColor.BLACK.index;
WeightCS.SetFont(SimpleF);
HSSFCellStyle PriceBelCS = hssfworkbook.CreateCellStyle();
PriceBelCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
PriceBelCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
PriceBelCS.BottomBorderColor = HSSFColor.BLACK.index;
PriceBelCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
PriceBelCS.LeftBorderColor = HSSFColor.BLACK.index;
PriceBelCS.BorderRight = HSSFCellStyle.BORDER_THIN;
PriceBelCS.RightBorderColor = HSSFColor.BLACK.index;
PriceBelCS.BorderTop = HSSFCellStyle.BORDER_THIN;
PriceBelCS.TopBorderColor = HSSFColor.BLACK.index;
PriceBelCS.SetFont(SimpleF);
HSSFCellStyle PriceForeignCS = hssfworkbook.CreateCellStyle();
PriceForeignCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
PriceForeignCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
PriceForeignCS.BottomBorderColor = HSSFColor.BLACK.index;
PriceForeignCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
PriceForeignCS.LeftBorderColor = HSSFColor.BLACK.index;
PriceForeignCS.BorderRight = HSSFCellStyle.BORDER_THIN;
PriceForeignCS.RightBorderColor = HSSFColor.BLACK.index;
PriceForeignCS.BorderTop = HSSFCellStyle.BORDER_THIN;
PriceForeignCS.TopBorderColor = HSSFColor.BLACK.index;
PriceForeignCS.SetFont(SimpleF);
HSSFCellStyle ReportCS1 = hssfworkbook.CreateCellStyle();
ReportCS1.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
ReportCS1.BottomBorderColor = HSSFColor.BLACK.index;
ReportCS1.SetFont(HeaderF1);
HSSFCellStyle ReportCS2 = hssfworkbook.CreateCellStyle();
ReportCS2.SetFont(HeaderF1);
HSSFCellStyle SummaryWithoutBorderBelCS = hssfworkbook.CreateCellStyle();
SummaryWithoutBorderBelCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
SummaryWithoutBorderBelCS.SetFont(HeaderF2);
HSSFCellStyle SummaryWithoutBorderForeignCS = hssfworkbook.CreateCellStyle();
SummaryWithoutBorderForeignCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
SummaryWithoutBorderForeignCS.SetFont(HeaderF2);
HSSFCellStyle SummaryWeightCS = hssfworkbook.CreateCellStyle();
SummaryWeightCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
SummaryWeightCS.SetFont(HeaderF2);
HSSFCellStyle SummaryWithBorderBelCS = hssfworkbook.CreateCellStyle();
SummaryWithBorderBelCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
SummaryWithBorderBelCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderBelCS.BottomBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderBelCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderBelCS.LeftBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderBelCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderBelCS.RightBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderBelCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderBelCS.TopBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderBelCS.WrapText = true;
SummaryWithBorderBelCS.SetFont(HeaderF2);
HSSFCellStyle SummaryWithBorderForeignCS = hssfworkbook.CreateCellStyle();
SummaryWithBorderForeignCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
SummaryWithBorderForeignCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderForeignCS.BottomBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderForeignCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderForeignCS.LeftBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderForeignCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderForeignCS.RightBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderForeignCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderForeignCS.TopBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderForeignCS.WrapText = true;
SummaryWithBorderForeignCS.SetFont(HeaderF2);
HSSFCellStyle SimpleHeaderCS = hssfworkbook.CreateCellStyle();
SimpleHeaderCS.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderLeft = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderRight = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderTop = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.TopBorderColor = HSSFColor.BLACK.index;
//SimpleHeaderCS.WrapText = true;
SimpleHeaderCS.SetFont(HeaderF3);
#endregion Create fonts and styles
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Маркетинг");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
int DisplayIndex = 0;
sheet1.SetColumnWidth(DisplayIndex++, 40 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 9 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 15 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 14 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 14 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 14 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 9 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 9 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 9 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 13 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 13 * 256);
HSSFCell Cell1;
DisplayIndex = 0;
if (AdditionalCostDT.Rows.Count > 0)
{
pos += 2;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Менеджер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Дата согласования");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Сумма дополнительно");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Сумма заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Валюта");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Курс");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Расчетный курс");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Итого сумма дополнительно");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Итого сумма заказа");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
int ColumnCount = AdditionalCostDT.Columns.Count;
for (int x = 0; x < AdditionalCostDT.Rows.Count; x++)
{
for (int y = 0; y < ColumnCount; y++)
{
Type t = AdditionalCostDT.Rows[x][y].GetType();
if (t.Name == "Decimal")
{
HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y);
cell.SetCellValue(Convert.ToDouble(AdditionalCostDT.Rows[x][y]));
cell.CellStyle = CountCS;
continue;
}
if (t.Name == "Int32")
{
HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y);
cell.SetCellValue(Convert.ToInt32(AdditionalCostDT.Rows[x][y]));
cell.CellStyle = SimpleCS;
continue;
}
if (t.Name == "Boolean")
{
bool b = Convert.ToBoolean(AdditionalCostDT.Rows[x][y]);
string str = "Да";
if (!b)
{
str = "Нет";
}
HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y);
cell.SetCellValue(str);
cell.CellStyle = SimpleCS;
continue;
}
if (t.Name == "DateTime")
{
string dateTime = Convert.ToDateTime(AdditionalCostDT.Rows[x][y]).ToShortDateString();
HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y);
cell.SetCellValue(dateTime);
cell.CellStyle = SimpleCS;
continue;
}
if (t.Name == "String" || t.Name == "DBNull")
{
HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y);
cell.SetCellValue(AdditionalCostDT.Rows[x][y].ToString());
cell.CellStyle = SimpleCS;
continue;
}
}
pos++;
}
string tempFolder = System.Environment.GetEnvironmentVariable("TEMP");
FileInfo file = new FileInfo(tempFolder + @"\" + FileName + ".xls");
int j = 1;
while (file.Exists == true)
{
file = new FileInfo(tempFolder + @"\" + FileName + "(" + j++ + ").xls");
}
FileStream NewFile = new FileStream(file.FullName, FileMode.Create);
hssfworkbook.Write(NewFile);
NewFile.Close();
System.Diagnostics.Process.Start(file.FullName);
}
}
private void button1_Click(object sender, EventArgs e)
{
//查询数据，显示到表格上
ManagerInfoBll miBll = new ManagerInfoBll();
var list = miBll.GetList();
dataGridView1.DataSource = list;
//进行excel生成创建操作
//1、创建workbook,不指定参数，表示创建一个新的工作本
HSSFWorkbook workbook = new HSSFWorkbook();
//2、创建sheet
HSSFSheet sheet = workbook.CreateSheet("管理员");
//3、创建row
HSSFRow row = sheet.CreateRow(0);
//4、创建cell
HSSFCell cell0 = row.CreateCell(0);
cell0.SetCellValue("管理员列表");
//5、设置合并单元格
sheet.AddMergedRegion(new NPOI.HSSF.Util.Region(0, 0, 0, 3));
//6、设置单元格居中
HSSFCellStyle styleTitle = workbook.CreateCellStyle();
styleTitle.Alignment = 2;//居中
cell0.CellStyle = styleTitle;
//6.1设置字体
HSSFFont fontTitle = workbook.CreateFont();
fontTitle.FontHeightInPoints = 14;
styleTitle.SetFont(fontTitle);
//7、创建标题行
//7.1创建行
HSSFRow rowTitle = sheet.CreateRow(1);
//7.2创建单元格
HSSFCell cellTitle0 = rowTitle.CreateCell(0);
cellTitle0.SetCellValue("编号");
cellTitle0.CellStyle = styleTitle;
HSSFCell cellTitle1 = rowTitle.CreateCell(1);
cellTitle1.SetCellValue("姓名");
cellTitle1.CellStyle = styleTitle;
HSSFCell cellTitle2 = rowTitle.CreateCell(2);
cellTitle2.SetCellValue("密码");
cellTitle2.CellStyle = styleTitle;
HSSFCell cellTitle3 = rowTitle.CreateCell(3);
cellTitle3.SetCellValue("类型");
cellTitle3.CellStyle = styleTitle;
//8、遍历集合，创建正文数据
//8.1遍历集合
int rowIndex = 2;
foreach (var mi in list)
{
//8.2创建行
HSSFRow rowData = sheet.CreateRow(rowIndex++);
//8.3创建数据单元格
HSSFCell cellData0 = rowData.CreateCell(0);
cellData0.SetCellValue(mi.MId);
HSSFCell cellData1 = rowData.CreateCell(1);
cellData1.SetCellValue(mi.MName);
HSSFCell cellData2 = rowData.CreateCell(2);
cellData2.SetCellValue(mi.MPwd);
HSSFCell cellData3 = rowData.CreateCell(3);
cellData3.SetCellValue(mi.MType == 1?"经理":"店员");
}
//保存工作本
FileStream stream = new FileStream(@"C:\Users\q1\Desktop\t1.xls", FileMode.Create);
workbook.Write(stream);
stream.Close();
stream.Dispose();
}
public static void CopyCell(HSSFCell oldCell, HSSFCell newCell, IDictionary <Int32, HSSFCellStyle> styleMap, Dictionary <short, short> paletteMap, Boolean keepFormulas)
{
if (styleMap != null)
{
if (oldCell.CellStyle != null)
{
if (oldCell.Sheet.Workbook == newCell.Sheet.Workbook)
{
newCell.CellStyle = oldCell.CellStyle;
}
else
{
int styleHashCode = oldCell.CellStyle.GetHashCode();
if (styleMap.ContainsKey(styleHashCode))
{
newCell.CellStyle = styleMap[styleHashCode];
}
else
{
HSSFCellStyle newCellStyle = (HSSFCellStyle)newCell.Sheet.Workbook.CreateCellStyle();
newCellStyle.CloneStyleFrom(oldCell.CellStyle);
RemapCellStyle(newCellStyle, paletteMap); //Clone copies as-is, we need to remap colors manually
newCell.CellStyle = newCellStyle;
//Clone of cell style always clones the font. This makes my life easier
IFont theFont = newCellStyle.GetFont(newCell.Sheet.Workbook);
if (theFont.Color > 0 && paletteMap.ContainsKey(theFont.Color))
{
theFont.Color = paletteMap[theFont.Color]; //Remap font color
}
styleMap.Add(styleHashCode, newCellStyle);
}
}
}
else
{
newCell.CellStyle = null;
}
}
switch (oldCell.CellType)
{
case CellType.String:
HSSFRichTextString rts = oldCell.RichStringCellValue as HSSFRichTextString;
newCell.SetCellValue(rts);
if (rts != null)
{
for (int j = 0; j < rts.NumFormattingRuns; j++)
{
short fontIndex = rts.GetFontOfFormattingRun(j);
int startIndex = rts.GetIndexOfFormattingRun(j);
int endIndex = 0;
if (j + 1 == rts.NumFormattingRuns)
{
endIndex = rts.Length;
}
else
{
endIndex = rts.GetIndexOfFormattingRun(j + 1);
}
FontRecord fr = newCell.BoundWorkbook.CreateNewFont();
fr.CloneStyleFrom(oldCell.BoundWorkbook.GetFontRecordAt(fontIndex));
HSSFFont font = new HSSFFont((short)(newCell.BoundWorkbook.GetFontIndex(fr)), fr);
newCell.RichStringCellValue.ApplyFont(startIndex, endIndex, font);
}
}
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.Blank:
newCell.SetCellType(CellType.Blank);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
if (keepFormulas)
{
newCell.SetCellType(CellType.Formula);
newCell.CellFormula = oldCell.CellFormula;
}
else
{
try
{
newCell.SetCellType(CellType.Numeric);
newCell.SetCellValue(oldCell.NumericCellValue);
}
catch (Exception ex)
{
newCell.SetCellType(CellType.String);
newCell.SetCellValue(oldCell.ToString());
}
}
break;
default:
break;
}
}
//private static WriteLog wl = new WriteLog();
#region 从datatable中将数据导出到excel
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "柳永法"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
workbook.Dispose();
return(ms);
}
}
/// <summary>
/// DataTable转换成Excel文件
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="titles">标题</param>
/// <param name="exprotName">文件名</param>
/// <param name="max">导出最多行数</param>
/// <returns></returns>
public static byte[] TableToExcel(DataTable dt, object[,] titles, string exprotName, int max = int.MaxValue)
{
/**
* titles demo
* public string SayHello(string msg)
* object[,] titles = new object[,] {
* { "OrderCode", "订单编号", "" },
* { "OrderState", "订单状态", new Func<string, string>(SayHello) },
* { "SalePrice", "订单金额", new object[]{ new Func<string, string>(getjine), "SalePrice", "DeliveryFee" } }
* };
*/
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
// 新建一个Excel页签
ISheet sheet = hssfworkbook.CreateSheet("Sheet1");
IRow rowhead = sheet.CreateRow(0);
Dictionary <string, object[]> titDic = new Dictionary <string, object[]>(); // key-属性名称 value-调用方法或空
string strTemp = string.Empty;
// 设置表头
for (int i = 0; i < titles.GetLength(0); i++)
{
ICell cell = rowhead.CreateCell(i);
cell.SetCellValue((string)titles[i, 1]);
// 设置样式 加粗居中
HSSFCellStyle cellStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();
font.Boldweight = 600;
cellStyle.SetFont(font);
cellStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle = cellStyle;
if (!titDic.ContainsKey(strTemp = ((string)titles[i, 0]).ToLower())) // 执行if判断之后，把值赋给变量
{
titDic.Add(strTemp, new object[2] {
titles[i, 1], titles[i, 2]
}); // key=>字段 value=>字段中文名 字段值获取方法
}
}
int index;
for (int i = 0; i < dt.Rows.Count && i < max; i++)
{
IRow rowbody = sheet.CreateRow(i + 1);
index = 0;
foreach (KeyValuePair <string, object[]> item in titDic)
{
object[] val = titDic[dt.Columns[item.Key].ToString().ToLower()];
object fun = val[1];
ICell cell = rowbody.CreateCell(index++);
if (fun is Func <string, string> )
{
cell.SetCellValue((fun as Func <string, string>)(dt.Rows[i][item.Key].ToString()));
}
else if (fun is object[])
{
cell.SetCellValue((((object[])fun)[0] as Func <string, string, string>)(dt.Rows[i][(string)((object[])fun)[1]].ToString(), dt.Rows[i][(string)((object[])fun)[2]].ToString()));
}
else
{
FillCellValue(cell, dt.Rows[i][item.Key]);
}
}
}
// 冻结首行
sheet.CreateFreezePane(0, 1);
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
sheet = null;
return(file.GetBuffer());
}
public static bool ToSingleMbExcels(string savePath, string mbPath, List <SingleMb> dataList, string gwdcode, int rownum)
{
try
{
IWorkbook workbook;
try
{
using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(file);//创建对应文件EXCEL2003
}
}
catch (Exception ex)
{
using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(file);//创建对应文件EXCEL2007
}
}
#region 样式
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
style.WrapText = true;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HSSFFont font = (HSSFFont)workbook.CreateFont();
//font.FontName = "宋体";
font.FontName = "Arial Narrow";
font.FontHeightInPoints = 9;
style.SetFont(font);
HSSFFont fontSmall = (HSSFFont)workbook.CreateFont();
fontSmall.FontName = "Arial Narrow";
fontSmall.FontHeightInPoints = 8;
#endregion
ISheet sheet = workbook.GetSheetAt(0);
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.WrapText = true;//换行
foreach (SingleMb item in dataList)
{
IRow row = sheet.GetRow(item.rowIndex - 1);
if (row == null)
{
row = sheet.CreateRow(item.rowIndex - 1);
}
ICell cell = row.GetCell(item.cellIndex - 1, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
//cell.CellStyle = cellStyle;
cell.SetCellValue(item.value); //循环往第二行的单元格中添加数据
}
#region 插入图片
string gwdgz = gwdcode;
string imageFilePath = HttpContext.Current.Server.MapPath("/Content/TASK/images/");
if (System.IO.File.Exists(imageFilePath + gwdgz + ".png"))
{
byte[] bytes = System.IO.File.ReadAllBytes(imageFilePath + gwdgz + ".png");
//int pictureIdx = myWorkbook.AddPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG);
// Create the drawing patriarch. This is the top level container for all shapes.
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
//add a picture
//dx1：起始单元格的x偏移量，如例子中的255表示直线起始位置距A1单元格左侧的距离；
//dy1：起始单元格的y偏移量，如例子中的125表示直线起始位置距A1单元格上侧的距离；
//dx2：终止单元格的x偏移量，如例子中的1023表示直线起始位置距C3单元格左侧的距离；
//dy2：终止单元格的y偏移量，如例子中的150表示直线起始位置距C3单元格上侧的距离；
//col1：起始单元格列序号，从0开始计算；
//row1：起始单元格行序号，从0开始计算，如例子中col1=0,row1=0就表示起始单元格为A1；
//col2：终止单元格列序号，从0开始计算；
//row2：终止单元格行序号，从0开始计算，如例子中col2=2,row2=2就表示起始单元格为C3；
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 13, 0, 14, 4);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//anchor = new HSSFClientAnchor(0, 0, 0, 0, 13, 11, 15, 14);
//HSSFPicture pict1 = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//pict.Resize();
}
#endregion
#region 放入问题表格边框
for (int i = 23; i < rownum - 1; i++)
{
for (int j = 0; j <= 13; j++)
{
sheet.GetRow(i).GetCell(j).CellStyle = style;
if (j == 0)
{
sheet.GetRow(i).GetCell(j).CellStyle.SetFont(fontSmall);
}
}
}
#endregion
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(savePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}
}
catch (Exception)
{
return(false);
}
return(true);
}
/// <summary>
/// 导出Excel
/// </summary>
public void OutPutExcel(List <CostReportInfo> list)
{
var workbook = new HSSFWorkbook();
var ms = new MemoryStream();
var sheet = new HSSFSheet[1];// 增加sheet。
#region Excel样式
//标题样式styletitle
HSSFFont fonttitle = workbook.CreateFont();
fonttitle.FontHeightInPoints = 10;
fonttitle.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
HSSFCellStyle styletitle = workbook.CreateCellStyle();
styletitle.BorderBottom = HSSFCellStyle.BORDER_THIN;
styletitle.BorderLeft = HSSFCellStyle.BORDER_THIN;
styletitle.BorderRight = HSSFCellStyle.BORDER_THIN;
styletitle.BorderTop = HSSFCellStyle.BORDER_THIN;
styletitle.SetFont(fonttitle);
//内容字体styleContent
HSSFFont fontcontent = workbook.CreateFont();
fontcontent.FontHeightInPoints = 9;
fontcontent.Color = HSSFColor.BLACK.index;
HSSFCellStyle styleContent = workbook.CreateCellStyle();
styleContent.BorderBottom = HSSFCellStyle.BORDER_THIN;
styleContent.BorderLeft = HSSFCellStyle.BORDER_THIN;
styleContent.BorderRight = HSSFCellStyle.BORDER_THIN;
styleContent.BorderTop = HSSFCellStyle.BORDER_THIN;
styleContent.SetFont(fontcontent);
#endregion
#region 将值插入sheet
sheet[0] = workbook.CreateSheet("押金回收列表");//添加sheet名
sheet[0].DefaultColumnWidth = 40;
sheet[0].DefaultRowHeight = 20;
HSSFRow rowtitle = sheet[0].CreateRow(0);
HSSFCell celltitie = rowtitle.CreateCell(0);
const string SHEET_TITLE = "押金回收列表";
celltitie.SetCellValue(SHEET_TITLE);
HSSFCellStyle style = workbook.CreateCellStyle();
style.Alignment = HSSFCellStyle.ALIGN_CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Color = HSSFColor.BLACK.index;
font.Boldweight = 2;
style.SetFont(font);
celltitie.CellStyle = style;
sheet[0].AddMergedRegion(new Region(0, 0, 0, 2));
#region //列名
HSSFRow rowTitle = sheet[0].CreateRow(3);
HSSFCell cell1 = rowTitle.CreateCell(0);
HSSFCell cell2 = rowTitle.CreateCell(1);
HSSFCell cell3 = rowTitle.CreateCell(2);
HSSFCell cell4 = rowTitle.CreateCell(3);
HSSFCell cell5 = rowTitle.CreateCell(4);
HSSFCell cell6 = rowTitle.CreateCell(5);
HSSFCell cell7 = rowTitle.CreateCell(6);
HSSFCell cell8 = rowTitle.CreateCell(7);
HSSFCell cell9 = rowTitle.CreateCell(8);
HSSFCell cell10 = rowTitle.CreateCell(9);
HSSFCell cell11 = rowTitle.CreateCell(10);
cell1.SetCellValue("公司");
cell2.SetCellValue("账户");
cell3.SetCellValue("申请人");
cell4.SetCellValue("预借款单据号");
cell5.SetCellValue("付款金额");
cell6.SetCellValue("付款时间");
cell7.SetCellValue("回收单据号");
cell8.SetCellValue("回收金额");
cell9.SetCellValue("回收时间");
cell10.SetCellValue("备注说明");
cell11.SetCellValue("状态");
cell1.CellStyle = styletitle;
cell2.CellStyle = styletitle;
cell3.CellStyle = styletitle;
cell4.CellStyle = styletitle;
cell5.CellStyle = styletitle;
cell6.CellStyle = styletitle;
cell7.CellStyle = styletitle;
cell8.CellStyle = styletitle;
cell9.CellStyle = styletitle;
cell10.CellStyle = styletitle;
cell11.CellStyle = styletitle;
cell1.CellStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
#endregion
#region //内容
int row = 4;
if (list.Count > 0)
{
foreach (CostReportInfo info in list)
{
var depositRecoveryList = _costReportDepositRecovery.GetDepositRecoveryList(info.ReportId);
if (depositRecoveryList.Count > 0)
{
foreach (var depositRecovery in depositRecoveryList)
{
HSSFRow rowt = sheet[0].CreateRow(row);
HSSFCell c1 = rowt.CreateCell(0);
HSSFCell c2 = rowt.CreateCell(1);
HSSFCell c3 = rowt.CreateCell(2);
HSSFCell c4 = rowt.CreateCell(3);
HSSFCell c5 = rowt.CreateCell(4);
HSSFCell c6 = rowt.CreateCell(5);
HSSFCell c7 = rowt.CreateCell(6);
HSSFCell c8 = rowt.CreateCell(7);
HSSFCell c9 = rowt.CreateCell(8);
HSSFCell c10 = rowt.CreateCell(9);
HSSFCell c11 = rowt.CreateCell(10);
c1.SetCellValue(CacheCollection.Filiale.GetFilialeNameAndFilialeId(info.PayBankAccountId).Split(',')[0]);
c2.SetCellValue(info.BankAccount);
c3.SetCellValue(new PersonnelManager().GetName(info.ReportPersonnelId));
c4.SetCellValue(info.ReportNo);
c5.SetCellValue(info.RealityCost.ToString());
c6.SetCellValue(info.ExecuteDate.ToString("yyyy-MM-dd") == "1900-01-01" ? "" : info.ExecuteDate.ToString("yyyy-MM-dd"));
c7.SetCellValue(depositRecovery.ReportNo);
c8.SetCellValue(depositRecovery.RecoveryCost.ToString());
c9.SetCellValue(depositRecovery.RecoveryDate.ToString("yyyy-MM-dd"));
c10.SetCellValue(depositRecovery.RecoveryRemarks);
c11.SetCellValue(ddl_State.SelectedItem.ToString());
c1.CellStyle = styleContent;
c2.CellStyle = styleContent;
c3.CellStyle = styleContent;
c4.CellStyle = styleContent;
c5.CellStyle = styleContent;
c6.CellStyle = styleContent;
c7.CellStyle = styleContent;
c8.CellStyle = styleContent;
c9.CellStyle = styleContent;
c10.CellStyle = styleContent;
c11.CellStyle = styleContent;
c1.CellStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
row++;
}
}
else
{
HSSFRow rowt = sheet[0].CreateRow(row);
HSSFCell c1 = rowt.CreateCell(0);
HSSFCell c2 = rowt.CreateCell(1);
HSSFCell c3 = rowt.CreateCell(2);
HSSFCell c4 = rowt.CreateCell(3);
HSSFCell c5 = rowt.CreateCell(4);
HSSFCell c6 = rowt.CreateCell(5);
HSSFCell c7 = rowt.CreateCell(6);
HSSFCell c8 = rowt.CreateCell(7);
HSSFCell c9 = rowt.CreateCell(8);
HSSFCell c10 = rowt.CreateCell(9);
HSSFCell c11 = rowt.CreateCell(10);
c1.SetCellValue(CacheCollection.Filiale.GetFilialeNameAndFilialeId(info.PayBankAccountId).Split(',')[0]);
c2.SetCellValue(info.BankAccount);
c3.SetCellValue(new PersonnelManager().GetName(info.ReportPersonnelId));
c4.SetCellValue(info.ReportNo);
c5.SetCellValue(info.RealityCost.ToString());
c6.SetCellValue(info.ExecuteDate.ToString("yyyy-MM-dd") == "1900-01-01" ? "" : info.ExecuteDate.ToString("yyyy-MM-dd"));
c7.SetCellValue("");
c8.SetCellValue("");
c9.SetCellValue("");
c10.SetCellValue("");
c11.SetCellValue(ddl_State.SelectedItem.ToString());
c1.CellStyle = styleContent;
c2.CellStyle = styleContent;
c3.CellStyle = styleContent;
c4.CellStyle = styleContent;
c5.CellStyle = styleContent;
c6.CellStyle = styleContent;
c7.CellStyle = styleContent;
c8.CellStyle = styleContent;
c9.CellStyle = styleContent;
c10.CellStyle = styleContent;
c11.CellStyle = styleContent;
c1.CellStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
row++;
}
}
}
else
{
HSSFRow rtotal = sheet[0].CreateRow(row);
HSSFCell t0 = rtotal.CreateCell(0);
t0.SetCellValue("无数据显示");
}
sheet[0].DisplayGridlines = false;
#endregion
#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
#endregion
}
private void BalancesMarketingReport(bool bZOV)
{
int pos = 0;
string SheetName = "Маркетинг";
string ConnectionString = ConnectionStrings.MarketingOrdersConnectionString;
#region Create fonts and styles
HSSFFont HeaderF = hssfworkbook.CreateFont();
HeaderF.FontHeightInPoints = 9;
HeaderF.Boldweight = 9 * 256;
HeaderF.FontName = "Calibri";
HSSFFont SimpleF = hssfworkbook.CreateFont();
SimpleF.FontHeightInPoints = 10;
SimpleF.FontName = "Calibri";
HSSFCellStyle SimpleCS = hssfworkbook.CreateCellStyle();
SimpleCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SimpleCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SimpleCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SimpleCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SimpleCS.TopBorderColor = HSSFColor.BLACK.index;
SimpleCS.SetFont(SimpleF);
HSSFCellStyle CountCS1 = hssfworkbook.CreateCellStyle();
CountCS1.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
CountCS1.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountCS1.BottomBorderColor = HSSFColor.BLACK.index;
CountCS1.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountCS1.LeftBorderColor = HSSFColor.BLACK.index;
CountCS1.BorderRight = HSSFCellStyle.BORDER_THIN;
CountCS1.RightBorderColor = HSSFColor.BLACK.index;
CountCS1.BorderTop = HSSFCellStyle.BORDER_THIN;
CountCS1.TopBorderColor = HSSFColor.BLACK.index;
CountCS1.SetFont(SimpleF);
HSSFCellStyle CountCS2 = hssfworkbook.CreateCellStyle();
CountCS2.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.0000");
CountCS2.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountCS2.BottomBorderColor = HSSFColor.BLACK.index;
CountCS2.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountCS2.LeftBorderColor = HSSFColor.BLACK.index;
CountCS2.BorderRight = HSSFCellStyle.BORDER_THIN;
CountCS2.RightBorderColor = HSSFColor.BLACK.index;
CountCS2.BorderTop = HSSFCellStyle.BORDER_THIN;
CountCS2.TopBorderColor = HSSFColor.BLACK.index;
CountCS2.SetFont(SimpleF);
HSSFCellStyle CountCS3 = hssfworkbook.CreateCellStyle();
CountCS3.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
CountCS3.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountCS3.BottomBorderColor = HSSFColor.BLACK.index;
CountCS3.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountCS3.LeftBorderColor = HSSFColor.BLACK.index;
CountCS3.BorderRight = HSSFCellStyle.BORDER_THIN;
CountCS3.RightBorderColor = HSSFColor.BLACK.index;
CountCS3.BorderTop = HSSFCellStyle.BORDER_THIN;
CountCS3.TopBorderColor = HSSFColor.BLACK.index;
CountCS3.SetFont(SimpleF);
HSSFCellStyle SimpleHeaderCS = hssfworkbook.CreateCellStyle();
SimpleHeaderCS.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderLeft = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderRight = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderTop = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.TopBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.SetFont(HeaderF);
#endregion Create fonts and styles
HSSFSheet sheet1 = hssfworkbook.CreateSheet(SheetName);
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
sheet1.SetColumnWidth(0, 20 * 256);
sheet1.SetColumnWidth(1, 30 * 256);
sheet1.SetColumnWidth(2, 20 * 256);
sheet1.SetColumnWidth(3, 20 * 256);
HSSFCell Cell1;
string SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina,
SUM(dbo.PackageDetails.Count) AS Expr2
FROM dbo.PackageDetails INNER JOIN
dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND dbo.DecorOrders.Length = - 1 AND dbo.DecorOrders.Height = - 1 AND
dbo.DecorOrders.Width = - 1 INNER JOIN
dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN
infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN
infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID
GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName
ORDER BY ProductName, Decor, Color, Patina";
using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString))
{
BalancesDT.Dispose();
BalancesDT = new DataTable();
DA.Fill(BalancesDT);
}
if (BalancesDT.Rows.Count > 0)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < BalancesDT.Rows.Count; i++)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"]));
Cell1.CellStyle = CountCS3;
pos++;
}
pos += 2;
}
SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina, dbo.DecorOrders.Length,
SUM(dbo.PackageDetails.Count) AS Expr2 FROM dbo.PackageDetails INNER JOIN
dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND
dbo.DecorOrders.Length <> - 1 AND dbo.DecorOrders.Height = - 1 AND dbo.DecorOrders.Width = - 1 INNER JOIN
dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN
infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN
infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID
GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName, dbo.DecorOrders.Length
ORDER BY ProductName, Decor, Color, Patina, dbo.DecorOrders.Length";
using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString))
{
BalancesDT.Dispose();
BalancesDT = new DataTable();
DA.Fill(BalancesDT);
}
if (BalancesDT.Rows.Count > 0)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue("Длина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < BalancesDT.Rows.Count; i++)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Length"]));
Cell1.CellStyle = CountCS3;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"]));
Cell1.CellStyle = CountCS3;
pos++;
}
pos += 2;
}
SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina,
SUM(CAST(dbo.DecorOrders.Length * dbo.DecorOrders.Width * dbo.PackageDetails.Count AS decimal(10,2))) AS Expr1, SUM(dbo.PackageDetails.Count) AS Expr2 FROM dbo.PackageDetails INNER JOIN
dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND dbo.DecorOrders.Length <> - 1 AND dbo.DecorOrders.Height = - 1 AND
dbo.DecorOrders.Width <> - 1 INNER JOIN
dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN
infiniu2_catalog.dbo.DecorConfig ON dbo.DecorOrders.DecorConfigID = infiniu2_catalog.dbo.DecorConfig.DecorConfigID AND infiniu2_catalog.dbo.DecorConfig.MeasureID = 2 INNER JOIN
infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN
infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID
GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName
ORDER BY ProductName, Decor, Color, Patina";
using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString))
{
BalancesDT.Dispose();
BalancesDT = new DataTable();
DA.Fill(BalancesDT);
foreach (DataRow item in BalancesDT.Rows)
{
item["Expr1"] = Convert.ToDecimal(item["Expr1"]) / 1000;
}
}
if (BalancesDT.Rows.Count > 0)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue("м.п.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < BalancesDT.Rows.Count; i++)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue(Convert.ToDouble(BalancesDT.Rows[i]["Expr1"]));
Cell1.CellStyle = CountCS1;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"]));
Cell1.CellStyle = CountCS3;
pos++;
}
pos += 2;
}
SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina,
SUM(CAST(dbo.DecorOrders.Length * dbo.DecorOrders.Width * dbo.PackageDetails.Count AS decimal(12,4))) AS Expr1, SUM(dbo.PackageDetails.Count) AS Expr2 FROM dbo.PackageDetails INNER JOIN
dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND dbo.DecorOrders.Length <> - 1 AND dbo.DecorOrders.Height = - 1 AND
dbo.DecorOrders.Width <> - 1 INNER JOIN
dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN
infiniu2_catalog.dbo.DecorConfig ON dbo.DecorOrders.DecorConfigID = infiniu2_catalog.dbo.DecorConfig.DecorConfigID AND infiniu2_catalog.dbo.DecorConfig.MeasureID = 1 INNER JOIN
infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN
infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID
GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName
ORDER BY ProductName, Decor, Color, Patina";
using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString))
{
BalancesDT.Dispose();
BalancesDT = new DataTable();
DA.Fill(BalancesDT);
foreach (DataRow item in BalancesDT.Rows)
{
item["Expr1"] = Convert.ToDecimal(item["Expr1"]) / 1000000;
}
}
if (BalancesDT.Rows.Count > 0)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue("м.кв.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < BalancesDT.Rows.Count; i++)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue(Convert.ToDouble(BalancesDT.Rows[i]["Expr1"]));
Cell1.CellStyle = CountCS2;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"]));
Cell1.CellStyle = CountCS3;
pos++;
}
}
SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina,
SUM(CAST(dbo.DecorOrders.Height * dbo.DecorOrders.Width * dbo.PackageDetails.Count AS decimal(12,4))) AS Expr1, SUM(dbo.PackageDetails.Count) AS Expr2 FROM dbo.PackageDetails INNER JOIN
dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND
dbo.DecorOrders.Length = - 1 AND dbo.DecorOrders.Height <> - 1 AND dbo.DecorOrders.Width <> - 1 INNER JOIN
dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN
infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN
infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID
GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName
ORDER BY ProductName, Decor, Color, Patina";
using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString))
{
BalancesDT.Dispose();
BalancesDT = new DataTable();
DA.Fill(BalancesDT);
foreach (DataRow item in BalancesDT.Rows)
{
item["Expr1"] = Convert.ToDecimal(item["Expr1"]) / 1000000;
}
}
if (BalancesDT.Rows.Count > 0)
{
for (int i = 0; i < BalancesDT.Rows.Count; i++)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue(Convert.ToDouble(BalancesDT.Rows[i]["Expr1"]));
Cell1.CellStyle = CountCS2;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"]));
Cell1.CellStyle = CountCS3;
pos++;
}
pos += 1;
}
}
private void ExportExcel()
{
Response.ContentType = "application/vnd.ms-excel";
string datetime = DateTime.Now.ToString().Replace("/", "").Replace(" ", "").Replace(":", "");
string projectName = "ALL";
projectName = ddlProject.SelectedValue == "0" ? projectName : ddlProject.SelectedItem.Text;
string ticketType = "ALL";
ticketType = ddlTicketType.SelectedValue == "-1" ? ticketType : ddlTicketType.SelectedItem.Text;
string ticketStatu = "ALL";
ticketStatu = ddlStatus.SelectedValue == "-1" ? ticketStatu : ddlStatus.SelectedItem.Text;
string filename = string.Empty;
if (projectName == "ALL" && ticketType == "ALL" && ticketStatu == "ALL")
{
filename = string.Format("ALL_{0}.xls", datetime);
}
else
{
projectName = projectName == "ALL" ? "AllProject" : projectName;
ticketType = ticketType == "ALL" ? "ALLType" : ticketType;
ticketStatu = ticketStatu == "ALL" ? "ALLStatus" : ticketStatu;
filename = string.Format("{0}_{1}_{2}.xls", projectName, ticketStatu, ticketType);
}
Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
//Set Sheet ColumnWidth
sheet.SetColumnWidth(0, 30 * 256);
sheet.SetColumnWidth(2, 50 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 20 * 256);
sheet.SetColumnWidth(6, 20 * 256);
sheet.SetColumnWidth(7, 20 * 256);
sheet.SetColumnWidth(8, 100 * 256);
sheet.DisplayGridlines = false;
HSSFRow row_head = (HSSFRow)sheet.CreateRow(0);
row_head.HeightInPoints = 20;
row_head.CreateCell((short)0).SetCellValue("Project");
row_head.CreateCell((short)1).SetCellValue("Type");
row_head.CreateCell((short)2).SetCellValue("Ticket Code / Ticket Title");
row_head.CreateCell((short)3).SetCellValue("Created Date");
row_head.CreateCell((short)4).SetCellValue("Updated Date");
row_head.CreateCell((short)5).SetCellValue("Status");
row_head.CreateCell((short)6).SetCellValue("Priority");
row_head.CreateCell((short)7).SetCellValue("Created By");
row_head.CreateCell((short)8).SetCellValue("Description");
HSSFCellStyle hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//hStyle.BorderTop = (NPOI.SS.UserModel.CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
hStyle.BorderRight = (NPOI.SS.UserModel.CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
//hStyle.BorderLeft = (NPOI.SS.UserModel.CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
hStyle.BorderBottom = (NPOI.SS.UserModel.CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
//Set head row background color
hStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.TEAL.index;
hStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SQUARES;
hStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.TEAL.index;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.Color = NPOI.HSSF.Util.HSSFColor.WHITE.index;
font.FontName = "Verdana";
font.FontHeightInPoints = 12;
hStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
hStyle.SetFont(font);
foreach (var c in row_head.Cells)
{
c.CellStyle = hStyle;
}
SearchTicketsResponse response = GetResponse(false);
int i = 1;
foreach (ExpandTicketsEntity ticket in response.ResultList)
{
HSSFRow rownumber = (HSSFRow)sheet.CreateRow(i);
rownumber.CreateCell((short)0).SetCellValue(ticket.ProjectTitle);
rownumber.CreateCell((short)1).SetCellValue(ticket.TicketType.ToString());
rownumber.CreateCell((short)2).SetCellValue(ticket.TicketID + " " + ticket.Title);
rownumber.CreateCell((short)3).SetCellValue(string.Format("{0:M/d/yyyy}", ticket.CreatedOn));
rownumber.CreateCell((short)4).SetCellValue(string.Format("{0:M/d/yyyy}", ticket.ModifiedOn));
rownumber.CreateCell((short)5).SetCellValue(
GetClientStatusNameBySatisfyStatus((int)ticket.Status, ticket.TicketID, false));
rownumber.CreateCell((short)6).SetCellValue(ticket.Priority.ToString());
rownumber.CreateCell((short)7).SetCellValue(string.Format("{0} {1}", ticket.FirstName, ticket.LastName));
rownumber.CreateCell((short)8).SetCellValue(ticket.FullDescription);
HSSFCellStyle rStyle = (HSSFCellStyle)workbook.CreateCellStyle();
rStyle.BorderRight = (NPOI.SS.UserModel.CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
rStyle.BorderBottom = (NPOI.SS.UserModel.CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
rStyle.WrapText = true;
rStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
rStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
foreach (var c in rownumber.Cells)
{
c.CellStyle = rStyle;
}
i++;
}
workbook.Write(Response.OutputStream);
}
/// <summary>导出EXCEL
/// </summary>
public void OutPutExcel(Dictionary <GoodsBaseModel, List <GoodsBaseModel> > dic)
{
var workbook = new HSSFWorkbook();
var ms = new MemoryStream();
var sheet = new HSSFSheet[1]; // 增加sheet。
#region Excel样式
//标题样式styletitle
HSSFFont fonttitle = workbook.CreateFont();
fonttitle.FontHeightInPoints = 12;
fonttitle.Color = HSSFColor.RED.index;
HSSFCellStyle styletitle = workbook.CreateCellStyle();
styletitle.BorderBottom = HSSFCellStyle.BORDER_THIN;
styletitle.BorderLeft = HSSFCellStyle.BORDER_THIN;
styletitle.BorderRight = HSSFCellStyle.BORDER_THIN;
styletitle.BorderTop = HSSFCellStyle.BORDER_THIN;
styletitle.SetFont(fonttitle);
//内容字体styleContent
HSSFFont fontcontent = workbook.CreateFont();
fontcontent.FontHeightInPoints = 9;
fontcontent.Color = HSSFColor.BLACK.index;
HSSFCellStyle styleContent = workbook.CreateCellStyle();
styleContent.BorderBottom = HSSFCellStyle.BORDER_THIN;
styleContent.BorderLeft = HSSFCellStyle.BORDER_THIN;
styleContent.BorderRight = HSSFCellStyle.BORDER_THIN;
styleContent.BorderTop = HSSFCellStyle.BORDER_THIN;
styleContent.SetFont(fontcontent);
//总计 styletotal
HSSFFont fonttotal = workbook.CreateFont();
fonttotal.FontHeightInPoints = 12;
fonttotal.Color = HSSFColor.RED.index;
fonttotal.Boldweight = 2;
HSSFCellStyle styletotal = workbook.CreateCellStyle();
styletotal.SetFont(fonttotal);
#endregion
#region [模板及sheet名字]
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);
HSSFCell celltitie = rowtitle.CreateCell(0);
celltitie.SetCellValue("框架赠品绑定" + DateTime.Now.ToString("yyyy-MM-dd"));
HSSFCellStyle style = workbook.CreateCellStyle();
style.Alignment = HSSFCellStyle.ALIGN_CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Color = HSSFColor.BLACK.index;
font.Boldweight = 2;
style.SetFont(font);
celltitie.CellStyle = style;
sheet[0].AddMergedRegion(new Region(0, 0, 0, 7));
#endregion
#region [列名]
HSSFRow rowtitles = sheet[0].CreateRow(1);
HSSFCell ct1 = rowtitles.CreateCell(0);
HSSFCell ct2 = rowtitles.CreateCell(1);
ct1.SetCellValue("商品名名称");
ct2.SetCellValue("赠品商品名称");
ct1.CellStyle = styletitle;
ct2.CellStyle = styletitle;
#endregion
int row = 2;
foreach (var item in dic.Keys)
{
HSSFRow rowt = sheet[0].CreateRow(row);
HSSFCell c1 = rowt.CreateCell(0);
HSSFCell c2 = rowt.CreateCell(1);
c1.SetCellValue(item.GoodsName); //商品名
var giftList = dic.FirstOrDefault(ent => ent.Key.GoodsId == item.GoodsId).Value;
StringBuilder giftGoodsName = new StringBuilder();
foreach (var goodsBaseModel in giftList)
{
giftGoodsName.Append(" [ ").Append(goodsBaseModel.GoodsName).Append(" ] ");
}
c2.SetCellValue(giftGoodsName.ToString()); //赠品商品
c1.CellStyle = styleContent;
c2.CellStyle = styleContent;
c1.CellStyle.Alignment = HSSFCellStyle.ALIGN_LEFT;
row++;
}
sheet[0].DisplayGridlines = false;
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();
}
private FileContentResult SendToExcel2(FormCollection coll)
{
DateTime firstDay = DateTime.Parse(coll["txtDateFrom"]);
DateTime lastDay = DateTime.Parse(coll["txtDateTo"]);
string User_CAI = coll["ddlUsers"];
ReportPackage rp = timedb.GetReportByUser(User_CAI, firstDay, lastDay);
// FileStream fs = new FileStream(Server.MapPath("~/Content/Template.xls"), FileMode.Open, FileAccess.Read);
// Getting the complete workbook...
HSSFWorkbook wb = new HSSFWorkbook();
// Getting the worksheet by its name...
HSSFSheet sheet = wb.CreateSheet(User_CAI + "_" + coll["txtDateFrom"] + "_" + coll["txtDateTo"]);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 30 * 256);
HSSFFont font1 = wb.CreateFont();
font1.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
font1.Color = HSSFColor.BLACK.index;
font1.FontHeightInPoints = 10;
HSSFFont font2 = wb.CreateFont();
font2.Color = HSSFColor.BLACK.index;
font2.FontHeightInPoints = 8;
HSSFFont sacofont = wb.CreateFont();
sacofont.Color = HSSFColor.WHITE.index;
sacofont.FontHeightInPoints = 8;
HSSFFont font2Bold = wb.CreateFont();
font2Bold = font2;
font2Bold.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
HSSFFont font_Red_Bold = wb.CreateFont();
font_Red_Bold.Color = HSSFColor.RED.index;
font_Red_Bold.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
font_Red_Bold.FontHeightInPoints = 10;
HSSFCellStyle globalstyle = wb.CreateCellStyle();
globalstyle.BorderBottom = HSSFCellStyle.BORDER_THIN;
globalstyle.BottomBorderColor = HSSFColor.BLACK.index;
globalstyle.BorderLeft = HSSFCellStyle.BORDER_THIN;
globalstyle.LeftBorderColor = HSSFColor.BLACK.index;
globalstyle.BorderRight = HSSFCellStyle.BORDER_THIN;
globalstyle.RightBorderColor = HSSFColor.BLACK.index;
globalstyle.BorderTop = HSSFCellStyle.BORDER_THIN;
globalstyle.TopBorderColor = HSSFColor.BLACK.index;
HSSFCellStyle headerstyle = wb.CreateCellStyle();
headerstyle.CloneStyleFrom(globalstyle);
headerstyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
headerstyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
headerstyle.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
headerstyle.SetFont(font1);
HSSFCellStyle dayStyle = wb.CreateCellStyle();
dayStyle.CloneStyleFrom(headerstyle);
dayStyle.SetFont(font1);
HSSFCellStyle datastyle = wb.CreateCellStyle();
datastyle.CloneStyleFrom(globalstyle);
datastyle.SetFont(font2);
HSSFCellStyle footerStyle = wb.CreateCellStyle();
footerStyle.CloneStyleFrom(headerstyle);
footerStyle.Alignment = HSSFCellStyle.ALIGN_RIGHT;
footerStyle.SetFont(font2);
HSSFCellStyle ErrorStyle = wb.CreateCellStyle();
ErrorStyle.CloneStyleFrom(headerstyle);
ErrorStyle.Alignment = HSSFCellStyle.ALIGN_RIGHT;
ErrorStyle.SetFont(font_Red_Bold);
HSSFCellStyle SacofooterStyle = wb.CreateCellStyle();
SacofooterStyle.CloneStyleFrom(globalstyle);
SacofooterStyle.Alignment = HSSFCellStyle.ALIGN_RIGHT;
SacofooterStyle.FillForegroundColor = HSSFColor.LIGHT_BLUE.index;
SacofooterStyle.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
SacofooterStyle.SetFont(sacofont);
//Header
HSSFRow row = sheet.CreateRow(0);
row.CreateCell(0).CellStyle = headerstyle;
row.GetCell(0).SetCellValue("Status");
row.CreateCell(1).CellStyle = headerstyle;
row.GetCell(1).SetCellValue("Prj No.");
row.CreateCell(2).CellStyle = headerstyle;
row.GetCell(2).SetCellValue("SAP");
row.CreateCell(3).CellStyle = headerstyle;
row.GetCell(3).SetCellValue("Cost Code");
row.CreateCell(4).CellStyle = headerstyle;
row.GetCell(4).SetCellValue("Project Name");
int colcount = 5;
for (DateTime dt = rp.DateFrom; dt < rp.DateTo.AddDays(1); dt = dt.AddDays(1))
{
row.CreateCell(colcount).CellStyle = dayStyle;
row.GetCell(colcount).CellStyle.WrapText = true;
row.GetCell(colcount).SetCellValue(new DateTime(dt.Year, dt.Month, dt.Day).ToString("ddd") + "\n" + new DateTime(dt.Year, dt.Month, dt.Day).ToString("dd") + "\n" + new DateTime(dt.Year, dt.Month, dt.Day).ToString("MMM"));
colcount++;
}
row.CreateCell(colcount).CellStyle = headerstyle;
row.GetCell(colcount).SetCellValue("Total");
//Details
int rowcount = 1;
foreach (var ts in rp.TimeSheets)
{
sheet.CreateRow(rowcount);
sheet.GetRow(rowcount).CreateCell(0).CellStyle = datastyle;
sheet.GetRow(rowcount).GetCell(0).SetCellValue(ts.Status);
sheet.GetRow(rowcount).CreateCell(1).CellStyle = datastyle;
sheet.GetRow(rowcount).GetCell(1).SetCellValue(ts.Project_Number.ToString());
sheet.GetRow(rowcount).CreateCell(2).CellStyle = datastyle;
sheet.GetRow(rowcount).GetCell(2).SetCellValue(ts.SAP);
sheet.GetRow(rowcount).CreateCell(3).CellStyle = datastyle;
sheet.GetRow(rowcount).GetCell(3).SetCellValue(ts.WBS);
sheet.GetRow(rowcount).CreateCell(4).CellStyle = datastyle;
sheet.GetRow(rowcount).GetCell(4).CellStyle.WrapText = true;
sheet.GetRow(rowcount).GetCell(4).SetCellValue(ts.Project_Name);
colcount = 5;
foreach (var d in ts.Days)
{
sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = datastyle;
if (d.HoursWorked.HasValue)
{
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue((double)d.HoursWorked.Value);
}
else
{
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(string.Empty);
}
colcount++;
}
sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = datastyle;
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue((double)ts.Total);
rowcount++;
}
//Footer
//Insert blank spaces
sheet.CreateRow(rowcount);
sheet.GetRow(rowcount).CreateCell(0).CellStyle = footerStyle;
sheet.GetRow(rowcount).CreateCell(1).CellStyle = footerStyle;
sheet.GetRow(rowcount).CreateCell(2).CellStyle = footerStyle;
sheet.GetRow(rowcount).CreateCell(3).CellStyle = footerStyle;
sheet.GetRow(rowcount).CreateCell(4).CellStyle = footerStyle;
colcount = 5; //Start at column 5 inserting values
int loopindex = 0;
foreach (var d in rp.DayTotals)
{
sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = footerStyle;
if (d.HoursWorked.HasValue && (decimal)d.HoursWorked.Value > 0)
{
loopindex = d.Number - 1;
if (rp.DayTotals[loopindex].HoursWorked.Value > rp.DaySacoTotals[loopindex].HoursWorked.Value)
{
sheet.GetRow(rowcount).GetCell(colcount).CellStyle = ErrorStyle;
}
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(((decimal)d.HoursWorked.Value).ToString("#0.00"));
}
else
{
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(string.Empty);
}
colcount++;
}
sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = footerStyle;
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(((decimal)rp.GrandTotal).ToString("#0.00"));
rowcount++;
//SACO Footer
//Insert blank spaces
sheet.CreateRow(rowcount);
sheet.GetRow(rowcount).CreateCell(0).CellStyle = SacofooterStyle;
sheet.GetRow(rowcount).GetCell(0).SetCellValue("Saco");
sheet.GetRow(rowcount).CreateCell(1).CellStyle = SacofooterStyle;
sheet.GetRow(rowcount).CreateCell(2).CellStyle = SacofooterStyle;
sheet.GetRow(rowcount).CreateCell(3).CellStyle = SacofooterStyle;
sheet.GetRow(rowcount).CreateCell(4).CellStyle = SacofooterStyle;
colcount = 5; //Start at column 5 inserting values
foreach (var d in rp.DaySacoTotals)
{
sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = SacofooterStyle;
if (d.HoursWorked.HasValue && (decimal)d.HoursWorked.Value > 0)
{
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(((decimal)d.HoursWorked.Value).ToString("#0.00"));
}
else
{
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(string.Empty);
}
colcount++;
}
sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = SacofooterStyle;
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(((decimal)rp.DaySacoTotals.Sum(t => t.HoursWorked)).ToString("#0.00"));
MemoryStream ms = new MemoryStream();
// Writing the workbook content to the FileStream...
wb.Write(ms);
// Sending the server processed data back to the user computer...
return(File(ms.ToArray(), "application/vnd.ms-excel", coll["ddlUsers"] + "_" + coll["txtDateFrom"] + "_" + coll["txtDateTo"] + "_TimeSheetReport.xls"));
}
/// <summary>
/// Take a cell, and apply a font to it
/// </summary>
/// <param name="cell">the cell to Set the alignment for</param>
/// <param name="workbook">The workbook that is being worked with.</param>
/// <param name="font">The HSSFFont that you want to Set...</param>
public static void SetFont(ICell cell, HSSFWorkbook workbook, HSSFFont font)
{
SetCellStyleProperty(cell, workbook, FONT, font);
}
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string title)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Yeli";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "yelioa"; //填加xls文件作者信息
si.ApplicationName = title; //填加xls文件创建程序信息
si.LastAuthor = "yelioa"; //填加xls文件最后保存者信息
si.Comments = "说明信息"; //填加xls文件作者信息
si.Title = title; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
//dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//string[] chineseHeaders;
//if(strHeaderText=="部门积分信息")
//{
// chineseHeaders =new string[] { "姓名", "部门", "月度积分", "季度积分", "年度积分", "总积分" };
//}
//else
// chineseHeaders = new string[] {"序号", "编号", "提交日期", "审批日期", "财务审批日期",
// "提交人", "部门", "费用归属部门", "产品", "费用明细", "金额", "实报金额", "状态", "审批人", "抄送人", "备注", "审批意见", "审批结果"};
for (int i = 0; i < dtSource.Columns.Count; i++)
{
int colWidth = sheet.GetColumnWidth(i) * 2;
if (colWidth < 255 * 256)
{
sheet.SetColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
else
{
sheet.SetColumnWidth(i, 6000);
}
DataColumn column = dtSource.Columns[i];
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
//sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 255);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
#region 填充内容
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
if ("".Equals(drValue))
{
newCell.SetCellValue(drValue);
break;
}
DateTime date = Convert.ToDateTime(drValue);
drValue = date.ToString("yyyy-MM-dd HH:mm:ss");
//DateTime dateV;
//DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(drValue);
//newCell.SetCellType(HSSFCellType.FORMULA);
//newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();
return(ms);
}
}
