/// <summary>
/// 把DataTable 转为Excel 内容
/// </summary>
/// <param name="dt"></param>
/// <param name="startRow"></param>
/// <param name="endRow"></param>
/// <param name="book"></param>
/// <param name="sheetName"></param>
private void DataWrite2Sheet(DataTable dt, int startRow, int endRow, HSSFWorkbook book, string sheetName)
{
//头部样式
CellStyle headstyle = book.CreateCellStyle();
headstyle.Alignment = HorizontalAlignment.CENTER;
headstyle.VerticalAlignment = VerticalAlignment.CENTER;
Font headfont = book.CreateFont();
headfont.Boldweight = 700;
headstyle.SetFont(headfont);
headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
headstyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
headstyle.BorderBottom = CellBorderType.THIN;
headstyle.BorderLeft = CellBorderType.THIN;
headstyle.BorderRight = CellBorderType.THIN;
headstyle.BorderTop = CellBorderType.THIN;
NPOI.SS.UserModel.Sheet sheet = book.CreateSheet(sheetName);
NPOI.SS.UserModel.Row header = sheet.CreateRow(0);
header.Height = 20 * 20;
//表格内容样
CellStyle dataStyle = book.CreateCellStyle();
dataStyle.BorderBottom = CellBorderType.THIN;
dataStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
dataStyle.BorderLeft = CellBorderType.THIN;
dataStyle.BorderRight = CellBorderType.THIN;
dataStyle.BorderTop = CellBorderType.THIN;
for (int i = 0; i < dt.Columns.Count; i++)
{
NPOI.SS.UserModel.Cell cell = header.CreateCell(i);
cell.CellStyle = headstyle;
string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;
cell.SetCellValue(val);
}
int rowIndex = 1;
for (int i = startRow; i <= endRow; i++)
{
DataRow dtRow = dt.Rows[i];
NPOI.SS.UserModel.Row excelRow = sheet.CreateRow(rowIndex++);
for (int j = 0; j < dtRow.ItemArray.Length; j++)
{
excelRow.CreateCell(j).CellStyle = dataStyle;
excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString());
}
}
}
protected void CreateDataHeader(Sheet worksheet)
{
StyleManager.InitStaticStyle();
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8));
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 2));
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 3, 5));
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 6, 8));
int colindex = 0;
NPOI.SS.UserModel.Row header = worksheet.CreateRow(0);
StyleManager.SetCenterBoldStringCell(header.CreateCell(colindex++)).SetCellValue("Daily AR&AP Interface");
header.Height = 450;
NPOI.SS.UserModel.Row header1 = worksheet.CreateRow(1);
colindex = 0;
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue("Newegg.com.cn");
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue("Posting Date: " + end);
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue("MTD( " + begin + " - " + end + " )");
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
NPOI.SS.UserModel.Row header2 = worksheet.CreateRow(2);
colindex = 0;
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Legacy Data");
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Retrieved from SAP Table");
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Balance");
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Legacy Data");
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Retrieved from SAP Table");
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Balance");
NPOI.SS.UserModel.Row header3 = worksheet.CreateRow(3);
colindex = 0;
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("AcctType");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("DocumentType");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("CompanyCode");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(A)");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(B)");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(C=A-B)");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(A)");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(B)");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(C=A-B)");
}
/// <summary>
/// Get a specific cell from a row. If the cell doesn't exist,
/// </summary>
/// <param name="row">The row that the cell is part of</param>
/// <param name="column">The column index that the cell is in.</param>
/// <returns>The cell indicated by the column.</returns>
public static NPOI.SS.UserModel.Cell GetCell(NPOI.SS.UserModel.Row row, int column)
{
NPOI.SS.UserModel.Cell cell = row.GetCell(column);
if (cell == null)
{
cell = row.CreateCell(column);
}
return(cell);
}
protected void CreateDataCells(Sheet worksheet, List <SAPInterfaceExchangeInfo> dataList)
{
ushort currentRowIndex = 4;
ushort currentColIndex = 0;
dataList.ForEach(delegate(SAPInterfaceExchangeInfo item)
{
NPOI.SS.UserModel.Row data = worksheet.CreateRow(currentRowIndex++);
currentColIndex = 0;
StyleManager.SetCenterBoldStringCell(data.CreateCell(currentColIndex++)).SetCellValue(item.AcctTypeDisplay);
StyleManager.SetCenterBoldStringCell(data.CreateCell(currentColIndex++)).SetCellValue(item.DocumentType);
StyleManager.SetCenterBoldStringCell(data.CreateCell(currentColIndex++)).SetCellValue(item.CompanyCode);
StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.Legacy_GLAmount));
StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.SAP_GLAmount));
StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.DateBalance));
StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.MTDData.MTDLegacy_GLAmount));
StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.MTDData.MTDSAP_GLAmount));
StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.MTDData.MTDBalance));
});
}
protected void ExportResult(IList <TransportationBill> tbillList)
{
HSSFWorkbook excel = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = excel.CreateSheet("TransportationBill");
NPOI.SS.UserModel.Row row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("BILL NO");
row.CreateCell(1).SetCellValue("ShipOrderNo");
row.CreateCell(2).SetCellValue("单价");
row.CreateCell(3).SetCellValue("开票数");
row.CreateCell(4).SetCellValue("金额");
int rowNum = 1;
foreach (TransportationBill bill in tbillList)
{
foreach (TransportationBillDetail bd in bill.TransportationBillDetails)
{
NPOI.SS.UserModel.Row _row = sheet.CreateRow(rowNum);
_row.CreateCell(0).SetCellValue(bd.Bill.BillNo);
_row.CreateCell(1).SetCellValue(bd.ActBill.OrderNo);
_row.CreateCell(2).SetCellValue((double)bd.UnitPrice);
_row.CreateCell(3).SetCellValue((double)bd.BilledQty);
_row.CreateCell(4).SetCellValue((double)bd.Amount);
rowNum++;
}
}
MemoryStream ms = new MemoryStream();
excel.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=TBillResult.xls"));
Response.BinaryWrite(ms.ToArray());
excel = null;
ms.Close();
ms.Dispose();
}
/// <summary>
/// Gets the cell to manage it.
/// </summary>
/// <param name="rowPos">The row pos.</param>
/// <param name="columnPos">The column pos.</param>
/// <returns>Cell of the spreadsheet</returns>
public NPOI.SS.UserModel.Cell GetCell(int rowPos, int columnPos)
{
NPOI.SS.UserModel.Row row = _sheet.GetRow(rowPos);
if (row == null)
{
AddRow(rowPos);
row = _sheet.GetRow(rowPos);
}
//Creating the cell
NPOI.SS.UserModel.Cell cell = row.GetCell(columnPos);
if (cell == null)
{
row.CreateCell(columnPos);
cell = row.GetCell(columnPos);
}
return(cell);
}
public override void OutPut(DataTable dt)
{
string fileExt = DateTime.Now.ToString("yyyyMMddHHmmss");
string fileOutPut = base.m_OutputFilePath.Insert(m_OutputFilePath.LastIndexOf("."), fileExt);
string saveFileName = fileOutPut;
//SaveFileDialog saveDialog = new SaveFileDialog();
//saveDialog.DefaultExt = "xls";
//saveDialog.Filter = "Excel文件|*.xls";
//saveDialog.FileName = fileName;
//saveDialog.ShowDialog();
//saveFileName = saveDialog.FileName;
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
Sheet sheet = workbook.CreateSheet("Sheet1");
NPOI.SS.UserModel.Row dataTableName = sheet.CreateRow(0);
NPOI.SS.UserModel.Cell cellTableName = dataTableName.CreateCell(0);
cellTableName.SetCellValue(dt.TableName);
//sheet.SetActiveCellRange(0, 2, 0, dt.Columns.Count);
int rangeID = sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 2, 0, dt.Columns.Count));
NPOI.SS.UserModel.Row dataRowColumnName = sheet.CreateRow(3);
for (int j = 0; j < dt.Columns.Count; j++)
{
NPOI.SS.UserModel.Cell cellName = dataRowColumnName.CreateCell(j);
cellName.SetCellValue(dt.Columns[j].ColumnName);
NPOI.SS.UserModel.Name sheetName = workbook.CreateName();
sheetName.NameName = dt.Columns[j].ColumnName;
sheetName.SheetIndex = 0;
}
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
for (int i = 0; i < rowCount; i++)
{
NPOI.SS.UserModel.Row dataRow = sheet.CreateRow(4 + i);
for (int j = 0; j < colCount; j++)
{
NPOI.SS.UserModel.Cell cell = dataRow.CreateCell(j);
if (dt.Rows[i][j].GetType().Equals(typeof(DateTime)))
{
cell.SetCellValue(((DateTime)(dt.Rows[i][j])).ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
cell.SetCellValue(dt.Rows[i][j].ToString()); //项目序号
}
}
}
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
if (MessageBox.Show("Excel导出成功：" + fileOutPut + "\r\n是否要打开？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
System.Diagnostics.Process.Start(fileOutPut);
}
}
public static void ExportToExcelExtended(DataTable table, string Name)
{
//ExcelHelper excel = new ExcelHelper();
//excel.CreateFile(Name);
//excel.CreateSheet(Name);
InitializeWorkbook();
Sheet sheet = hssfworkbook.CreateSheet("new sheet");
//ExcelDocument document = new ExcelDocument();
//document.UserName = "******";
//document.CodePage = CultureInfo.CurrentCulture.TextInfo.ANSICodePage;
//document.ColumnWidth(0, 120);
//document.ColumnWidth(1, 80);
//document[0, 0].Value = Name;
//document[0, 0].Font = new System.Drawing.Font("Tahoma", 10, System.Drawing.FontStyle.Bold);
//document[0, 0].ForeColor = ExcelColor.DarkRed;
//document[0, 0].Alignment = Alignment.Centered;
//document[0, 0].BackColor = ExcelColor.Silver;
int i = 1; //стрічка
int j = 1; //колонка
NPOI.SS.UserModel.Row header = sheet.CreateRow(0);
foreach (DataColumn column in table.Columns)
{
NPOI.SS.UserModel.Cell cell = header.CreateCell(j);
cell.SetCellValue(column.ColumnName);
j++;
}
j = 1;
foreach (DataRow r in table.Rows)
{
j = 1;
//NPOI.SS.UserModel.Row row = excel.CreateRow(i);
NPOI.SS.UserModel.Row row = sheet.CreateRow(i);
foreach (DataColumn column in table.Columns)
{
NPOI.SS.UserModel.Cell cell = row.CreateCell(j);
Type dataType = column.DataType;
switch (dataType.Name)
{
case "String":
{
cell.SetCellValue(r.ItemArray[j - 1].ToString());
break;
}
case "DateTime":
{
DateTime date = (Convert.ToDateTime(r.ItemArray[j - 1]));
cell.SetCellValue(date);
CellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("yyyyy-MM-dd h:mm:ss");
cell.CellStyle = cellStyle;
break;
}
case "Decimal":
{
if (r.ItemArray[j - 1] != null)
{
if (r.ItemArray[j - 1] != null)
{
if (r.ItemArray[j - 1].ToString().Length > 0)
{
cell.SetCellValue(Convert.ToDouble(r.ItemArray[j - 1]));
}
}
}
break;
}
default:
{
cell.SetCellValue(r.ItemArray[j - 1].ToString());
break;
}
}
//excel.SetCellValue( r.ItemArray[j]
j++;
}
//foreach (object item in r.ItemArray)
//{
// //document.Cell(i,j).Format
// excel.CreateCell(j);
// excel.SetCellValue( item == null ? "" : (item);
// j++;
//}
i++;
}
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.FileName = Name;
saveFileDialog1.DefaultExt = "xls";
saveFileDialog1.ShowDialog();
//FileStream stream = new FileStream(saveFileDialog1.FileName, FileMode.Create);
WriteToFile(saveFileDialog1.FileName);
//System.Diagnostics.Process.Start(saveFileDialog1.FileName);
}
/// <summary>
/// 导出DataTable到Excel文件
/// </summary>
/// <param name="filename">文件名称</param>
/// <param name="dtTable">数据DataTable</param>
/// <returns>文件保存路径</returns>
public string ExportFile(string filename, List <dynamic> data, string webPath = "")
{
NPOI.SS.UserModel.Row rowItem = null;
int rowCnt = 0;
foreach (dynamic dr in data)
{
if (rowCnt == 0)
{
//创建表头
rowItem = _sheet.CreateRow(rowCnt);
int i = 0;
foreach (KeyValuePair <string, object> dynamicItem in dr)
{
if (_colName.ContainsKey(dynamicItem.Key))
{
rowItem.CreateCell(i).SetCellValue(_colName[dynamicItem.Key]);
}
else
{
rowItem.CreateCell(i).SetCellValue(dynamicItem.Key);
}
i++;
}
rowCnt = rowCnt + 1;
}
rowItem = _sheet.CreateRow(rowCnt);
int k = 0;
foreach (KeyValuePair <string, object> dynamicItem in dr)
{
string sItemVal = "";
if (dynamicItem.Value != null)
{
sItemVal = dynamicItem.Value.ToString().Trim();
}
rowItem.CreateCell(k).SetCellValue(sItemVal);
k++;
}
rowCnt = rowCnt + 1;
}
//保存文件
if (webPath == "")
{
webPath = System.Web.HttpContext.Current.Server.MapPath("/");
}
//文件名
string strFileName = string.Format("{0}_{1}_{2}.xls", filename.ToString(),
DateTime.Now.ToString("yyyyMMddHHmm"), CommonLib.Helper.GetRandomNum());
//文件路径
string strFilePath = "\\ExportFile\\" + _className + "\\" + DateTime.Now.ToString("yyyyMM") + "\\";
if (!Directory.Exists(webPath + strFilePath))
{
Directory.CreateDirectory(webPath + strFilePath);
}
var fileInfo = new FileStream(webPath + strFilePath + strFileName, FileMode.Create);
Workbook.Write(fileInfo);
fileInfo.Close();
return(strFilePath + strFileName);
}