using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.IO;
|
using System.Linq;
|
using System.Text;
|
using NPOI.HPSF;
|
using NPOI.HSSF.UserModel;
|
using NPOI.SS.Formula.Functions;
|
using NPOI.SS.UserModel;
|
using NPOI.SS.Util;
|
using HH.WMS.Entitys;
|
|
namespace HH.WMS.Common
|
{
|
public class ExcelHelper
|
{
|
/// <summary>
|
/// 生成Excel
|
/// </summary>
|
/// <param name="title">表头名</param>
|
/// <param name="cellTexts">每列单元格的名称</param>
|
/// <param name="cellWidths">每列单元格的宽度</param>
|
/// <param name="cellValues">对应DataTable中的列名</param>
|
/// <param name="table"></param>
|
/// <returns></returns>
|
public static MemoryStream DataTableToExcel(string title, string[] cellTexts, int[] cellWidths, string[] cellValues, DataTable table)
|
{
|
MemoryStream ms = new MemoryStream();
|
using (table)
|
{
|
//初始化
|
HSSFWorkbook _book = new HSSFWorkbook();
|
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
|
dsi.Company = "汉和";
|
_book.DocumentSummaryInformation = dsi;
|
|
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
|
si.Subject = title;
|
_book.SummaryInformation = si;
|
|
//创建表格
|
ISheet _sheet = _book.CreateSheet(title);
|
|
//第一行:表头
|
IRow _row = _sheet.CreateRow(0);
|
_row.HeightInPoints = 30;
|
|
ICell _cell = _row.CreateCell(0);
|
_cell.SetCellValue(title);
|
|
ICellStyle style = _book.CreateCellStyle();
|
style.Alignment = HorizontalAlignment.Center;
|
IFont font = _book.CreateFont();
|
font.FontHeight = 20 * 20;
|
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; //粗体
|
style.SetFont(font);
|
_cell.CellStyle = style;
|
|
_sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, cellTexts.Length - 1));
|
|
//第二行：字段名称
|
ICellStyle _style = _book.CreateCellStyle();
|
_style.Alignment = HorizontalAlignment.Center;
|
IFont _font = _book.CreateFont();
|
_font.FontHeight = 15 * 15;
|
_font.FontName = "微软雅黑";
|
_style.SetFont(_font);
|
|
_row = _sheet.CreateRow(1);
|
_row.HeightInPoints = 20;
|
|
for (int cellIndex = 0; cellIndex < cellTexts.Length; cellIndex++)
|
{
|
_cell = _row.CreateCell(cellIndex);
|
_cell.SetCellValue(cellTexts[cellIndex]);
|
_cell.CellStyle = _style;
|
_sheet.SetColumnWidth(cellIndex, cellWidths[cellIndex]);
|
}
|
|
for (int i = 0; i < table.Rows.Count; i++)
|
{
|
_row = _sheet.CreateRow(i + 2);
|
_row.HeightInPoints = 20;
|
|
for (int cellIndex = 0; cellIndex < cellTexts.Length; cellIndex++)
|
{
|
_cell = _row.CreateCell(cellIndex);
|
_cell.SetCellValue(table.Rows[i][cellValues[cellIndex]].ToString());
|
}
|
}
|
_book.Write(ms);
|
ms.Flush();
|
}
|
|
return ms;
|
}
|
|
public static MemoryStream ListToExcel<T>(string title, string[] cellTexts, int[] cellWidths, string[] cellValues, List<T> lists)
|
{
|
MemoryStream ms = new MemoryStream();
|
//初始化
|
HSSFWorkbook _book = new HSSFWorkbook();
|
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
|
dsi.Company = "汉和";
|
_book.DocumentSummaryInformation = dsi;
|
|
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
|
si.Subject = title;
|
_book.SummaryInformation = si;
|
|
//创建表格
|
ISheet _sheet = _book.CreateSheet(title);
|
|
//第一行:表头
|
IRow _row = _sheet.CreateRow(0);
|
_row.HeightInPoints = 30;
|
|
ICell _cell = _row.CreateCell(0);
|
_cell.SetCellValue(title);
|
|
ICellStyle style = _book.CreateCellStyle();
|
style.Alignment = HorizontalAlignment.Center;
|
IFont font = _book.CreateFont();
|
font.FontHeight = 20 * 20;
|
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; //粗体
|
style.SetFont(font);
|
_cell.CellStyle = style;
|
|
_sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, cellTexts.Length - 1));
|
|
//第二行：字段名称
|
ICellStyle _style = _book.CreateCellStyle();
|
_style.Alignment = HorizontalAlignment.Center;
|
IFont _font = _book.CreateFont();
|
_font.FontHeight = 15 * 15;
|
_font.FontName = "微软雅黑";
|
_style.SetFont(_font);
|
|
_row = _sheet.CreateRow(1);
|
_row.HeightInPoints = 20;
|
|
for (int cellIndex = 0; cellIndex < cellTexts.Length; cellIndex++)
|
{
|
_cell = _row.CreateCell(cellIndex);
|
_cell.SetCellValue(cellTexts[cellIndex]);
|
_cell.CellStyle = _style;
|
_sheet.SetColumnWidth(cellIndex, cellWidths[cellIndex]);
|
}
|
|
for (int i = 0; i < lists.Count; i++)
|
{
|
_row = _sheet.CreateRow(i + 2);
|
_row.HeightInPoints = 20;
|
|
foreach (var list in lists)
|
{
|
for (int cellIndex = 0; cellIndex < cellTexts.Length; cellIndex++)
|
{
|
_cell = _row.CreateCell(cellIndex);
|
_cell.SetCellValue(GetValue<T>(list, cellValues[cellIndex]));
|
_cell.CellStyle = _style;
|
}
|
|
}
|
|
}
|
_book.Write(ms);
|
ms.Flush();
|
|
return ms;
|
}
|
|
private static string GetValue<T>(T t, string name)
|
{
|
var p = typeof(T).GetProperty(name);
|
object obj = null;
|
if (p != null)
|
{
|
obj = p.GetValue(t, null);
|
return obj != null ? obj.ToString() : "";
|
}
|
else
|
return "";
|
}
|
|
public static OperateResult SaveTemp(dynamic columns,string title,DataTable dt,string fileName)
|
{
|
try
|
{
|
string[] cellTexts = new string[columns.Count];
|
string[] cellValues = new string[columns.Count];
|
int[] cellWidths = new int[columns.Count];
|
|
for (int i = 0; i < columns.Count; i++)
|
{
|
cellTexts[i] = columns[i].n;
|
cellValues[i] = columns[i].f;
|
cellWidths[i] = Convert.ToInt16((columns[i].w == null ? 100 : columns[i].w.Value)) * 50;
|
}
|
|
MemoryStream ms = ExcelHelper.DataTableToExcel(title,
|
cellTexts,
|
cellWidths,
|
cellValues,
|
dt
|
);
|
byte[] buffer;
|
buffer = ms.ToArray();
|
ms.Dispose();
|
string tempPath = Path.GetTempPath() + fileName;
|
using (FileStream fs = new FileStream(tempPath, FileMode.OpenOrCreate, FileAccess.Write))
|
{
|
fs.Write(buffer, 0, buffer.Length);
|
fs.Flush();
|
buffer = null;
|
}
|
return OperateResult.Succeed();
|
}
|
catch (Exception ex)
|
{
|
return OperateResult.Error(ex.Message);
|
}
|
}
|
}
|
}
|
