public void TestHSSFCellToStringWithDataFormat()
{
HSSFWorkbook wb = new HSSFWorkbook();
ICell cell = wb.CreateSheet("Sheet1").CreateRow(0).CreateCell(0);
cell.SetCellValue(new DateTime(2009, 8, 20));
NPOI.SS.UserModel.ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
cell.CellStyle = cellStyle;
Assert.AreEqual("8/20/09", cell.ToString());
NPOI.SS.UserModel.ICellStyle cellStyle2 = wb.CreateCellStyle();
IDataFormat format = wb.CreateDataFormat();
cellStyle2.DataFormat = format.GetFormat("YYYY-mm/dd");
cell.CellStyle = cellStyle2;
Assert.AreEqual("2009-08/20", cell.ToString());
}
public void TestCloneStyleDiffWB()
{
HSSFWorkbook wbOrig = new HSSFWorkbook();
IFont fnt = wbOrig.CreateFont();
fnt.FontName=("TestingFont");
Assert.AreEqual(5, wbOrig.NumberOfFonts);
IDataFormat fmt = wbOrig.CreateDataFormat();
fmt.GetFormat("MadeUpOne");
fmt.GetFormat("MadeUpTwo");
NPOI.SS.UserModel.ICellStyle orig = wbOrig.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat=(fmt.GetFormat("Test##"));
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(fnt,orig.GetFont(wbOrig));
Assert.AreEqual(fmt.GetFormat("Test##") , orig.DataFormat);
// Now a style on another workbook
HSSFWorkbook wbClone = new HSSFWorkbook();
Assert.AreEqual(4, wbClone.NumberOfFonts);
IDataFormat fmtClone = wbClone.CreateDataFormat();
NPOI.SS.UserModel.ICellStyle clone = wbClone.CreateCellStyle();
Assert.AreEqual(4, wbClone.NumberOfFonts);
Assert.AreNotEqual(HorizontalAlignment.Right,clone.Alignment);
Assert.AreNotEqual("TestingFont", clone.GetFont(wbClone).FontName);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual("TestingFont" ,clone.GetFont(wbClone).FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"),clone.DataFormat);
Assert.AreNotEqual(fmtClone.GetFormat("Test##") , fmt.GetFormat("Test##"));
Assert.AreEqual(5, wbClone.NumberOfFonts);
}
private void FillRows(HSSFWorkbook workbook, ISheet workSheet, List<Cell> cells, dynamic rows)
{
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
var currentRow = 1;
if (rows == null)
{
return;
}
foreach (var row in rows)
{
var workRow = workSheet.CreateRow(currentRow);
var currentCol = 0;
var type = row.GetType();
foreach (var cell in cells)
{
var workCell = workRow.CreateCell(currentCol);
SetCellValue(workbook, workCell, type, row, cell);
currentCol++;
}
currentRow++;
}
}
private static void SetCellValue(HSSFWorkbook workbook, ICell workCell, Type type, dynamic row, Cell cell)
{
var value = type.GetProperty(cell.Field).GetValue(row);
if (value == null)
{
return;
}
if (value is DateTime)
{
workCell.SetCellValue((DateTime)value);
}
else if (value is int)
{
workCell.SetCellValue((int)value);
}
else if (value is double)
{
workCell.SetCellValue((double)value);
}
else
{
workCell.SetCellValue(value.ToString());
}
if (!string.IsNullOrWhiteSpace(cell.Format))
{
var cellStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat(cell.Format);
workCell.CellStyle = cellStyle;
}
}
private void btnOutput_Click(object sender, EventArgs e)
{
List <MODEL.Classes> lists = cm.GetAllClass(false);
/*
* 1.创建出工作薄
* 2.为这个工作薄创建出工作表
* 3.为这个表创建出行
* 4.为这个行创建出每一列(单元格cell)--添加数据
* 5.文件的写入
*/
//创建一个工作薄
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
//创建一张工作表
ISheet sheet1 = workbook.CreateSheet("sh1");
// workbook.CreateSheet("Sh1");
///NPOI.HSSF. .SS .UserModel.HSSFSheet sheet1 = workbook.CreateSheet(); //.CreateSheet("sh1");
////workbook.CreateSheet("sh1"); // workbook.CreateSheet("sh1");
//需要导出的数据在集合中，每一个对象对应着后期表的一行数据
for (int i = 0; i < lists.Count; i++)
{
//创建行
IRow row = sheet1.CreateRow(i);
//创建第一个单元格
ICell cell1 = row.CreateCell(0);
cell1.SetCellValue(lists[i].CID);
//创建第2个单元格
ICell cell2 = row.CreateCell(1);
cell2.SetCellValue(lists[i].CName);
//创建第3个单元格
ICell cell3 = row.CreateCell(2);
cell3.SetCellValue(lists[i].CCount);
//创建第4个单元格
ICell cell4 = row.CreateCell(3);
cell4.SetCellValue(lists[i].CImg);
//创建第5个单元格
ICell cell5 = row.CreateCell(4);
cell5.SetCellValue(lists[i].CIsDel);
//创建第6个单元格
ICell cell6 = row.CreateCell(5);
//cell6.SetCellValue(lists[i].CAddTime.ToString("yyyy年MM月dd日")); //日期值会被当成double
cell6.SetCellValue(lists[i].CAddTime);
//如何修改日期类型的格式
ICellStyle cs = workbook.CreateCellStyle();
IDataFormat df = workbook.CreateDataFormat();
cs.DataFormat = df.GetFormat("yyyy年MM月dd日");
cell6.CellStyle = cs;
}
using (FileStream fs = new FileStream("aa.xls", FileMode.Create))
{
workbook.Write(fs);
MessageBox.Show("ok");
}
}
public void SetUp()
{
wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("new sheet");
style = wb.CreateCellStyle();
IDataFormat fmt = wb.CreateDataFormat();
style.DataFormat=(fmt.GetFormat("hh:mm:ss"));
cell11 = sheet.CreateRow(0).CreateCell(0);
form = new DataFormatter();
Evaluator = new HSSFFormulaEvaluator(wb);
}
/// <summary>
/// 设置样式
/// </summary>
private static void SetExcelValue(DataTable dt, string sheetname, NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ICellStyle style)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.ICell newCell = null;
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
else if (strColDataType.Equals("System.DateTime"))
{
DateTime dateV;
DateTime.TryParse(dt.Rows[i][j].ToString(), out dateV);
newCell = row2.CreateCell(j);
newCell.SetCellValue(dateV);
//格式化显示
HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)book.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-m-d");
newCell.CellStyle = cellStyle;
}
}
}
}
/// <summary>
/// 加载Excel模板样式(xls)
/// </summary>
/// <param name="file"></param>
private static void LoadExcelStyleForXLS(string file)
{
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XLS.HSSFWorkbook hssfworkbook = new XLS.HSSFWorkbook(fs);
ISheet s = hssfworkbook.GetSheetAt(0);
for (int i = s.FirstRowNum; i <= s.LastRowNum; i++)
{
ICell c0 = s.GetRow(i).GetCell(0);
ICell c1 = s.GetRow(i).GetCell(1);
CellStyles.Add(c0.StringCellValue, c1.CellStyle);
if (c0.StringCellValue == "DateTime")
{
IDataFormat format = hssfworkbook.CreateDataFormat();
CellStyles["DateTime"].DataFormat = format.GetFormat(c1.StringCellValue);
}
}
}
}
private void btnExportToExcel_Click(object sender, RoutedEventArgs e)
{
SaveFileDialog sdfExport = new SaveFileDialog();
sdfExport.Filter = "Excel文件|*.xls";
if (sdfExport.ShowDialog() != true)
{
return;
}
string filename = sdfExport.FileName;
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("员工数据");
IRow rowHeader = sheet.CreateRow(0);//表头行
rowHeader.CreateCell(0, CellType.STRING).SetCellValue("姓名");
rowHeader.CreateCell(1, CellType.STRING).SetCellValue("工号");
rowHeader.CreateCell(2, CellType.STRING).SetCellValue("入职日期");
//把查询结果导出到Excel
Employee[] employees = (Employee[])datagrid.ItemsSource;
for (int i = 0; i < employees.Length; i++)
{
Employee employee = employees[i];
IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0, CellType.STRING).SetCellValue(employee.Name);
row.CreateCell(1, CellType.STRING).SetCellValue(employee.Number);
ICellStyle styledate = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
//格式具体有哪些请看单元格右键中的格式，有说明
styledate.DataFormat = format.GetFormat("yyyy\"年\"m\"月\"d\"日\"");
ICell cellInDate = row.CreateCell(2, CellType.NUMERIC);
cellInDate.CellStyle = styledate;
cellInDate.SetCellValue(employee.InDate);
}
using (Stream stream = File.OpenWrite(filename))
{
workbook.Write(stream);
}
}
public XlsModel(string sheetName)
{
SheetName = sheetName;
// Create a new workbook and a sheet named "User Accounts"
_workbook = new HSSFWorkbook();
_sheet = _workbook.CreateSheet(SheetName);
var boldFont = _workbook.CreateFont();
boldFont.FontHeightInPoints = 10;
boldFont.FontName = "Arial";
boldFont.Boldweight = (short) FontBoldWeight.Bold;
_dateStyle = _workbook.CreateCellStyle();
_dateStyle.DataFormat = _workbook.CreateDataFormat().GetFormat("dd/mm/yyyy");
_nullStyle = _workbook.CreateCellStyle();
_nullStyle.FillForegroundColor = HSSFColor.Grey40Percent.Index;
_nullStyle.FillPattern = FillPattern.SolidForeground;
_headStyle = _workbook.CreateCellStyle();
_headStyle.SetFont(boldFont);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param Name="dtSource">DataTable数据源</param>
/// <param Name="strHeaderText">Excel表头文本（例如：车辆列表）</param>
public MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息";//填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
int j = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints =20;
font.Boldweight =800;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dcs.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 800;
font.FontName = "宋体";
headStyle.SetFont(font);
int index = 0;
foreach (string column in dcs)
{
headerRow.CreateCell(index).SetCellValue(column);
headerRow.GetCell(index).CellStyle = headStyle;
sheet.AutoSizeColumn(index);
//设置列宽
//sheet.SetColumnWidth(index, (arrColWidth[index] + 1) * 256);
//sheet.SetColumnWi
index++;
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int columnIndex = 0;
DistinctSupervisor(dtSource.Rows[j][6].ToString(), ListSupervisor);//去职称
for (int i = 0; i < dcs.Count; i++)
{
ICell newCell = dataRow.CreateCell(columnIndex);
columnIndex++;
if (i != dcs.Count - 1)
{
//注意这个在导出的时候加了“\t” 的目的就是避免导出的数据显示为科学计数法。可以放在每行的首尾。
switch (i)
{
case 0:
newCell.SetCellValue((j + 1).ToString() + "\t");
break;
case 1:
newCell.SetCellValue(dtSource.Rows[j][8].ToString() + "\t");
break;
case 2:
newCell.SetCellValue(dtSource.Rows[j][9].ToString() + "\t");
break;
case 3:
newCell.SetCellValue(dtSource.Rows[j][10].ToString() + "\t");
break;
case 4:
newCell.SetCellValue(dtSource.Rows[j][11].ToString() + "\t");
break;
case 5:
newCell.SetCellValue(dtSource.Rows[j][7].ToString() + "\t");
break;
case 6:
newCell.SetCellValue(dtSource.Rows[j][2].ToString() + "\t");
break;
case 7:
newCell.SetCellValue(dtSource.Rows[j][3].ToString() + "\t");
break;
case 8:
//听课时间
newCell.SetCellValue(
CalendarTools.getdata(Common.Year, Convert.ToInt32(dtSource.Rows[j][3]), Convert.ToInt32(dtSource.Rows[j][4]) - CalendarTools.weekdays(CalendarTools.CaculateWeekDay(Common.Year, Common.Month, Common.Day)), Common.Month, Common.Day).ToLongDateString()
+ " " + addseparator(Convert.ToInt32(dtSource.Rows[j][5])) + "节" + "\t");
break;
case 9:
newCell.SetCellValue(FormatSupervisor(ListSupervisor) + "\t");
break;
case 10:
newCell.SetCellValue(dtSource.Rows[j][12].ToString() + "\t");
break;
}
}
else
{
newCell.SetCellValue(" ");
}
}
#endregion
j++;
rowIndex++;
}
adjustcolum(sheet);//调整列宽
AddBorder(sheet,workbook);//加边框
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
}
public ExportEquipmentCategoryColumnTemplateCommand.Result Handle(ExportEquipmentCategoryColumnTemplateCommand command)
{
var category = _repository.Get <EquipmentCategory>(command.CategoryId);
var columns = category.Columns;
string fileName = $"{category.Name}设备分类模板-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // 文件名称
string urlPath = "Attachments/CategoryTemplate/" + fileName; // 文件下载的URL地址，供给前台下载
string filePath = HttpContext.Current.Server.MapPath("\\" + urlPath); // 文件路径
string directoryName = Path.GetDirectoryName(filePath);
if (!Directory.Exists(directoryName))
{
if (directoryName != null)
{
Directory.CreateDirectory(directoryName);
}
}
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow rowHeader = sheet1.CreateRow(0);
rowHeader.CreateCell(0).SetCellValue("ID"); // 表头列名
rowHeader.CreateCell(1).SetCellValue("设备分类"); // 表头列名
rowHeader.CreateCell(2).SetCellValue("生产厂商");
rowHeader.CreateCell(3).SetCellValue("批次");
rowHeader.CreateCell(4).SetCellValue("产品小类");
rowHeader.CreateCell(5).SetCellValue("产品名称");
rowHeader.CreateCell(6).SetCellValue("产品编码");
rowHeader.CreateCell(7).SetCellValue("规格型号");
rowHeader.CreateCell(8).SetCellValue("材质");
rowHeader.CreateCell(9).SetCellValue("技术人员");
rowHeader.CreateCell(10).SetCellValue("物资人员");
rowHeader.CreateCell(11).SetCellValue("领料人");
rowHeader.CreateCell(12).SetCellValue("出厂日期");
rowHeader.CreateCell(13).SetCellValue("检测人员");
rowHeader.CreateCell(14).SetCellValue("检测结果");
rowHeader.CreateCell(15).SetCellValue("产品执行标准");
rowHeader.CreateCell(16).SetCellValue("安装位置");
var dateIndexes = new List <int>();
for (int i = 0; i < columns.Count; i++)
{
var cellStartIndex = i + 17;
rowHeader.CreateCell(cellStartIndex).SetCellValue(columns[i].ColumnName); // 表头列名
if (columns[i].ColumnType == EquipmentCategoryColumnType.日期.ToString())
{
dateIndexes.Add(cellStartIndex);
}
}
IRow row1 = sheet1.CreateRow(1);
row1.CreateCell(0).SetCellValue(category.Id);
row1.CreateCell(1).SetCellValue(category.Name);
foreach (var dateIndex in dateIndexes)
{
var cell = row1.CreateCell(dateIndex);
//设置单元格格式
HSSFCellStyle style = (HSSFCellStyle)book.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)book.CreateDataFormat();
style.DataFormat = format.GetFormat("yyyy年mm月dd日");
cell.CellStyle = style;
}
// 4.生成文件
FileStream file = new FileStream(filePath, FileMode.Create);
book.Write(file);
file.Close();
return(new ExportEquipmentCategoryColumnTemplateCommand.Result
{
IsSucceed = true,
UrlPath = urlPath,
});
}
public void TestGetDataFormatUniqueIndex()
{
HSSFWorkbook wb = new HSSFWorkbook();
IDataFormat format = wb.CreateDataFormat();
short formatidx1 = format.GetFormat("YYYY-mm/dd");
short formatidx2 = format.GetFormat("YYYY-mm/dd");
Assert.AreEqual(formatidx1, formatidx2);
short formatidx3 = format.GetFormat("000000.000");
Assert.AreNotEqual(formatidx1, formatidx3);
}
public void ExportToExcel(int page, int size, int store, string mrf, string stockCode, string stockName, string status, string fd, string td, string enable)
{
// Get the data to report on
var requisitionMasters = _service.ListCondition(page, size, store, mrf, stockCode, stockName, status,
fd, td, enable);
var requisitionDetails = _service.ListConditionDetailExcel(page, size, store, mrf, stockCode, stockName, status,
fd, td, enable).OrderByDescending(i=>i.MRF);
// Create a new workbook
var workbook = new HSSFWorkbook();
#region Cell Styles
#region HeaderLabel Cell Style
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER;
headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerLabelCellStyle.SetFont(headerLabelFont);
#endregion
#region RightAligned Cell Style
var rightAlignedCellStyle = workbook.CreateCellStyle();
rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT;
#endregion
#region Currency Cell Style
var currencyCellStyle = workbook.CreateCellStyle();
currencyCellStyle.Alignment = HorizontalAlignment.RIGHT;
var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
currencyCellStyle.DataFormat = formatId;
#endregion
#region Detail Subtotal Style
var detailSubtotalCellStyle = workbook.CreateCellStyle();
detailSubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailSubtotalFont = workbook.CreateFont();
detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailSubtotalCellStyle.SetFont(detailSubtotalFont);
#endregion
#region Detail Currency Subtotal Style
var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle();
detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailCurrencySubtotalFont = workbook.CreateFont();
detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont);
formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
detailCurrencySubtotalCellStyle.DataFormat = formatId;
#endregion
#endregion
#region Requisition sheet
var sheet = workbook.CreateSheet("Requistion");
// Add header labels
var rowIndex = 0;
// Undestand as row in excel. row + 3 = xuong 3 row.
var row = sheet.CreateRow(rowIndex);
var cell = row.CreateCell(0);
cell.SetCellValue("MRF");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("Status");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("From");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("Deliver Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("Location");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("Project Code");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("Project Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Created Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("Created By");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(9);
cell.SetCellValue("Modified Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(10);
cell.SetCellValue("Modified By");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
// Add data rows
foreach (var requisition in requisitionMasters)
{
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(requisition.MRF);
row.CreateCell(1).SetCellValue(requisition.Status);
row.CreateCell(2).SetCellValue(requisition.From);
row.CreateCell(3).SetCellValue(requisition.Deliver_Date != null
? requisition.Deliver_Date.Value.ToString("dd/MM/yyyy")
: requisition.Deliver_Date.ToString());
row.CreateCell(4).SetCellValue(requisition.Location);
row.CreateCell(5).SetCellValue(requisition.Project_Code);
row.CreateCell(6).SetCellValue(requisition.Project_Name);
row.CreateCell(7).SetCellValue(requisition.Created_Date != null
? requisition.Created_Date.Value.ToString("dd/MM/yyyy")
: requisition.Created_Date.ToString());
row.CreateCell(8).SetCellValue(requisition.Created_By);
row.CreateCell(9).SetCellValue(requisition.Modified_Date != null
? requisition.Modified_Date.Value.ToString("dd/MM/yyyy")
: requisition.Modified_Date.ToString());
row.CreateCell(10).SetCellValue(requisition.Modified_By);
rowIndex++;
}
// Auto-size each column
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024);
}
// Add row indicating date/time report was generated...
sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy"));
#endregion
#region Detail sheet
sheet = workbook.CreateSheet("Details");
#region Add header labels
rowIndex = 0;
row = sheet.CreateRow(rowIndex);
cell = row.CreateCell(0);
cell.SetCellValue("MRF");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("Stock Code");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("Stock Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("Stock Type");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("Quantity");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("To be Purchased");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("Follow Up Required");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Purchased");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("Sent");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(9);
cell.SetCellValue("Status");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(10);
cell.SetCellValue("Date Assign");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(11);
cell.SetCellValue("Remark");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(12);
cell.SetCellValue("Unit");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(13);
cell.SetCellValue("Category");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(14);
cell.SetCellValue("Part No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(15);
cell.SetCellValue("Ral No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(16);
cell.SetCellValue("Color");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
#endregion
// Add data rows
var lastProductName = string.Empty;
// For sum in excel
// var startRowIndexForProductDetails = 1;
foreach (var detail in requisitionDetails)
{
// Show a summary row for each new product
var productNameToShow = string.Empty;
if (string.Compare(detail.MRF, lastProductName) != 0)
{
if (!string.IsNullOrEmpty(lastProductName))
{
// Add the subtotal row
// AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle);
// rowIndex += 3;
}
productNameToShow = detail.MRF;
lastProductName = detail.MRF;
// startRowIndexForProductDetails = rowIndex;
}
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(productNameToShow);
row.CreateCell(1).SetCellValue(detail.Stock_Code);
row.CreateCell(2).SetCellValue(detail.Stock_Name);
row.CreateCell(3).SetCellValue(detail.Type);
row.CreateCell(4).SetCellValue(detail.Quantity.ToString());
row.CreateCell(5).SetCellValue(detail.Quantity_PE.ToString());
row.CreateCell(6).SetCellValue(detail.FollowUpRequired.ToString());
row.CreateCell(7).SetCellValue(detail.Purchased.ToString());
row.CreateCell(8).SetCellValue(detail.Sent.ToString());
row.CreateCell(9).SetCellValue(detail.Status);
row.CreateCell(10).SetCellValue(detail.Created_Date != null
? detail.Created_Date.Value.ToString("dd/MM/yyyy")
: detail.Created_Date.ToString());
row.CreateCell(11).SetCellValue(detail.Remark);
row.CreateCell(12).SetCellValue(detail.Unit);
row.CreateCell(13).SetCellValue(detail.Category);
row.CreateCell(14).SetCellValue(detail.Part_No);
row.CreateCell(15).SetCellValue(detail.Ral_No);
row.CreateCell(16).SetCellValue(detail.Color);
//if (productDetail.Order.OrderDate.HasValue)
//{
// cell = row.CreateCell(2);
// cell.SetCellValue(productDetail.Order.OrderDate.Value.ToShortDateString());
// cell.CellStyle = rightAlignedCellStyle;
//}
//cell = row.CreateCell(3);
//cell.SetCellType(CellType.NUMERIC);
//cell.SetCellValue((double)productDetail.UnitPrice);
//cell.CellStyle = currencyCellStyle;
//cell = row.CreateCell(5);
//cell.SetCellType(CellType.NUMERIC);
//cell.SetCellValue((double)productDetail.UnitPrice * productDetail.Quantity);
//cell.CellStyle = currencyCellStyle;
rowIndex++;
}
// Add the subtotal row for the last product
// AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle);
// rowIndex += 2;
// Auto-size each column
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024);
}
// Add row indicating date/time report was generated...
// sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy"));
#endregion
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
var saveAsFileName = string.Format("Requisition-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-");
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}
}
/// </summary>
/// <param name="dt"> 数据源</param>
/// <returns>stream</returns>
public static Stream StreamData(DataTable dt, string sheetname, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = null;
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dt.Columns.Count];
foreach (DataColumn item in dt.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
sheet = (HSSFSheet)workbook.CreateSheet(sheetname);
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dt.Columns.Count - 1));
headerRow = null;
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); //遍历datatable，将datatable的列名赋予sheet
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
headerRow = null;
}
#endregion
int index = 2; //表头和列头已经占用一行，所以从2开始
foreach (DataRow row in dt.Rows)
{
HSSFRow datarow = (HSSFRow)sheet.CreateRow(index);
foreach (DataColumn column in dt.Columns)
{
// row.CreateCell(column.Ordinal).SetCellValue(datarow[column].ToString()); //遍历并将值放入sheet
HSSFCell newCell = (HSSFCell)datarow.CreateCell(column.Ordinal); // 实例化cell
string drValue = row[column].ToString();
if (drValue == null || drValue == "")
{
newCell.SetCellValue("");
continue;
}
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
case "System.DateTime"://日期类型
newCell.SetCellValue(drValue);
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":
case "System.Float":
case "System.Single":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
index++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//headerrow = null;
//workbook = null;
workbook.Dispose();
return ms;
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <param name="headerText"></param>
/// <param name="sheetName"></param>
/// <param name="columnName"></param>
/// <param name="columnTitle"></param>
/// <returns></returns>
public static HSSFWorkbook GenerateData(DataTable table, string headerText, string sheetName, string[] columnName, string[] columnTitle)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet(sheetName);
#region 设置文件属性信息
//创建一个文档摘要信息实体。
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Weilog Team"; //公司名称
hssfworkbook.DocumentSummaryInformation = dsi;
//创建一个摘要信息实体。
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = " Weilog 系统";
si.Title = headerText;
si.Subject = headerText;
si.CreateDateTime = DateTime.Now;
hssfworkbook.SummaryInformation = si;
#endregion
ICellStyle dateStyle = hssfworkbook.CreateCellStyle();
IDataFormat format = hssfworkbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 取得列宽
int[] colWidth = new int[columnName.Length];
for (int i = 0; i < columnName.Length; i++)
{
colWidth[i] = Encoding.GetEncoding(936).GetBytes(columnTitle[i]).Length;
}
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < columnName.Length; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(table.Rows[i][columnName[j]].ToString()).Length;
if (intTemp > colWidth[j])
{
colWidth[j] = intTemp;
}
}
}
#endregion
int rowIndex = 0;
foreach (DataRow row in table.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = hssfworkbook.CreateSheet(sheetName + ((int)rowIndex / 65535).ToString());
}
#region 表头及样式
//if (!string.IsNullOrEmpty(headerText))
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(headerText);
ICellStyle headStyle = hssfworkbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
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));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1);
IRow headerRow;
//if (!string.IsNullOrEmpty(headerText))
//{
// headerRow = sheet.CreateRow(0);
//}
//else
//{
headerRow = sheet.CreateRow(1);
//}
ICellStyle headStyle = hssfworkbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < columnName.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(columnTitle[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
if ((colWidth[i] + 1) * 256 > 30000)
{
sheet.SetColumnWidth(i, 10000);
}
else
{
sheet.SetColumnWidth(i, (colWidth[i] + 1) * 256);
}
}
/*
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);
}
* */
}
#endregion
//if (!string.IsNullOrEmpty(headerText))
//{
// rowIndex = 1;
//}
//else
//{
rowIndex = 2;
//}
}
#endregion
#region 填充数据
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < columnName.Length; i++)
{
ICell newCell = dataRow.CreateCell(i);
string drValue = row[columnName[i]].ToString();
switch (table.Columns[columnName[i]].DataType.ToString())
{
case "System.String"://字符串类型
if (drValue.ToUpper() == "TRUE")
newCell.SetCellValue("是");
else if (drValue.ToUpper() == "FALSE")
newCell.SetCellValue("否");
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);
if (boolV)
newCell.SetCellValue("是");
else
newCell.SetCellValue("否");
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++;
}
return hssfworkbook;
}
/// <summary>
/// Adjusts the column width to fit the contents.
/// This Process can be relatively slow on large sheets, so this should
/// normally only be called once per column, at the end of your
/// Processing.
/// You can specify whether the content of merged cells should be considered or ignored.
/// Default is to ignore merged cells.
/// </summary>
/// <param name="column">the column index</param>
/// <param name="useMergedCells">whether to use the contents of merged cells when calculating the width of the column</param>
public void AutoSizeColumn(int column, bool useMergedCells)
{
/**
* Excel measures columns in Units of 1/256th of a Char width
* but the docs say nothing about what particular Char is used.
* '0' looks to be a good choice.
*/
char defaultChar = '0';
/**
* This is the multiple that the font height is scaled by when determining the
* boundary of rotated text.
*/
double fontHeightMultiple = 2.0;
//FontRenderContext frc = new FontRenderContext(null, true, true);
HSSFWorkbook wb = new HSSFWorkbook(book);
NPOI.SS.UserModel.Font defaultFont = wb.GetFontAt((short)0);
//str = new AttributedString("" + defaultChar);
//CopyAttributes(defaultFont, str, 0, 1);
//layout = new TextLayout(str.GetEnumerator(), frc);
System.Drawing.Font font = HSSFFont2Font((HSSFFont)defaultFont);
int defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width;
double width = -1;
bool skipthisrow = false;
for (IEnumerator it = rows.Values.GetEnumerator(); it.MoveNext(); )
{
HSSFRow row = (HSSFRow)it.Current;
NPOI.SS.UserModel.Cell cell = (HSSFCell)row.GetCell(column);
if (cell == null) continue;
int colspan = 1;
for (int i = 0; i < NumMergedRegions; i++)
{
NPOI.SS.Util.CellRangeAddress region = GetMergedRegion(i);
if (ContainsCell(region, row.RowNum, column))
{
if (!useMergedCells)
{
// If we're not using merged cells, skip this one and move on to the next.
skipthisrow = true;
}
cell = row.GetCell(region.FirstColumn);
colspan = 1 + region.LastColumn - region.FirstColumn;
}
}
if (skipthisrow)
{
continue;
}
NPOI.SS.UserModel.CellStyle style = cell.CellStyle;
NPOI.SS.UserModel.Font font1 = wb.GetFontAt(style.FontIndex);
if (cell.CellType == NPOI.SS.UserModel.CellType.STRING)
{
HSSFRichTextString rt = (HSSFRichTextString)cell.RichStringCellValue;
String[] lines = rt.String.Split(new char[] { '\n' });
for (int k = 0; k < lines.Length; k++)
{
String txt = lines[k] + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font1, str, 0, txt.Length);
if (rt.NumFormattingRuns > 0)
{
for (int j = 0; j < lines[k].Length; j++)
{
int idx = rt.GetFontAtIndex(j);
if (idx != 0)
{
NPOI.SS.UserModel.Font fnt = wb.GetFontAt((short)idx);
//copyAttributes(fnt, str, j, j + 1);
}
}
}
//layout = new TextLayout(str.GetEnumerator(), frc);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the Unrotated top and bottom of the text that normally wouldn't be present if Unscaled, but
* is Added by the standard Excel autosize.
*/
double angle = style.Rotation * 2.0 * Math.PI / 360.0;
//Tony Qu
//TODO:: text rotated width measure
//AffineTransform trans = new AffineTransform();
//trans.concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.concatenate(
//AffineTransform.GetScaleInstance(1, fontHeightMultiple)
//);
width = Math.Max(width, ((TextRenderer.MeasureText(txt, font).Width * 1.0 / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
throw new NotImplementedException();
}
else
{
//width = Math.Max(width, ((TextRenderer.MeasureText(txt, font).Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
width = Math.Max(width, (TextRenderer.MeasureText(txt, font).Width * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
else
{
String sval = null;
if (cell.CellType == NPOI.SS.UserModel.CellType.NUMERIC)
{
NPOI.SS.UserModel.DataFormat dataformat = wb.CreateDataFormat();
short idx = style.DataFormat;
String format = "General";
if (idx >= 0)
{
format = dataformat.GetFormat(idx).Replace("\"", "");
}
double value = cell.NumericCellValue;
try
{
if ("General".Equals(format))
sval = "" + value;
else
{
sval = value.ToString("F");
}
}
catch (Exception)
{
sval = "" + value;
}
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.BOOLEAN)
{
sval = cell.BooleanCellValue.ToString();
}
if (sval != null)
{
String txt = sval + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.Length);
//layout = new TextLayout(str.GetEnumerator(), frc);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the Unrotated top and bottom of the text that normally wouldn't be present if Unscaled, but
* is Added by the standard Excel autosize.
*/
//Tony Qu
//TODO:: text rotated width measure
//AffineTransform trans = new AffineTransform();
//trans.concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.concatenate(
//AffineTransform.GetScaleInstance(1, fontHeightMultiple)
//);
//width = Math.Max(width, ((layout.GetOutline(trans).GetBounds().Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
throw new NotImplementedException();
}
else
{
//width = Math.Max(width, ((TextRenderer.MeasureText(txt, font).Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
width = Math.Max(width, (TextRenderer.MeasureText(txt, font).Width * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
}
if (width != -1)
{
if (width > short.MaxValue)
{ //width can be bigger that Short.MAX_VALUE!
width = short.MaxValue;
}
_sheet.SetColumnWidth(column, (short)(width * 256));
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText = "统计数据", string strImageFileName = "")
{
IWorkbook workbook = new HSSFWorkbook();
#region 填充图片
if (strImageFileName != "")
{
ISheet sheet1 = workbook.CreateSheet("图标");
byte[] bytes = System.IO.File.ReadAllBytes(strImageFileName);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
// Create the drawing patriarch. This is the top level container for all shapes.
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
//add a picture
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 0, 20, 20);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
//rowIndex += 20;
}
#endregion
if (strHeaderText == "")
{
strHeaderText = "统计数据";
}
ISheet sheet = workbook.CreateSheet(strHeaderText);
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
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;
arrColWidth[item.Ordinal] = arrColWidth[item.Ordinal] > 50 ? 50 : arrColWidth[item.Ordinal];
}
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] && intTemp < 50)
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
if (strHeaderText != "")
{
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
else
{
rowIndex = 0;
}
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(rowIndex);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
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++;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
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();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return ms;
}
}
/// <summary>
/// 创建工作表
/// </summary>
/// <param name="workBook"></param>
/// <param name="dt"></param>
/// <param name="name"></param>
/// <param name="header"></param>
/// <returns></returns>
private static HSSFSheet CreateSheet(HSSFWorkbook workBook, DataTable dt, string name = "sheet1", string header = "")
{
HSSFSheet sheet = (HSSFSheet)workBook.CreateSheet(name);
HSSFCellStyle dateStyle = (HSSFCellStyle)workBook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workBook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dt.Columns.Count];
foreach (DataColumn item in dt.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dt.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workBook.CreateSheet();
}
#region 表头及样式
{
if (!string.IsNullOrEmpty(header))
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(header);
HSSFCellStyle headStyle = (HSSFCellStyle)workBook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workBook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
font.FontName = "微软雅黑";
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 = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workBook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workBook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
font.FontName = "微软雅黑";
headStyle.SetFont(font);
foreach (DataColumn column in dt.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 = (HSSFRow)sheet.CreateRow(rowIndex);
HSSFCellStyle cellStyle = (HSSFCellStyle)workBook.CreateCellStyle();
HSSFFont cellFont = (HSSFFont)workBook.CreateFont();
cellFont.FontName = "微软雅黑";
cellStyle.SetFont(cellFont);
foreach (DataColumn column in dt.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"://日期类型
System.DateTime dateV;
System.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;
}
newCell.CellStyle = cellStyle;
}
#endregion
rowIndex++;
}
return sheet;
}
/// <summary>
/// Create an Excel document with a nice breakdown of Trello.
/// </summary>
/// <returns></returns>
public static byte[] ToExcel()
{
List<Board> _boards = ParseBoardData();
// New workbook
var workbook = new HSSFWorkbook();
foreach (Board _boardInfo in _boards) {
JavaScriptSerializer _worker = new JavaScriptSerializer();
_worker.MaxJsonLength = int.MaxValue;
File.WriteAllText(HttpContext.Current.Server.MapPath("") + @"\output\" + _boardInfo.name.ToString() + ".json", _worker.Serialize(_boardInfo));
// Create a sheet
var sheet = workbook.CreateSheet(_boardInfo.name.ToString());
// Kill everything not supposed to be in there.
var rowIndex = 0;
#region HEADING STYLE
HSSFFont _headingFont = (HSSFFont)workbook.CreateFont();
_headingFont.Boldweight = (short)FontBoldWeight.Bold;
_headingFont.Color = HSSFColor.Grey80Percent.Index;
//_headingFont.FontHeight = 280;
HSSFCellStyle _headingStyle = (HSSFCellStyle)workbook.CreateCellStyle();
_headingStyle = (HSSFCellStyle)workbook.CreateCellStyle();
_headingStyle.VerticalAlignment = VerticalAlignment.Center;
_headingStyle.SetFont(_headingFont);
#endregion
// Create the heading
var row = sheet.CreateRow(rowIndex);
row.Height = 350;
HSSFCell cell = (HSSFCell)row.CreateCell(0);
cell.SetCellValue("Card name");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(1);
cell.SetCellValue("Date created");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(2);
cell.SetCellValue("Pending");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(3);
cell.SetCellValue("In Progress");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(4);
cell.SetCellValue("In Testing");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(5);
cell.SetCellValue("Released");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(6);
cell.SetCellValue("On Hold");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(7);
cell.SetCellValue("Description");
cell.CellStyle = _headingStyle;
// Next row
rowIndex++;
#region ROW STYLE
HSSFCellStyle _rowStyle = (HSSFCellStyle)workbook.CreateCellStyle();
_rowStyle.VerticalAlignment = VerticalAlignment.Center;
IDataFormat _dataFormat = workbook.CreateDataFormat();
#endregion
if (_boardInfo.Lists != null) {
foreach (Board.List _listData in _boardInfo.Lists) {
if (_listData.Cards != null) {
foreach (Board.List.Card _cardData in _listData.Cards) {
// Add the row
row = sheet.CreateRow(rowIndex);
row.Height = 350;
// Card name
cell = (HSSFCell)row.CreateCell(0);
cell.SetCellValue(_cardData.name.ToString());
cell.CellStyle = _rowStyle;
// Created
cell = (HSSFCell)row.CreateCell(1);
DateTime _startDate = new DateTime(1970, 01, 01);
cell.SetCellValue(_startDate.AddSeconds(int.Parse(_cardData.id.ToString().Substring(0, 8), System.Globalization.NumberStyles.HexNumber)).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
// Actions
foreach (Board.List.Card.Action _actionData in _cardData.Actions) {
if (_actionData.data != null) {
if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("pending")) {
cell = (HSSFCell)row.CreateCell(2);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
} else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("progress")) {
cell = (HSSFCell)row.CreateCell(3);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
} else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("testing")) {
cell = (HSSFCell)row.CreateCell(4);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
} else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("release")) {
cell = (HSSFCell)row.CreateCell(5);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
} else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("hold")) {
cell = (HSSFCell)row.CreateCell(6);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
}
}
}
// Description
cell = (HSSFCell)row.CreateCell(7);
cell.SetCellValue(_cardData.desc.ToString());
cell.CellStyle = _rowStyle;
// Next row.
rowIndex++;
}
}
}
// Size it
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
sheet.AutoSizeColumn(3);
sheet.AutoSizeColumn(4);
sheet.AutoSizeColumn(5);
sheet.AutoSizeColumn(6);
sheet.AutoSizeColumn(7);
sheet.CreateFreezePane(0, 1);
}
}
#region OUTPUT
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream()) {
workbook.Write(exportData);
return exportData.GetBuffer();
}
#endregion
}
/// <summary>DataTable导出到Excel的MemoryStream</summary>
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText, string[] str)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
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 == 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.FontName = str[5]; //[5]
font.FontHeightInPoints = Convert.ToInt16(str[0]); //[0]
font.Boldweight = Convert.ToInt16(str[1]); //[1]
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.FontName = str[6]; //[6]
font.FontHeightInPoints = Convert.ToInt16(str[2]); //[2]
font.Boldweight = Convert.ToInt16(str[3]); //[3]
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) * Convert.ToInt32(str[4])); //[4]
}
//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": //字符串类型
string result = drValue;
newCell.SetCellValue(result);
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++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet;
//workbook.Dispose();
return ms;
}
public void TestWriteDataFormat()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetSimple", ".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
IDataFormat format = wb.CreateDataFormat();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
short df = format.GetFormat("0.0");
cs.DataFormat=(df);
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellStyle=(cs);
c.SetCellValue(1.25);
wb.Write(out1);
out1.Close();
FileStream stream = new FileStream(filepath,FileMode.OpenOrCreate);
POIFSFileSystem fs = new POIFSFileSystem(stream);
HSSFWorkbook workbook = new HSSFWorkbook(fs);
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
ICell cell =
sheet.GetRow(0).GetCell(0);
format = workbook.CreateDataFormat();
Assert.AreEqual(1.25, cell.NumericCellValue, 1e-10);
Assert.AreEqual(format.GetFormat(df), "0.0");
Assert.AreEqual(format, workbook.CreateDataFormat());
stream.Close();
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.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");
//取得列宽
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 = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
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.
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
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 = (HSSFRow)sheet.CreateRow(rowIndex);
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"://日期类型
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;
return ms;
}
}
private void ConvertStyle(HSSFWorkbook workbook, string name)
{
ExcelStyle cf = ExcelStyles[name];
Font font = workbook.CreateFont();
CellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = GetColor(cf.ForegroundColor);
style.FillPattern = GetPatternType(cf.ForegroundPattern);
font.Boldweight = (short)GetBoldWeight(cf.Bold);
if (!string.IsNullOrEmpty(cf.FontName))
font.FontName = cf.FontName;
font.FontHeightInPoints = cf.FontHeight;
style.SetFont(font);
if (cf.DataFormat != string.Empty)
{
DataFormat format = workbook.CreateDataFormat();
short formatIndex = format.GetFormat(cf.DataFormat);
style.DataFormat = formatIndex;
}
this.CellStyles.Add(name, style);
}
public void Export2Excel(string fileName, DataSet data)
{
MemoryStream ms = new MemoryStream();
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet();
#region 文件属性
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "xi";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "xi";
si.ApplicationName = "xi";
si.LastAuthor = "xi";
si.CreateDateTime = DateTime.Now;
hssfworkbook.SummaryInformation = si;
#endregion
#region Excel单元格格式
int rowIndex = 0;
HSSFRow headRow = null;
HSSFRow titleRow = null;
HSSFRow dataRow = null;
HSSFCell cell = null;
HSSFCellStyle headStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
HSSFFont headfont = (HSSFFont)hssfworkbook.CreateFont();
headStyle.SetFont(headfont);
HSSFCellStyle titleStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.CENTER;
HSSFFont titlefont = (HSSFFont)hssfworkbook.CreateFont();
headStyle.SetFont(titlefont);
HSSFCellStyle cellDateStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat cellDateFormat = hssfworkbook.CreateDataFormat() as HSSFDataFormat;
if ("Y" == "Y")
cellDateStyle.DataFormat = cellDateFormat.GetFormat("yyyy-MM-dd HH:mm");
else
cellDateStyle.DataFormat = cellDateFormat.GetFormat("yyyy-MM-dd");
//数量小数格式化字符串
HSSFCellStyle cellNumStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat cellNumFormat = hssfworkbook.CreateDataFormat() as HSSFDataFormat;
string formatValue = "0";
string formatStr = string.Empty;
switch (formatValue)
{
#region 格式化
case "0":
formatStr = "0";
break;
case "1":
formatStr = "0.0";
break;
case "2":
formatStr = "0.00";
break;
case "3":
formatStr = "0.000";
break;
case "4":
formatStr = "0.0000";
break;
case "5":
formatStr = "0.00000";
break;
default:
formatStr = "{0:0}";
break;
#endregion
}
if (formatStr == "0" || formatStr == "0.00")
{
cellNumStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(formatStr);
}
else
{
cellNumStyle.DataFormat = cellNumFormat.GetFormat(formatStr);
}
//日期格式字符串
string dateFormat = "yyyy-MM-dd";
if ("Y" == "Y")
{
dateFormat += " " + "HH:mm";
}
#endregion
foreach (DataRow row in data.Tables[0].Rows)
{
#region 新建sheet 填写表头 列头
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)hssfworkbook.CreateSheet();
}
#region 列头
titleRow = (HSSFRow)sheet.CreateRow(rowIndex);
for (int i = 0; i < data.Tables[0].Columns.Count; i++)
{
titleRow.CreateCell(i).SetCellValue(data.Tables[0].Columns[i].ColumnName);
titleRow.GetCell(i).CellStyle = titleStyle;
}
rowIndex++;
#endregion
}
#endregion
#region 明细
dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
for (int i = 0; i < data.Tables[0].Columns.Count; i++)
{
cell = (HSSFCell)dataRow.CreateCell(i);
string value = row[data.Tables[0].Columns[i].ColumnName].ToString();
switch (data.Tables[0].Columns[i].DataType.FullName)
{
case "System.String":
cell.SetCellValue(value);
break;
case "System.DateTime":
DateTime datevalue;
DateTime.TryParse(value, out datevalue);
if (datevalue != new DateTime())
{
cell.CellStyle = cellDateStyle;
cell.SetCellValue(DateTime.Parse(datevalue.ToString(dateFormat)));
}
else
{
cell.SetCellValue("");
}
break;
case "System.Boolean":
bool boolvalue = false;
bool.TryParse(value, out boolvalue);
cell.SetCellValue(boolvalue);
break;
case "System.Int16":
Int16 int16 = 0;
Int16.TryParse(value, out int16);
cell.SetCellValue(int16);
break;
case "System.Int32":
Int32 int32 = 0;
Int32.TryParse(value, out int32);
cell.SetCellValue(int32);
break;
case "System.Int64":
Int64 int64 = 0;
Int64.TryParse(value, out int64);
cell.SetCellValue(int64);
break;
case "System.Byte":
int intV = 0;
int.TryParse(value, out intV);
cell.SetCellValue(intV);
break;
case "System.Decimal":
double decV = 0;
double.TryParse(value, out decV);
cell.SetCellValue(decV);
cell.CellStyle = cellNumStyle;
break;
case "System.Single":
float floatV = 0;
float.TryParse(value, out floatV);
cell.SetCellValue(floatV);
cell.CellStyle = cellNumStyle;
break;
case "System.Double":
double doubV = 0;
double.TryParse(value, out doubV);
cell.SetCellValue(doubV);
cell.CellStyle = cellNumStyle;
break;
case "System.DBNull":
cell.SetCellValue("");
break;
default:
cell.SetCellValue(value);
break;
}
}
#endregion
rowIndex++;
}
hssfworkbook.Write(ms);
ms.Flush();
ms.Position = 0;
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "GB2312";
HttpContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
HttpContext.Response.BinaryWrite(ms.ToArray());
HttpContext.Response.End();
ms.Close();
sheet = null;
hssfworkbook = null;
ms = null;
}
private void btnExportToExcel_Click(object sender, RoutedEventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "excel文件|*.xls";
if (sfd.ShowDialog() != true)
{
return;
}
string fileName = sfd.FileName;
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("员工信息表");
IRow rowHeader = sheet.CreateRow(0);
rowHeader.CreateCell(0, CellType.STRING).SetCellValue("姓名");
rowHeader.CreateCell(1, CellType.STRING).SetCellValue("工号");
rowHeader.CreateCell(2, CellType.STRING).SetCellValue("入职时间");
rowHeader.CreateCell(3, CellType.STRING).SetCellValue("学历");
rowHeader.CreateCell(4, CellType.STRING).SetCellValue("毕业院校");
rowHeader.CreateCell(5, CellType.STRING).SetCellValue("基本工资");
rowHeader.CreateCell(6, CellType.STRING).SetCellValue("部门");
rowHeader.CreateCell(7, CellType.STRING).SetCellValue("职位");
rowHeader.CreateCell(8, CellType.STRING).SetCellValue("合同签订日期");
rowHeader.CreateCell(9, CellType.STRING).SetCellValue("合同到期日期");
Employee[] employees = (Employee[])datagrid.ItemsSource;
ICellStyle cellStyle = workbook.CreateCellStyle();
IDataFormat dataFormat = workbook.CreateDataFormat();
cellStyle.DataFormat = dataFormat.GetFormat("yyyy\"年\"m\"月\"d\"日\"");
for (int i = 0; i < employees.Length; i++)
{
Employee employee = employees[i];
IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0, CellType.STRING).SetCellValue(employee.Name);
row.CreateCell(1, CellType.STRING).SetCellValue(employee.Number);
ICell dateCell = row.CreateCell(2, CellType.NUMERIC);
dateCell.CellStyle = cellStyle;
dateCell.SetCellValue(employee.InDate);
row.CreateCell(3, CellType.STRING).SetCellValue(IdNameDAL.GetEducationNameById(employee.EducationId));
row.CreateCell(4, CellType.STRING).SetCellValue(employee.School);
row.CreateCell(5, CellType.STRING).SetCellValue(employee.BaseSalary);
row.CreateCell(6, CellType.STRING).SetCellValue(DepartmentDAL.GetById(employee.DepartmentId).Name);
row.CreateCell(7, CellType.STRING).SetCellValue(employee.Position);
ICell contractBegindateCell = row.CreateCell(8, CellType.NUMERIC);
contractBegindateCell.CellStyle = cellStyle;
contractBegindateCell.SetCellValue(employee.ContractStartDay);
ICell contractEnddateCell = row.CreateCell(9, CellType.NUMERIC);
contractEnddateCell.CellStyle = cellStyle;
contractEnddateCell.SetCellValue(employee.ContractEndDay);
}
using (Stream stream = File.OpenWrite(fileName))
{
workbook.Write(stream);
}
}
/// <summary>
/// 导出数据行
/// </summary>
/// <param name="dtSource"></param>
/// <param name="drSource"></param>
/// <param name="currentExcelRow"></param>
/// <param name="excelSheet"></param>
/// <param name="excelWorkBook"></param>
protected static void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook)
{
for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
{
//列名称
string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
HSSFCell newCell = null;
System.Type rowType = drSource[columnsName].GetType();
string drValue = drSource[columnsName].ToString().Trim();
switch (rowType.ToString())
{
case "System.String"://字符串类型
drValue = drValue.Replace("&", "&");
drValue = drValue.Replace(">", ">");
drValue = drValue.Replace("<", "<");
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(dateV);
//格式化显示
HSSFCellStyle cellStyle = excelWorkBook.CreateCellStyle();
HSSFDataFormat format = excelWorkBook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
newCell.CellStyle = cellStyle;
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell = currentExcelRow.CreateCell(cellIndex);
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 = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(intV.ToString());
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue("");
break;
default:
throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
}
}
}
public void ExportToExcel(int page, int size, int store, int poType, string status, string po, int supplier, string srv, string stockCode, string stockName, string fd, string td, string enable)
{
// Get the data to report on
var masters = _service.ListCondition(page, size, store, poType, status, po, supplier, srv, stockCode, stockName, fd, td, enable);
var details = _service.ListConditionDetailExcel(page, size, store, poType, status, po, supplier, srv, stockCode, stockName, fd, td, enable);
// Create a new workbook
var workbook = new HSSFWorkbook();
#region Cell Styles
#region HeaderLabel Cell Style
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER;
headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerLabelCellStyle.SetFont(headerLabelFont);
#endregion
#region RightAligned Cell Style
var rightAlignedCellStyle = workbook.CreateCellStyle();
rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT;
#endregion
#region Currency Cell Style
var currencyCellStyle = workbook.CreateCellStyle();
currencyCellStyle.Alignment = HorizontalAlignment.RIGHT;
var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
currencyCellStyle.DataFormat = formatId;
#endregion
#region Detail Subtotal Style
var detailSubtotalCellStyle = workbook.CreateCellStyle();
detailSubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailSubtotalFont = workbook.CreateFont();
detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailSubtotalCellStyle.SetFont(detailSubtotalFont);
#endregion
#region Detail Currency Subtotal Style
var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle();
detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailCurrencySubtotalFont = workbook.CreateFont();
detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont);
formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
detailCurrencySubtotalCellStyle.DataFormat = formatId;
#endregion
#endregion
#region Master sheet
var sheet = workbook.CreateSheet("Main");
// Add header labels
var rowIndex = 0;
// Undestand as row in excel. row + 3 = xuong 3 row.
var row = sheet.CreateRow(rowIndex);
var cell = row.CreateCell(0);
cell.SetCellValue("No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("PE Code");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("Supplier");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("PE Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("PE Total");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("Location");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("PE Type");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Currency");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("PE Status");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
// Add data rows
foreach (var item in masters)
{
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(item.No.ToString());
row.CreateCell(1).SetCellValue(item.PE_Code);
row.CreateCell(2).SetCellValue(item.Supplier);
row.CreateCell(3).SetCellValue(item.PE_Date.ToString("dd/MM/yyyy"));
row.CreateCell(4).SetCellValue(item.PE_Total.ToString());
row.CreateCell(5).SetCellValue(item.PE_Location);
row.CreateCell(6).SetCellValue(item.PE_Type);
row.CreateCell(7).SetCellValue(item.Currency);
row.CreateCell(8).SetCellValue(item.PE_Status);
rowIndex++;
}
// Auto-size each column
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024);
}
// Add row indicating date/time report was generated...
sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy"));
#endregion
#region Detail sheet
sheet = workbook.CreateSheet("Details");
#region Add header labels
rowIndex = 0;
row = sheet.CreateRow(rowIndex);
cell = row.CreateCell(0);
cell.SetCellValue("PE Code");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("Stock Code");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("Stock Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("Qty Order");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("Qty Receive");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("Qty Pending");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("Invoice No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Invoice Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("MRF");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(9);
cell.SetCellValue("SRV");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(10);
cell.SetCellValue("Stock Type");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(11);
cell.SetCellValue("Stock Unit");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(12);
cell.SetCellValue("Stock Category");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(13);
cell.SetCellValue("Part No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(14);
cell.SetCellValue("Ral No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(15);
cell.SetCellValue("Color");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(16);
cell.SetCellValue("Description");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
#endregion
// Add data rows
var lastProductName = string.Empty;
// For sum in excel
// var startRowIndexForProductDetails = 1;
foreach (var detail in details)
{
// Show a summary row for each new product
//var productNameToShow = string.Empty;
//if (String.CompareOrdinal(detail.PE_Code, lastProductName) != 0)
//{
// if (!string.IsNullOrEmpty(lastProductName))
// {
// // Add the subtotal row
// // AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle);
// // rowIndex += 3;
// }
// productNameToShow = detail.PE_Code;
// lastProductName = detail.PE_Code;
// // startRowIndexForProductDetails = rowIndex;
//}
row = sheet.CreateRow(rowIndex);
//row.CreateCell(0).SetCellValue(productNameToShow);
row.CreateCell(0).SetCellValue(detail.PE_Code);
row.CreateCell(1).SetCellValue(detail.Stock_Code);
row.CreateCell(2).SetCellValue(detail.Stock_Name);
row.CreateCell(3).SetCellValue(detail.Qty_Total.ToString(CultureInfo.InvariantCulture));
row.CreateCell(4).SetCellValue(detail.Qty_Received.ToString(CultureInfo.InvariantCulture));
row.CreateCell(5).SetCellValue(detail.Qty_Pending.ToString(CultureInfo.InvariantCulture));
row.CreateCell(6).SetCellValue(detail.InvoiceNo);
row.CreateCell(7).SetCellValue(detail.InvoiceDate.ToString("dd/MM/yyyy"));
row.CreateCell(8).SetCellValue(detail.MRF);
row.CreateCell(9).SetCellValue(detail.SRV);
row.CreateCell(10).SetCellValue(detail.Type);
row.CreateCell(11).SetCellValue(detail.Unit);
row.CreateCell(12).SetCellValue(detail.Category);
row.CreateCell(13).SetCellValue(detail.PartNo);
row.CreateCell(14).SetCellValue(detail.RalNo);
row.CreateCell(15).SetCellValue(detail.Color);
row.CreateCell(16).SetCellValue(detail.Description);
rowIndex++;
}
// Add the subtotal row for the last product
// AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle);
// rowIndex += 2;
// Auto-size each column
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024);
}
// Add row indicating date/time report was generated...
// sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy"));
#endregion
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
var saveAsFileName = string.Format("StockIn-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-");
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="listColumn"> </param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="defaultColumnWidth">默认列宽度 </param>
public static MemoryStream Export(DataTable dtSource, List<Column> listColumn, string strHeaderText, int defaultColumnWidth)
{
#region 门卫代码
if (listColumn == null) listColumn = new List<Column>();
#endregion
var workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "云学时代";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "云学时代"; //填加xls文件作者信息
si.ApplicationName = strHeaderText; //填加xls文件创建程序信息
si.LastAuthor = "云学时代"; //填加xls文件最后保存者信息
si.Comments = "云学时代"; //填加xls文件作者信息
si.Title = strHeaderText; //填加xls文件标题信息
si.Subject = "云学时代" + strHeaderText; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 标题样式
ICellStyle titleCellStyle = workbook.CreateCellStyle();
titleCellStyle.Alignment = HorizontalAlignment.Center;
titleCellStyle.FillForegroundColor = HSSFColor.BlueGrey.Index;
titleCellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SparseDots;
//titleCellStyle.FillBackgroundColor = HSSFColor.BLUE_GREY.index;
titleCellStyle.FillBackgroundColor = HSSFColor.BlueGrey.Index;
IFont titleFont = workbook.CreateFont();
titleFont.FontHeightInPoints = 18;
titleFont.Boldweight = 700;
titleFont.FontName = "黑体";
titleCellStyle.SetFont(titleFont);
#endregion
#region 表头样式
ICellStyle headCellStyle = workbook.CreateCellStyle();
headCellStyle.Alignment = HorizontalAlignment.Center;
//headCellStyle.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
headCellStyle.FillForegroundColor = HSSFColor.LightYellow.Index;
//headCellStyle.FillPattern = FillPatternType.SPARSE_DOTS;
headCellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SparseDots;
//headCellStyle.FillBackgroundColor = HSSFColor.LIGHT_YELLOW.index;
headCellStyle.FillBackgroundColor = HSSFColor.LightYellow.Index;
IFont headfont = workbook.CreateFont();
headfont.FontHeightInPoints = 10;
headfont.Boldweight = 700;
headfont.FontName = "宋体";
headCellStyle.SetFont(headfont);
#endregion
#region 单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
IFont cellfont = workbook.CreateFont();
cellfont.FontHeightInPoints = 10;
cellfont.Boldweight = 400;
cellfont.FontName = "宋体";
cellStyle.SetFont(cellfont);
#endregion
#region 日期样式
ICellStyle dateStyle = workbook.CreateCellStyle();
//dateStyle.Alignment=HorizontalAlignment.LEFT;
dateStyle.Alignment = HorizontalAlignment.Left;
IFont dateFont = workbook.CreateFont();
dateFont.FontHeightInPoints = 10;
dateFont.Boldweight = 400;
dateFont.FontName = "宋体";
dateStyle.SetFont(dateFont);
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#endregion
#region 取得列宽
//var arrColWidth = new int[dtSource.Columns.Count];
//foreach (DataColumn item in dtSource.Columns)
//{
// arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).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;
// }
// }
//}
for (int i = 0; i < dtSource.Columns.Count; i++)
{
var firstOrDefault = listColumn.FirstOrDefault(a => a.Key == dtSource.Columns[i].ColumnName);
if (firstOrDefault != null)
{
sheet.SetColumnWidth(i, firstOrDefault.Width);
}
else
{
sheet.SetColumnWidth(i, defaultColumnWidth);
}
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
IRow titleRow = sheet.CreateRow(0);
titleRow.HeightInPoints = 22;
titleRow.CreateCell(0).SetCellValue(strHeaderText);
titleRow.GetCell(0).CellStyle = titleCellStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
#endregion
#region 列头及样式
IRow headerRow = sheet.CreateRow(1);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headCellStyle;
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
newCell.CellStyle = cellStyle; //格式化显示
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
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;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
//using (var ms = new MemoryStream())
//{
// workbook.Write(ms);
// ms.Flush();
// ms.Position = 0;
// return ms;
//}
var ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
private bool AutoCheckSL()
{
HSSFWorkbook cb = new HSSFWorkbook();
ISheet cs = cb.CreateSheet();
ICellStyle style = cb.CreateCellStyle();
IDataFormat format = cb.CreateDataFormat();
style.DataFormat = format.GetFormat("N2");
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
ICellStyle style_all = cb.CreateCellStyle();
style_all.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
IRow hr = cs.CreateRow(0);
hr.CreateCell(0).SetCellValue("条码");
hr.CreateCell(1).SetCellValue("库存");
hr.CreateCell(2).SetCellValue("盘点");
hr.CreateCell(3).SetCellValue("售价");
hr.CreateCell(4).SetCellValue("品名");
hr.CreateCell(5).SetCellValue("进价");
for (int m = 0; m < 6; m++)
{
hr.GetCell(m).CellStyle = style_all;
}
cs.SetColumnWidth(0, 16 * 256);
cs.SetColumnWidth(1, 8 * 256);
cs.SetColumnWidth(2, 8 * 256);
cs.SetColumnWidth(3, 10 * 256);
cs.SetColumnWidth(4, 24 * 256);
cs.SetColumnWidth(5, 10 * 256);
int total = 1;
FileStream fs = new FileStream(this.textBox_pd.Text, FileMode.Open, FileAccess.Read);
HSSFWorkbook book = new HSSFWorkbook(fs);
ISheet sheet = book.GetSheetAt(0);
string tm;
for (int i = 0; i < sheet.LastRowNum + 1; i++)
{
IRow row = sheet.GetRow(i);
ICell cell = row.GetCell(0);
switch (cell.CellType)
{
case CellType.Numeric:
tm = cell.NumericCellValue.ToString();
break;
case CellType.String:
tm = cell.StringCellValue;
break;
default:
return false;
}
string s = "select kc.tm,kc.sl,pd.sl,goods.sj,goods.pm,goods.jj from kc join pd on(kc.tm=pd.tm) join goods on(kc.tm=goods.tm) where pd.tm='" + tm + "'";
Form_main.Command.CommandText = s;
MySqlDataReader dr = Form_main.Command.ExecuteReader();
dr.Read();
if (dr.GetInt32(1) == dr.GetInt32(2))
{
this.toolStripStatusLabel1.Text = "正在核对商品:" + tm + " OK";
Application.DoEvents();
}
else
{
IRow r = cs.CreateRow(total);
r.CreateCell(0).SetCellType(CellType.String);
r.GetCell(0).CellStyle = style_all;
r.GetCell(0).SetCellValue(dr.GetString(0));//tm
r.CreateCell(1).SetCellType(CellType.Numeric);
r.GetCell(1).CellStyle = style_all;
r.GetCell(1).SetCellValue(dr.GetInt32(1));//kc-sl
r.CreateCell(2).SetCellType(CellType.Numeric);
r.GetCell(2).CellStyle = style_all;
r.GetCell(2).SetCellValue(dr.GetInt32(2));//pd-sl
r.CreateCell(3).SetCellType(CellType.Numeric);//sj
r.GetCell(3).CellStyle = style;
r.GetCell(3).SetCellValue(dr.GetFloat(3));
r.CreateCell(4).SetCellType(CellType.String);//pm
r.GetCell(4).CellStyle = style_all;
r.GetCell(4).SetCellValue(dr.GetString(4));
r.CreateCell(5).SetCellType(CellType.Numeric);
r.GetCell(5).CellStyle = style;
r.GetCell(5).SetCellValue(dr.GetFloat(5));
total++;
this.toolStripStatusLabel1.Text = "正在核对商品:" + tm + " Error";
Application.DoEvents();
}
dr.Close();
}
if (total > 1)
{
MessageBox.Show("核对完成,共核对" + (sheet.LastRowNum + 1) + "件商品,其中不匹配商品" + cs.LastRowNum + "件需要保存", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "请选择保存的文件名及路径(核对中产生的不同数据)";
sfd.AddExtension = true;
sfd.Filter = "电子表格文件(*.xls)|*.xls";
if (sfd.ShowDialog(this) == DialogResult.OK)
{
fs = new FileStream(sfd.FileName, FileMode.Create, FileAccess.Write);
cb.Write(fs);
fs.Close();
MessageBox.Show("数量不匹配条码已保存至" + sfd.FileName);
}
else
{
MessageBox.Show("找到不匹配数据未能保存", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("核对完成,共" + (sheet.LastRowNum + 1) + "件商品,全部与电脑库存相符！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
this.textBox_kc.Clear();
this.textBox_pd.Clear();
this.toolStripStatusLabel1.Text = "就绪";
return true;
}
public void SetUp()
{
// One or more test methods depends on the american culture.
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
// create the formatter to Test
formatter = new HSSFDataFormatter();
// create a workbook to Test with
wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDataFormat format = wb.CreateDataFormat();
// create a row and Put some cells in it
IRow row = sheet.CreateRow(0);
// date value for July 8 1901 1:19 PM
double dateNum = 555.555;
// date value for July 8 1901 11:23 AM
double timeNum = 555.47431;
//valid date formats -- all should have "Jul" in output
String[] goodDatePatterns = {
"[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy",
"mmm/d/yy\\ h:mm PM;@",
"mmmm/d/yy\\ h:mm;@",
"mmmm/d;@",
"mmmm/d/yy;@",
"mmm/dd/yy;@",
"[$-409]d\\-mmm;@",
"[$-409]d\\-mmm\\-yy;@",
"[$-409]dd\\-mmm\\-yy;@",
"[$-409]mmm\\-yy;@",
"[$-409]mmmm\\-yy;@",
"[$-409]mmmm\\ d\\,\\ yyyy;@",
"[$-409]mmm/d/yy\\ h:mm:ss;@",
"[$-409]mmmm/d/yy\\ h:mm:ss am;@",
"[$-409]mmmmm;@",
"[$-409]mmmmm\\-yy;@",
"mmmm/d/yyyy;@",
"[$-409]d\\-mmm\\-yyyy;@"
};
//valid time formats - all should have 11:23 in output
String[] goodTimePatterns = {
"HH:MM",
"HH:MM:SS",
"HH:MM;HH:MM;HH:MM",
// This is fun - blue if positive time,
// red if negative time or green for zero!
"[BLUE]HH:MM;[RED]HH:MM;[GREEN]HH:MM",
"yyyy-mm-dd hh:mm",
"yyyy-mm-dd hh:mm:ss",
};
// valid number formats
String[] goodNumPatterns = {
"#,##0.0000",
"#,##0;[Red]#,##0",
"(#,##0.00_);(#,##0.00)",
"($#,##0.00_);[Red]($#,##0.00)",
"$#,##0.00",
"[$-809]#,##0.00", // international format
"[$-2]#,##0.00", // international format
"0000.00000%",
"0.000E+00",
"0.00E+00",
"[BLACK]0.00;[COLOR 5]##.##",
};
// invalid date formats -- will throw exception in DecimalFormat ctor
String[] badNumPatterns = {
"#,#$'#0.0000",
"'#','#ABC#0;##,##0",
"000 '123 4'5'6 000",
"#''0#0'1#10L16EE"
};
// create cells with good date patterns
for (int i = 0; i < goodDatePatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dateNum);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/format.GetFormat(goodDatePatterns[i]));
cell.CellStyle = (/*setter*/cellStyle);
}
row = sheet.CreateRow(1);
// create cells with time patterns
for (int i = 0; i < goodTimePatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(timeNum);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/format.GetFormat(goodTimePatterns[i]));
cell.CellStyle = (/*setter*/cellStyle);
}
row = sheet.CreateRow(2);
// create cells with num patterns
for (int i = 0; i < goodNumPatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(-1234567890.12345);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/format.GetFormat(goodNumPatterns[i]));
cell.CellStyle = (/*setter*/cellStyle);
}
row = sheet.CreateRow(3);
// create cells with bad num patterns
for (int i = 0; i < badNumPatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(1234567890.12345);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/format.GetFormat(badNumPatterns[i]));
cell.CellStyle = (/*setter*/cellStyle);
}
// Built in formats
{ // Zip + 4 format
row = sheet.CreateRow(4);
ICell cell = row.CreateCell(0);
cell.SetCellValue(123456789);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/format.GetFormat("00000-0000"));
cell.CellStyle = (/*setter*/cellStyle);
}
{ // Phone number format
row = sheet.CreateRow(5);
ICell cell = row.CreateCell(0);
cell.SetCellValue(5551234567D);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/format.GetFormat("[<=9999999]###-####;(###) ###-####"));
cell.CellStyle = (/*setter*/cellStyle);
}
{ // SSN format
row = sheet.CreateRow(6);
ICell cell = row.CreateCell(0);
cell.SetCellValue(444551234);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/format.GetFormat("000-00-0000"));
cell.CellStyle = (/*setter*/cellStyle);
}
{ // formula cell
row = sheet.CreateRow(7);
ICell cell = row.CreateCell(0);
cell.SetCellType(CellType.FORMULA);
cell.CellFormula = (/*setter*/"SUM(12.25,12.25)/100");
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/format.GetFormat("##.00%;"));
cell.CellStyle = (/*setter*/cellStyle);
}
}
public void ExportToExcel(int page, int size, int store, string department, string user, string enable)
{
// Get the data to report on
var masters = _service.ListCondition(page, size, store, department, user, enable);
// Create a new workbook
var workbook = new HSSFWorkbook();
#region Cell Styles
#region HeaderLabel Cell Style
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER;
headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerLabelCellStyle.SetFont(headerLabelFont);
#endregion
#region RightAligned Cell Style
var rightAlignedCellStyle = workbook.CreateCellStyle();
rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT;
#endregion
#region Currency Cell Style
var currencyCellStyle = workbook.CreateCellStyle();
currencyCellStyle.Alignment = HorizontalAlignment.RIGHT;
var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
currencyCellStyle.DataFormat = formatId;
#endregion
#region Detail Subtotal Style
var detailSubtotalCellStyle = workbook.CreateCellStyle();
detailSubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailSubtotalFont = workbook.CreateFont();
detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailSubtotalCellStyle.SetFont(detailSubtotalFont);
#endregion
#region Detail Currency Subtotal Style
var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle();
detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailCurrencySubtotalFont = workbook.CreateFont();
detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont);
formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
detailCurrencySubtotalCellStyle.DataFormat = formatId;
#endregion
#endregion
#region Master sheet
var sheet = workbook.CreateSheet("User");
// Add header labels
var rowIndex = 0;
// Undestand as row in excel. row + 3 = xuong 3 row.
var row = sheet.CreateRow(rowIndex);
var cell = row.CreateCell(0);
cell.SetCellValue("User Id");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("User Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("First Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("Last Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("Email");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("Tel.");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("Mobile");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Department");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("Right User");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(9);
cell.SetCellValue("Right Store");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(10);
cell.SetCellValue("Right Project");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(11);
cell.SetCellValue("Right Stock");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(12);
cell.SetCellValue("Right Stock Out");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(13);
cell.SetCellValue("Right Stock In");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(14);
cell.SetCellValue("Right Stock Return");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(15);
cell.SetCellValue("Right Stock-Reactive");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(16);
cell.SetCellValue("Right Service");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(17);
cell.SetCellValue("Right Requisition");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(18);
cell.SetCellValue("Right PE");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(19);
cell.SetCellValue("Right Accounting");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(20);
cell.SetCellValue("Right Supplier");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(21);
cell.SetCellValue("Right Price");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(22);
cell.SetCellValue("Created Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(23);
cell.SetCellValue("Created By");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(24);
cell.SetCellValue("Modified Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(25);
cell.SetCellValue("Modified By");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
// Add data rows
foreach (var master in masters)
{
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(master.Id.ToString());
row.CreateCell(1).SetCellValue(master.UserName);
row.CreateCell(2).SetCellValue(master.FirstName);
row.CreateCell(3).SetCellValue(master.LastName);
row.CreateCell(4).SetCellValue(master.Email);
row.CreateCell(5).SetCellValue(master.Telephone);
row.CreateCell(6).SetCellValue(master.Mobile);
row.CreateCell(7).SetCellValue(master.Department);
row.CreateCell(8).SetCellValue(Constants.Action(master.UserR));
row.CreateCell(9).SetCellValue(Constants.Action(master.StoreR));
row.CreateCell(10).SetCellValue(Constants.Action(master.ProjectR));
row.CreateCell(11).SetCellValue(Constants.Action(master.StockR));
row.CreateCell(12).SetCellValue(Constants.Action(master.StockOutR));
row.CreateCell(13).SetCellValue(Constants.Action(master.StockInR));
row.CreateCell(14).SetCellValue(Constants.Action(master.StockReturnR));
row.CreateCell(15).SetCellValue(Constants.Action(master.ReActiveStockR));
row.CreateCell(16).SetCellValue(Constants.Action(master.StockServiceR));
row.CreateCell(17).SetCellValue(Constants.Action(master.RequisitionR));
row.CreateCell(18).SetCellValue(Constants.Action(master.PER));
row.CreateCell(19).SetCellValue(Constants.Action(master.AccountingR));
row.CreateCell(20).SetCellValue(Constants.Action(master.SupplierR));
row.CreateCell(21).SetCellValue(Constants.Action(master.PriceR));
row.CreateCell(22).SetCellValue(master.Created != null
? master.Created.Value.ToString("dd/MM/yyyy")
: master.Created.ToString());
row.CreateCell(23).SetCellValue(master.CreatedBy.ToString());
row.CreateCell(24).SetCellValue(master.Modified != null
? master.Modified.Value.ToString("dd/MM/yyyy")
: master.Modified.ToString());
row.CreateCell(25).SetCellValue(master.ModifiedBy.ToString());
rowIndex++;
}
// Auto-size each column
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024);
}
// Add row indicating date/time report was generated...
sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy"));
#endregion
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
var saveAsFileName = string.Format("User-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-");
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}
}
public void ExportToExcel(int page, int size, int supplierType, int supplierId, string supplierName, string stockCode, string stockName, int country, int market, string enable)
{
// Get the data to report on
var masters = _service.ListCondition(page, size, supplierType, supplierId, supplierName, stockCode, stockName, country, market, enable);
var details = _service.ListConditionDetailExcel(page, size, supplierType, supplierId, supplierName, stockCode, stockName, country, market, enable);
// Create a new workbook
var workbook = new HSSFWorkbook();
#region Cell Styles
#region HeaderLabel Cell Style
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER;
headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerLabelCellStyle.SetFont(headerLabelFont);
#endregion
#region RightAligned Cell Style
var rightAlignedCellStyle = workbook.CreateCellStyle();
rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT;
#endregion
#region Currency Cell Style
var currencyCellStyle = workbook.CreateCellStyle();
currencyCellStyle.Alignment = HorizontalAlignment.RIGHT;
var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
currencyCellStyle.DataFormat = formatId;
#endregion
#region Detail Subtotal Style
var detailSubtotalCellStyle = workbook.CreateCellStyle();
detailSubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailSubtotalFont = workbook.CreateFont();
detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailSubtotalCellStyle.SetFont(detailSubtotalFont);
#endregion
#region Detail Currency Subtotal Style
var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle();
detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailCurrencySubtotalFont = workbook.CreateFont();
detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont);
formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
detailCurrencySubtotalCellStyle.DataFormat = formatId;
#endregion
#endregion
#region Master sheet
var sheet = workbook.CreateSheet("Supplier");
// Add header labels
var rowIndex = 0;
// Undestand as row in excel. row + 3 = xuong 3 row.
var row = sheet.CreateRow(rowIndex);
var cell = row.CreateCell(0);
cell.SetCellValue("No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("Supplier Id");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("Supplier Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("Type");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("Address");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("City");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("Country");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Phone 1");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("Phone 2");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(9);
cell.SetCellValue("Mobile");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(10);
cell.SetCellValue("Fax");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(11);
cell.SetCellValue("Email");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(12);
cell.SetCellValue("Contact");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(13);
cell.SetCellValue("Created Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(14);
cell.SetCellValue("Created By");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(15);
cell.SetCellValue("Modified Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(16);
cell.SetCellValue("Modified By");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
// Add data rows
var no = 1;
foreach (var master in masters)
{
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(no);
row.CreateCell(1).SetCellValue(master.Id);
row.CreateCell(2).SetCellValue(master.Name);
row.CreateCell(3).SetCellValue(master.Type);
row.CreateCell(4).SetCellValue(master.Address);
row.CreateCell(5).SetCellValue(master.City);
row.CreateCell(6).SetCellValue(master.Country);
row.CreateCell(7).SetCellValue(master.Phone);
row.CreateCell(8).SetCellValue(master.Phone_2);
row.CreateCell(9).SetCellValue(master.Mobile);
row.CreateCell(10).SetCellValue(master.Fax);
row.CreateCell(11).SetCellValue(master.Email);
row.CreateCell(12).SetCellValue(master.Contact);
row.CreateCell(13).SetCellValue(master.Created_Date != null
? master.Created_Date.Value.ToString("dd/MM/yyyy")
: master.Created_Date.ToString());
row.CreateCell(14).SetCellValue(master.Created_By);
row.CreateCell(15).SetCellValue(master.Modified_Date != null
? master.Modified_Date.Value.ToString("dd/MM/yyyy")
: master.Modified_Date.ToString());
row.CreateCell(16).SetCellValue(master.Modified_By);
rowIndex++;
no++;
}
// Auto-size each column
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024);
}
// Add row indicating date/time report was generated...
sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy"));
#endregion
#region Detail sheet
sheet = workbook.CreateSheet("Product");
#region Add header labels
rowIndex = 0;
row = sheet.CreateRow(rowIndex);
cell = row.CreateCell(0);
cell.SetCellValue("Supplier");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("Stock Code");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("Stock Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("Stock Type");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("Unit");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("Category");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("Part No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Ral No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("Color");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
#endregion
// Add data rows
var lastProductName = string.Empty;
// For sum in excel
// var startRowIndexForProductDetails = 1;
foreach (var detail in details)
{
// Show a summary row for each new product
var productNameToShow = string.Empty;
if (string.Compare(detail.Supplier_Name, lastProductName) != 0)
{
if (!string.IsNullOrEmpty(lastProductName))
{
// Add the subtotal row
// AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle);
// rowIndex += 3;
}
productNameToShow = detail.Supplier_Name;
lastProductName = detail.Supplier_Name;
// startRowIndexForProductDetails = rowIndex;
}
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(productNameToShow);
row.CreateCell(1).SetCellValue(detail.Stock_Code);
row.CreateCell(2).SetCellValue(detail.Stock_Name);
row.CreateCell(3).SetCellValue(detail.Type);
row.CreateCell(4).SetCellValue(detail.Unit);
row.CreateCell(5).SetCellValue(detail.Category);
row.CreateCell(6).SetCellValue(detail.Part_No);
row.CreateCell(7).SetCellValue(detail.Ral_No);
row.CreateCell(8).SetCellValue(detail.Color);
rowIndex++;
}
// Add the subtotal row for the last product
// AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle);
// rowIndex += 2;
// Auto-size each column
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024);
}
// Add row indicating date/time report was generated...
// sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy"));
#endregion
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
var saveAsFileName = string.Format("Supplier-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-");
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}
}
/// <summary>
/// DataTable������Excel��MemoryStream
/// </summary>
/// <param name="dtSource">ԴDataTable</param>
/// <param name="strHeaderText">��ͷ�ı�</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region �һ��ļ� ������Ϣ
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "�ļ�������Ϣ"; //���xls�ļ�������Ϣ
si.ApplicationName = "����������Ϣ"; //���xls�ļ�����������Ϣ
si.LastAuthor = "�������Ϣ"; //���xls�ļ��������Ϣ
si.Comments = "������Ϣ"; //���xls�ļ�������Ϣ
si.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");
//ȡ���п�
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 = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
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 = (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);
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 = (HSSFRow)sheet.CreateRow(rowIndex);
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"://��������
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.();
//workbook.Dispose();//һ��ֻ��д��һ����OK�ˣ�����������ͷ�������Դ������ǰ�汾����������ֻ�ͷ�sheet
return ms;
}
}
