private void btnExcelExport_Click(object sender, EventArgs e)
{
ManagerInfoBll miBll = new ManagerInfoBll();
var list = miBll.GetList();
dataGridView1.DataSource = list;
//创建Excel工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
//创建单元格样式
ICellStyle cellTitleStyle = workbook.CreateCellStyle();
//设置单元格居中显示
cellTitleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//创建字体
IFont font = workbook.CreateFont();
//设置字体加粗显示
font.IsBold = true;
cellTitleStyle.SetFont(font);
//创建Excel工作表
ISheet sheet = workbook.CreateSheet("管理员");
//创建Excel行
IRow row = sheet.CreateRow(0);
//创建Excel单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(0);
//设置单元格值
cell.SetCellValue("管理员管理");
//设置单元格合并
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3));
cell.CellStyle = cellTitleStyle;
for (int i = 0; i < list.Count; i++)
{
IRow rowDate = sheet.CreateRow(i + 1);
Type t = list[i].GetType();
int count = 0;
foreach (PropertyInfo pi in t.GetProperties())
{
object value = pi.GetValue(list[i]);
string name = pi.Name;
NPOI.SS.UserModel.ICell cellDate = rowDate.CreateCell(count);
if (i == 0)
{
cellDate.SetCellValue(name);
}
else
{
cellDate.SetCellValue(value.ToString());
}
sheet.AutoSizeColumn(count);
count++;
}
}
using (FileStream fs = new FileStream(@"C:\Users\Saber\Desktop\Demo.xlsx", FileMode.OpenOrCreate))
{
workbook.Write(fs);
}
}
/// <summary>
/// 根据DataTable创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByDataTable(DataTable dt)
{
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
//创建以个Sheet
ISheet tb = wk.CreateSheet("Sheet1");
//创建表头(在第0行)
IRow row = tb.CreateRow(0);
#region 根据Datable表头创建Excel表头
for (int i = 0; i < dt.Columns.Count; i++)
{
//创建单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
//cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkRed.Index;
//cell.CellStyle.FillForegroundColor= NPOI.HSSF.Util.HSSFColor.Yellow.Index;
cell.SetCellValue(dt.Columns[i].ColumnName);
tb.AutoSizeColumn(i);//自动调整宽度，貌似对中文支持不好
SetBorder(cell);
}
#endregion
#region 根据DataTable内容创建Excel内容
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow rows = tb.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
SetBorder(cell);
}
}
#endregion
return wk;
}
/// <summary>
/// 赋值单元格
/// </summary>
/// <param name="sheet"></param>
/// <param name="list"></param>
private static void SetPurchaseOrder(ISheet sheet, List <TemplateMode> list)
{
try
{
foreach (var item in list)
{
IRow row = null;
ICell cell = null;
row = sheet.GetRow(item.row);
if (row == null)
{
row = sheet.CreateRow(item.row);
}
cell = row.GetCell(item.cell);
if (cell == null)
{
cell = row.CreateCell(item.cell);
}
cell.SetCellValue(item.value);
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 根据DataTable创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="columnName">表头</param>
/// <param name="SheetName">表名称</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByDataTable(DataTable dt, string[] columnName, string SheetName = "Sheet1")
{
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
//创建以个Sheet
ISheet tb = wk.CreateSheet(SheetName);
//创建表头(在第0行)
IRow row = tb.CreateRow(0);
#region 表头根据参数
for (int i = 0; i < columnName.Length; i++)
{
//创建单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
cell.SetCellValue(columnName[i]);
cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;
SetBorder(cell);
}
#endregion
#region 根据DataTable内容创建Excel内容
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow rows = tb.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j >= columnName.Length)
break;
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
SetBorder(cell);
}
}
#endregion
return wk;
}
void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
}
/// <summary>
/// render datatable to workbook stream
/// </summary>
/// <param name="sourceTable">datatable</param>
/// <returns>workbook stream</returns>
public static Stream RenderDataTableToExcel(DataTable sourceTable)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
//設定 Header Style
HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headerStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
headerStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
headerStyle.BorderTop = headerStyle.BorderLeft;
headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HSSFFont headerFont = (HSSFFont)workbook.CreateFont();
headerFont.Color = HSSFColor.White.Index;
headerFont.Boldweight = 1;
headerStyle.SetFont(headerFont);
// handling header.
for (int i = 0; i < sourceTable.Columns.Count; i++)
{
DataColumn column = sourceTable.Columns[i];
NPOI.SS.UserModel.ICell headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = headerStyle;
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
/// <summary>
/// 读取模板，根据List(list(string))"创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="columnName">表头</param>
/// <param name="columnWidth">列宽度*256</param>
/// <param name="ModUrl">模板地址</param>
/// <param name="startRowIndex">起始行,首行=0，默认0</param>
/// <param name="RemoveOtherRows">是否移除起始行后的数据，默认=是</param>
/// <param name="SheetName">表名称</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByList_Mod(System.Collections.Generic.List <System.Collections.Generic.List <object> > dt, string[] columnName, int[] columnWidth, string ModUrl, int startRowIndex = 0, bool RemoveOtherRows = true, string SheetName = "Sheet1")
{
startRowIndex = startRowIndex >= 0 ? startRowIndex : 0;
string ExcelModePath = NodeBase + ModUrl;
if (!File.Exists(ExcelModePath))
{
return(null);
}
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
using (FileStream fileExcelMod = new FileStream(ExcelModePath, FileMode.Open, FileAccess.Read))
{
wk = new XSSFWorkbook(fileExcelMod);
fileExcelMod.Close();
}
if (wk == null)
{
return(null);
}
//创建一个Sheet
ISheet tb = wk.GetSheetAt(0);
#region 移除起始行后的所有行
if (RemoveOtherRows && tb.LastRowNum > startRowIndex + 1)
{
for (int rmr = tb.LastRowNum - 1; rmr > startRowIndex; rmr--)
{
tb.ShiftRows(rmr, rmr + 1, -1);
}
}
#endregion
//设置单元格宽度
//创建表头(在第0行)
#region 根据List<List<object>>内容创建Excel内容
for (int i = 0; i < dt.Count; i++)
{
IRow rows = tb.CreateRow(i + startRowIndex);
for (int j = 0; j < dt[i].Count; j++)
{
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt[i][j].ToString());
SetBorder(cell);
}
}
#endregion
return(wk);
}
private static void SetCell(ICell newCell, ICellStyle dateStyle, Type dataType, string drValue)
{
switch (dataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
if (System.DateTime.TryParse(drValue, out dateV))
{
newCell.SetCellValue(dateV);
}
else
{
newCell.SetCellValue("");
}
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;
}
}
/// <summary>
/// Creates a cell, gives it a value, and applies a style if provided
/// </summary>
/// <param name="row">the row to Create the cell in</param>
/// <param name="column">the column index to Create the cell in</param>
/// <param name="value">The value of the cell</param>
/// <param name="style">If the style is not null, then Set</param>
/// <returns>A new HSSFCell</returns>
public static NPOI.SS.UserModel.ICell CreateCell(NPOI.SS.UserModel.IRow row, int column, String value, HSSFCellStyle style)
{
NPOI.SS.UserModel.ICell cell = GetCell(row, column);
cell.SetCellValue(new HSSFRichTextString(value));
if (style != null)
{
cell.CellStyle = (style);
}
return(cell);
}
private static void CopyCell(NPOI.SS.UserModel.ICell sCell, NPOI.SS.UserModel.ICell dCell, List <ICellStyle> dCellStyles, List <IFont> dFonts)
{
ICellStyle currCellStyle = dCell.Sheet.Workbook.FindStyle(sCell.Sheet.Workbook, sCell.CellStyle, dCellStyles, dFonts);
if (currCellStyle == null)
{
currCellStyle = dCell.Sheet.Workbook.CreateCellStyle().CopyStyle(sCell.CellStyle, dCell.Sheet.Workbook, sCell.Sheet.Workbook, dCellStyles, dFonts);
}
dCell.CellStyle = currCellStyle;
switch (sCell.CellType)
{
case CellType.String:
dCell.SetCellValue(sCell.StringCellValue);
break;
case CellType.Numeric:
dCell.SetCellValue(sCell.NumericCellValue);
break;
case CellType.Blank:
dCell.SetCellType(CellType.Blank);
break;
case CellType.Boolean:
dCell.SetCellValue(sCell.BooleanCellValue);
break;
case CellType.Error:
dCell.SetCellValue(sCell.ErrorCellValue);
break;
case CellType.Formula:
dCell.SetCellValue(sCell.CellFormula);
break;
default:
break;
}
}
//protected void Button1_Click(object sender, EventArgs e)
//{
// using (MemoryStream ms = Export())
// {
// Response.ContentType = "application/vnd.ms-word";
// Response.ContentEncoding = Encoding.UTF8;
// Response.Charset = "";
// Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("123.doc", Encoding.UTF8));
// Response.BinaryWrite(Export().GetBuffer());
// Response.End();
// }
//}
//private void ExportExcel(string fileName)
//{
// //byte[] byteArray;
// //if (Request.Browser.Browser == "IE")
// // eFilePath = HttpUtility.UrlEncode(eFilePath);
// //using (FileStream fs = new FileStream(eFilePath, FileMode.Open))
// //{
// // byteArray = new byte[fs.Length];
// // fs.Read(byteArray, 0, byteArray.Length);
// //}
// //Response.Buffer = false;
// //Response.Clear();
// //Response.ContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document; name=" + eFilePath;
// //Response.AddHeader("content-disposition", "attachment;filename=" + fileName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx");
// //Response.BinaryWrite(byteArray);
// //Response.End();
//}
public static MemoryStream ExportExcel(string templateFileName)
{
string filePath = templateFileName;
XSSFWorkbook workBook = null;
XSSFSheet sheet1 = null;
using (FileStream fs = File.OpenRead(filePath))
{
workBook = new XSSFWorkbook(fs);
sheet1 = (XSSFSheet)workBook.GetSheet("Sheet1");
//添加或修改WorkSheet里的数据
System.Data.DataTable dt = new System.Data.DataTable();
//dt = DbHelperMySQLnew.Query("select * from t_jb_info where id='" + id + "'").Tables[0];
//if (dt.Rows.Count > 0)
//{
// if (!string.IsNullOrEmpty(dt.Rows[0]["blrq"].ToString()))
// {
//sheet.GetRow(2).GetCell(1).SetCellValue("56565");
//sheet.GetRow(2).GetCell(2).SetCellValue("hahaha");
//sheet.GetRow(2).GetCell(3).SetCellValue(DateTime.Now.ToString());
// }
//}
// 创建新增行
for (var i = 1; i <= 10; i++)
{
IRow row1 = sheet1.CreateRow(i);
for (var j = 0; j < 10; j++)
{
//新建单元格
NPOI.SS.UserModel.ICell cell = row1.CreateCell(j);
// 单元格赋值
cell.SetCellValue("");
}
}
sheet1.GetRow(1).GetCell(0).SetCellValue("56565");
sheet1.GetRow(1).GetCell(1).SetCellValue("hahaha");
sheet1.GetRow(1).GetCell(2).SetCellValue(DateTime.Now.ToString());
sheet1.ForceFormulaRecalculation = true;
}
using (MemoryStream ms = new MemoryStream())
{
workBook.Write(ms);
return(ms);
}
}
public static void DataTableToExcel(string sheetName, DataTable dt, ExcelExt excelExt, Stream outStream)
{
try
{
NPOI.SS.UserModel.IWorkbook book = null;
if (excelExt == ExcelExt.Xls)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.ColumnName);
index++;
}
// 添加数据
int num = dt.Rows.Count;
for (int i = 0; i < num; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
book.Write(outStream);
book = null;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
/// <summary>
/// 作用：填写时间：
/// 作者：汪建龙
/// 编写时间：2017年3月8日15:48:22
/// </summary>
/// <param name="sheet"></param>
private static void WriteTime(ref ISheet sheet)
{
IRow row = sheet.GetRow(0);
if (row == null)
{
row = sheet.CreateRow(0);
}
NPOI.SS.UserModel.ICell cell = row.GetCell(3);
if (cell == null)
{
cell = row.CreateCell(3);
}
cell.SetCellValue(DateTime.Now.ToString("yyyy-MM-dd"));
}
/// <summary>
/// 根据List(list(string))"创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="columnName">表头</param>
/// <param name="columnWidth">列宽度*256</param>
/// <param name="SheetName">表名称</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByList(System.Collections.Generic.List <System.Collections.Generic.List <object> > dt, string[] columnName, int[] columnWidth, string SheetName = "Sheet1")
{
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
//创建以个Sheet
ISheet tb = wk.CreateSheet(SheetName);
//设置单元格宽度
for (int i = 0; i < columnWidth.Length; i++)
{
tb.SetColumnWidth(i, columnWidth[i] * 256);
}
//创建表头(在第0行)
IRow row = tb.CreateRow(0);
#region 表头根据参数
for (int i = 0; i < columnName.Length; i++)
{
//创建单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
cell.SetCellValue(columnName[i]);
//背景
//cell.CellStyle.FillForegroundColor = NPOI.XSSF.Util.XSSFColor.DarkTeal.Index;
//cell.CellStyle.FillPattern = FillPattern.SolidForeground;
//边框颜色
//cell.CellStyle.TopBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
//cell.CellStyle.LeftBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
//cell.CellStyle.BottomBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
//cell.CellStyle.RightBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
cell.CellStyle.IsLocked = true;
SetBorder(cell);
}
#endregion
#region 根据List<List<object>>内容创建Excel内容
for (int i = 0; i < dt.Count; i++)
{
IRow rows = tb.CreateRow(i + 1);
for (int j = 0; j < dt[i].Count; j++)
{
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt[i][j].ToString());
SetBorder(cell);
}
}
#endregion
return(wk);
}
/// <summary>
/// 將多個DataTable寫入ExcelFile並以Stream輸出.
/// </summary>
/// <param name="templateFileName">範本檔檔名</param>
/// <param name="sourceTables">source table.</param>
/// <param name="startRows">指定由範本檔第幾列開始附加資料</param>
/// <returns></returns>
public static Stream RenderDataTableToExcelSheet(string templateFileName, DataTable[] sourceTables, int[] startRows)
{
HSSFWorkbook workbook = FileToWorkBook(templateFileName);;
MemoryStream ms = new MemoryStream();;
HSSFSheet sheet = null;
for (int idx = 0; idx < sourceTables.Length; idx++)
{
sheet = (HSSFSheet)workbook.GetSheetAt(idx);
DataTable sourceTable = sourceTables[idx];
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyle.BorderTop = cellStyle.BorderLeft;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.WrapText = true;
// handling value.
int rowIndex = startRows[idx];
foreach (DataRow row in sourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(column.Ordinal);
cell.SetCellValue(row[column].ToString());
cell.CellStyle = cellStyle;
}
rowIndex++;
}
sheet = null;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
return(ms);
}
public void ExportExcel(string fileName, GridView dgv)
{
string saveFileName = DateTime.Now.ToString();;
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();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Sheet1");
int rowCount = dgv.Rows.Count;
int colCount = dgv.Columns.Count;
for (int i = 0; i < rowCount; i++)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(i);
for (int j = 0; j < colCount; j++)
{
if (dgv.Columns[j].Visible && dgv.Rows[i].Cells[j] != null)
{
NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(j);
cell.SetCellValue(dgv.Rows[i].Cells[j].ToString());
}
}
}
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
MessageBox.Show(fileName + " 保存成功", "提示", MessageBoxButtons.OK);
}
/// <summary>
/// 创建excel表头
/// </summary>
/// <param name="dgv"></param>
/// <param name="excelSheet"></param>
protected static void CreateHeader(NPOI.SS.UserModel.ISheet excelSheet, HSSFWorkbook excelWorkbook)
{
int cellIndex = 0;
NPOI.SS.UserModel.IRow row1 = excelSheet.CreateRow(0);
NPOI.SS.UserModel.ICellStyle cellStyle = excelWorkbook.CreateCellStyle();
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index;
//cellStyle.FillPattern = HSSFCellStyle.SQUARES;
cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
//循环导出列
foreach (System.Collections.DictionaryEntry de in _listColumnsName)
{
NPOI.SS.UserModel.ICell cell = row1.CreateCell(cellIndex);
cell.CellStyle = cellStyle;
string cellName = de.Value.ToString().Trim();
cell.SetCellValue(cellName);
excelSheet.SetColumnWidth(cellIndex, 15 * 256);
cellIndex++;
}
}
private void button1_Click(object sender, EventArgs e)
{
IWorkbook xssfwb;
string numer;
string nrSprawy;
using (FileStream file = new FileStream(sciezka + "ListaNumerow.xlsx", FileMode.Open, FileAccess.Read))
{
xssfwb = new XSSFWorkbook(file);
}
ISheet sheet = xssfwb.GetSheet("Arkusz1");
for (int row = 1; row <= 5; row++) //sheet.LastRowNum; row++)
{
if (sheet.GetRow(row) != null)
{
// MessageBox.Show(string.Format("Row {0} = {1}", row, sheet.GetRow(row).GetCell(6).StringCellValue));
IRow wiersz = sheet.GetRow(row);
numer = wiersz.GetCell(0).StringCellValue;
nrSprawy = wiersz.GetCell(1).StringCellValue.Replace('/', '_');
ZapiszPNG(numer);
zapiszPlikWord(nrSprawy, numer);
NPOI.SS.UserModel.ICell cell = wiersz.GetCell(9) ?? wiersz.CreateCell(9, CellType.String);
cell.SetCellValue("Zapisano");
}
}
using (FileStream file = new FileStream(sciezka + "ListaNumerow.xlsx", FileMode.Create, FileAccess.Write))
{
xssfwb.Write(file);
}
}
/**
* Called to update the embedded Excel workbook. As the format and structire
* of the workbook are known in advance, all this code attempts to do is
* write a new value into the first cell on the first row of the first
* worksheet. Prior to executing this method, that cell will contain the
* value 1.
*
* @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException
* Rather
* than use the specific classes (HSSF/XSSF) to handle the embedded
* workbook this method uses those defeined in the SS stream. As
* a result, it might be the case that a SpreadsheetML file is
* opened for processing, throwing this exception if that file is
* invalid.
* @throws java.io.IOException Thrown if a problem occurs in the underlying
* file system.
*/
public void UpdateEmbeddedDoc1()
{
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
NPOI.SS.UserModel.ICell cell = null;
PackagePart pPart = null;
IEnumerator <PackagePart> pIter = null;
List <PackagePart> embeddedDocs = this.doc.GetAllEmbedds();
if (embeddedDocs != null && embeddedDocs.Count != 0)
{
pIter = embeddedDocs.GetEnumerator();
while (pIter.MoveNext())
{
pPart = pIter.Current;
if (pPart.PartName.Extension.Equals(BINARY_EXTENSION) ||
pPart.PartName.Extension.Equals(OPENXML_EXTENSION))
{
// Get an InputStream from the pacage part and pass that
// to the create method of the WorkbookFactory class. Update
// the resulting Workbook and then stream that out again
// using an OutputStream obtained from the same PackagePart.
workbook = WorkbookFactory.Create(pPart.GetInputStream());
sheet = workbook.GetSheetAt(SHEET_NUM);
row = sheet.GetRow(ROW_NUM);
cell = row.GetCell(CELL_NUM);
cell.SetCellValue(NEW_VALUE);
workbook.Write(pPart.GetOutputStream());
}
}
// Finally, write the newly modified Word document out to file.
string file = Path.GetFileNameWithoutExtension(this.docFile) + "tmp" + Path.GetExtension(this.docFile);
this.doc.Write(new FileStream(file, FileMode.CreateNew));
}
}
/// <summary>
/// 將DataTable轉成Stream輸出.
/// </summary>
/// <param name="sourceTable">The source table.</param>
/// <param name="templateFileName">範本檔檔名</param>
/// <param name="startRows">指定由範本檔第幾列開始附加資料</param>
/// <returns></returns>
public static Stream RenderDataTableToExcel(DataTable sourceTable, string templateFileName, int startRows)
{
HSSFWorkbook workbook = FileToWorkBook(templateFileName);
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyle.WrapText = true;
// handling value.
int rowIndex = startRows;
foreach (DataRow row in sourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(column.Ordinal);
cell.SetCellValue(row[column].ToString());
cell.CellStyle = cellStyle;
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
workbook = null;
return(ms);
}
/// <summary>
/// return File(bs, "application/vnd.ms-excel");
/// </summary>
/// <param name="tb"></param>
/// <returns></returns>
public static byte[] DataTableToExcel(DataTable tb)
{
if (tb == null)
{
return(null);
}
using (var ms = new MemoryStream())
{
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
var sheet = workbook.CreateSheet(ValidateHelper.IsPlumpString(tb.TableName) ? tb.TableName : "sheet");
var style = GetStyle(workbook,
NPOI.HSSF.Util.HSSFColor.Red.Index, NPOI.HSSF.Util.HSSFColor.White.Index);
NPOI.SS.UserModel.IRow row = null;
NPOI.SS.UserModel.ICell cell = null;
for (int i = 0; i < tb.Rows.Count; ++i)
{
row = sheet.CreateRow(i);
for (int j = 0; j < tb.Columns.Count; ++j)
{
cell = row.CreateCell(j);
cell.SetCellValue(ConvertHelper.GetString(tb.Rows[i][j]));
cell.CellStyle = style;
}
}
workbook.Write(ms);
workbook.Clear();
tb.Clear();
var bs = ms.ToArray();
return(bs);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string passaord = null)
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
#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
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
NPOI.SS.UserModel.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;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
}
#region 表头及样式
{
if (string.IsNullOrEmpty(strHeaderText) == false)
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.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));
rowIndex += 1;
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.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();
rowIndex += 1;
}
#endregion
}
#endregion
#region 填充内容
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
NPOI.SS.UserModel.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>
/// DataTable导出到Excel的MemoryStream xlsx
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">文件名</param>
public static MemoryStream ExportXlsx(IEnumerable <DataTable> dataTables, string strHeaderText, string passaord = null)
{
XSSFWorkbook workbook = new XSSFWorkbook();
int i = 0;
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 右击文件 属性信息
{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "NPOI";
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "文件作者信息"; //填加xlsx文件作者信息
// si.ApplicationName = "创建程序信息"; //填加xlsx文件创建程序信息
// si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
// si.Comments = "作者信息"; //填加xls文件作者信息
// si.Title = "标题信息"; //填加xls文件标题信息
// si.Subject = "主题信息";//填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.GetProperties().CustomProperties.AddProperty("Company", "NPOI");
// if (!workbook.GetProperties().CustomProperties.Contains("Company"))
// workbook.GetProperties().CustomProperties.AddProperty("Company", dsi.Company);
}
#endregion
foreach (DataTable dt in dataTables)
{
string sheetName = string.IsNullOrEmpty(dt.TableName)
? "Sheet " + (++i).ToString()
: dt.TableName;
ISheet sheet = workbook.CreateSheet(sheetName);
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
int rowIndex = 0;
#region 表头及样式
{
if (string.IsNullOrEmpty(strHeaderText) == false)
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.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, dt.Columns.Count - 1));
rowIndex += 1;
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
//取得列宽
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 k = 0; k < dt.Rows.Count; k++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[k][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
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();
rowIndex += 1;
}
#endregion
//#region 表头
//for (int j = 0; j < dt.Columns.Count; j++)
//{
// string columnName = string.IsNullOrEmpty(dt.Columns[j].ColumnName)
// ? "Column " + j.ToString()
// : dt.Columns[j].ColumnName;
// headerRow.CreateCell(j).SetCellValue(columnName);
//}
//#endregion
#region 内容
for (int a = 0; a < dt.Rows.Count; a++)
{
DataRow dr = dt.Rows[a];
IRow row = sheet.CreateRow(a + rowIndex);
for (int b = 0; b < dt.Columns.Count; b++)
{
row.CreateCell(b).SetCellValue(dr[b] != DBNull.Value ? dr[b].ToString() : string.Empty);
DataColumn dc = dt.Columns[b];
NPOI.SS.UserModel.ICell newCell = row.CreateCell(dc.Ordinal);
string drValue = dr[b].ToString();
switch (dc.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
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
// ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
//Datatable导出Excel
public static void GridToExcelByNPOI(DataTable dt, string strExcelFileName, string title)
{
HSSFWorkbook workbook = new HSSFWorkbook();
try
{
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//标题头
int icolIndex = 0;
CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count > 0 ? dt.Columns.Count - 1 : 0);
sheet.AddMergedRegion(region);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 20;
NPOI.SS.UserModel.ICell celltitle = headerRow.CreateCell(0);
celltitle.SetCellValue(title);
celltitle.CellStyle = HeadercellStyle;
//用column name 作为列名
IRow headerRow1 = sheet.CreateRow(1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell1 = headerRow1.CreateCell(icolIndex);
cell1.SetCellValue(item.ColumnName);
cell1.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 2;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//写Excel
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
workbook = null;
}
}
/// <summary>
/// 向指定的行和列的单元格写值
/// </summary>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="value">值</param>
/// <returns></returns>
private bool setData(int rowIndex, int columnIndex, string value)
{
rowIndex--;
columnIndex--;
NPOI.SS.UserModel.IRow row = null;
if (xlSheet.GetRow(rowIndex) != null)
{
row = xlSheet.GetRow(rowIndex);
}
else
{
row = xlSheet.CreateRow(rowIndex);
}
/*
* if (xlSheet.LastRowNum == 0)
* {
* row = xlSheet.CreateRow(0);
* }
* else
* {
* if (xlSheet.LastRowNum >= rowIndex)
* {
* if (xlSheet.GetRow(rowIndex) != null)
* {
* row = xlSheet.GetRow(rowIndex);
* }
* else
* {
* row = xlSheet.CreateRow(rowIndex);
* }
* }
* else
* {
* row = xlSheet.CreateRow(rowIndex);
* }
* }
* */
NPOI.SS.UserModel.ICell cell = null;
if (row.GetCell(columnIndex) != null)
{
cell = row.GetCell(columnIndex);
}
else
{
cell = row.CreateCell(columnIndex);
}
/*
* if (row.LastCellNum >= columnIndex)
* {
* if (row.GetCell(columnIndex) != null)
* {
* cell = row.GetCell(columnIndex);
* }
* else
* {
* cell = row.CreateCell(columnIndex);
* }
*
* }
* else
* {
* cell = row.CreateCell(columnIndex);
* }
* */
cell.SetCellValue(value);
return(true);
}
/// <summary>
/// MLR將DataTable轉成Stream輸出.
/// </summary>
/// <param name="sourceTable">The source table.</param>
/// <param name="templateFileName">範本檔檔名</param>
/// <param name="startRows">指定由範本檔第幾列開始附加資料</param>
/// <param name="tableId">MLR TableID</param>
/// <returns></returns>
public static Stream RenderDataTableToExcelForMLR(DataTable sourceTable, string templateFileName, int startRows, string tableId)
{
HSSFWorkbook workbook = FileToWorkBook(templateFileName);
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.WrapText = true;
//Setting TableId
HSSFRow fRow = (HSSFRow)sheet.CreateRow(0);
NPOI.SS.UserModel.ICell firstCell = fRow.CreateCell(0);
firstCell.SetCellValue(tableId);
firstCell.CellStyle = cellStyle;
// handling value.
int rowIndex = startRows;
foreach (DataRow row in sourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(column.Ordinal);
cell.SetCellValue(row[column].ToString());
cell.CellStyle = cellStyle;
}
rowIndex++;
}
ISheet sheet1 = (ISheet)workbook.GetSheetAt(0);
List <string> merged = new List <string>();
for (int i = 0; i < sourceTable.Rows.Count; i++)
{
string seq = sourceTable.Rows[i]["MASK_TOOLING_SEQUENCE"].ToString();
var query = sourceTable.Select(string.Format("MASK_TOOLING_SEQUENCE = '{0}'", seq));
int count = query.Count();
if (count > 1)
{
if (!merged.Contains(seq))
{
merged.Add(seq);
int firstRow = startRows + i;
int lastRow = startRows + i + count - 1;
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, 0, 0));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, 3, 3));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, 8, 8));
}
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
workbook = null;
return(ms);
}
public void CreateExcel()
{
IWorkbook workbook = new XSSFWorkbook();
try
{
ISheet sheet = workbook.CreateSheet(SheetName);
ICellStyle cellStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
var RowIter = 0;
var ColIter = 0;
// Header Line - Food
var row = sheet.CreateRow(RowIter);
ColIter = 0;
row.CreateCell(ColIter).SetCellValue("Food");
RowIter++;
// Data Lines - Food
row = sheet.CreateRow(RowIter);
ColIter = 0;
dicListDate.TryGetValue("Food", out List <string> ListDate);
foreach (var val in ListDate)
{
DateTime t = ParseStringToDateTime(val);
NPOI.SS.UserModel.ICell cell = row.CreateCell(ColIter);
cell.SetCellValue(t);
cell.CellStyle = cellStyle;
ColIter++;
}
RowIter++;
row = sheet.CreateRow(RowIter);
ColIter = 0;
dicListFpi.TryGetValue("Food", out List <double> ListFpi);
foreach (var val in ListFpi)
{
row.CreateCell(ColIter).SetCellValue(val);
ColIter++;
}
RowIter++;
foreach (KeyValuePair <string, List <double> > entry
in dicListFpi.OrderBy(o => o.Key).ToDictionary(o => o.Key, p => p.Value))
{
if (entry.Key == "Food")
{
continue;
}
// Header Line - Except Food
row = sheet.CreateRow(RowIter);
ColIter = 0;
row.CreateCell(ColIter).SetCellValue(entry.Key);
RowIter++;
// Data Lines - Except Food
row = sheet.CreateRow(RowIter);
ColIter = 0;
foreach (var val in entry.Value)
{
DateTime t = ParseStringToDateTime(dicListDate[entry.Key][ColIter]);
NPOI.SS.UserModel.ICell cell = row.CreateCell(ColIter);
cell.SetCellValue(t);
cell.CellStyle = cellStyle;
//row.CreateCell(ColIter).SetCellValue(t);
ColIter++;
}
RowIter++;
row = sheet.CreateRow(RowIter);
ColIter = 0;
foreach (var val in entry.Value)
{
row.CreateCell(ColIter).SetCellValue(val);
ColIter++;
}
RowIter++;
}
// Save Workbook
FileInfo FI = new FileInfo(FilePath);
FI.Directory.Create(); // If the directory already exists, this method does nothing.
FileStream file = new FileStream(FilePath, FileMode.Create);
workbook.Write(file);
file.Close();
}
catch
{
throw;
}
finally
{
workbook.Close();
}
}
public static void ExportCommonGridtoExcel(System.Windows.Forms.DataGridView grid, string fileName)
{
if (grid == null || grid.DataSource == null ||
grid.Rows.Count == 0 || grid.Columns.Count == 0 ||
string.IsNullOrEmpty(fileName))
{
return;
}
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Sheet1");
int colCount = 0;
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < grid.Columns.Count; i++)
{
if (grid.Columns[i].Visible)
{
NPOI.SS.UserModel.ICell cell = headerRow.CreateCell(colCount++, NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(grid.Columns[i].HeaderText);
}
}
int rowCount = 1;
for (int i = 0; i < grid.Rows.Count; i++)
{
colCount = 0;
if (grid.Rows[i].Visible)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowCount++);
for (int j = 0; j < grid.Columns.Count; j++)
{
if (grid.Columns[j].Visible)
{
NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(colCount++, NPOI.SS.UserModel.CellType.String);
if (grid.Rows[i].Cells[j] != null && grid.Rows[i].Cells[j].Value != null)
{
cell.SetCellValue(grid.Rows[i].Cells[j].Value.ToString());
}
else
{
cell.SetCellValue(string.Empty);
}
}
}
}
}
for (int i = 0; i < grid.Columns.Count; i++)
{
if (grid.Columns[i].Visible)
{
sheet.AutoSizeColumn(i);
}
}
workbook.Write(ms);
FileStream file = new FileStream(fileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
}
catch (Exception)
{
throw;
}
}
/// <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, NPOI.SS.UserModel.IRow currentExcelRow, NPOI.SS.UserModel.ISheet excelSheet, HSSFWorkbook excelWorkBook, NPOI.SS.UserModel.ICellStyle cellStyle_DateTime)
{
for (int cellIndex = 0; cellIndex < _listColumnsName.Count; cellIndex++)
{
//列名称
string columnsName = _listColumnsName.GetKey(cellIndex).ToString();
NPOI.SS.UserModel.ICell 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);
//格式化显示
newCell.CellStyle = cellStyle_DateTime;
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;
case "System.Guid": //空值处理
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
default:
throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
}
}
}
public static void ExportCommonGridtoExcel(System.Windows.Forms.DataGridView grid, string sheetName, out string fileName)
{
fileName = "";
if (grid == null || grid.DataSource == null || grid.Rows.Count == 0 || grid.Columns.Count == 0)
{
return;
}
System.Windows.Forms.SaveFileDialog sfDialog = new System.Windows.Forms.SaveFileDialog();
sfDialog.Filter = "Excel files(*.xls)|*.xls";
sfDialog.FilterIndex = 1;
sfDialog.DefaultExt = ".xls";
//sfDialog.InitialDirectory = Environment.CurrentDirectory;
sfDialog.RestoreDirectory = true;
if (sfDialog.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return;
}
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(sheetName) ? "Sheet1" : sheetName);
int colCount = 0;
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < grid.Columns.Count; i++)
{
if (grid.Columns[i].Visible)
{
NPOI.SS.UserModel.ICell cell = headerRow.CreateCell(colCount++, NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(grid.Columns[i].HeaderText);
}
}
int rowCount = 1;
for (int i = 0; i < grid.Rows.Count; i++)
{
colCount = 0;
if (grid.Rows[i].Visible)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowCount++);
for (int j = 0; j < grid.Columns.Count; j++)
{
if (grid.Columns[j].Visible)
{
NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(colCount++, NPOI.SS.UserModel.CellType.String);
if (grid.Rows[i].Cells[j] != null && grid.Rows[i].Cells[j].Value != null)
{
cell.SetCellValue(grid.Rows[i].Cells[j].Value.ToString());
}
else
{
cell.SetCellValue(string.Empty);
}
}
}
}
}
for (int i = 0; i < grid.Columns.Count; i++)
{
if (grid.Columns[i].Visible)
{
sheet.AutoSizeColumn(i);
}
}
workbook.Write(ms);
FileStream file = new FileStream(sfDialog.FileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
fileName = sfDialog.FileName;
}
catch (Exception)
{
throw;
}
}