private void buildWorkbook(IWorkbook wb)
{
ISheet sh = wb.CreateSheet();
IRow row1 = sh.CreateRow(0);
IRow row2 = sh.CreateRow(1);
row3 = sh.CreateRow(2);
row1.CreateCell(0, CellType.Numeric);
row1.CreateCell(1, CellType.Numeric);
row2.CreateCell(0, CellType.Numeric);
row2.CreateCell(1, CellType.Numeric);
row3.CreateCell(0);
row3.CreateCell(1);
CellReference a1 = new CellReference("A1");
CellReference a2 = new CellReference("A2");
CellReference b1 = new CellReference("B1");
CellReference b2 = new CellReference("B2");
sh.GetRow(a1.Row).GetCell(a1.Col).SetCellValue(35);
sh.GetRow(a2.Row).GetCell(a2.Col).SetCellValue(0);
sh.GetRow(b1.Row).GetCell(b1.Col).CellFormula = (/*setter*/"A1/A2");
sh.GetRow(b2.Row).GetCell(b2.Col).CellFormula = (/*setter*/"NA()");
Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
}
internal static ICell CreateCell(IWorkbook workbook, IRow row, int column, decimal? content, bool isCentered)
{
ICellStyle style = workbook.CreateCellStyle();
ICell cell = row.CreateCell(column);
if (content == null)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(Convert.ToDouble(content.Value));
}
if (isCentered)
{
style.Alignment = HorizontalAlignment.Center;
}
style.BorderBottom = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
cell.CellStyle = style;
return cell;
}
private static int InsertCell(IRow row, int cellIndex, string value, ICellStyle cellStyle)
{
var cell = row.CreateCell(cellIndex);
cell.SetCellValue(value);
cell.CellStyle = cellStyle;
cellIndex++;
return cellIndex;
}
public void CreateCells(int cellsCount, ref IRow row, IList<string> cellsValues = null)
{
//var isEmptyCells = cellsValues == null || !cellsValues.Any() || cellsValues.Count != cellsCount;
//if (isEmptyCells)
// for (var j = 0; j < cellsCount; j++)
// row.CreateCell(j).SetCellValue(string.Empty);
//else
for (var j = 0; j < cellsCount; j++)
row.CreateCell(j).SetCellValue("cell " + j);
}
protected ICell WriteCell(IRow row, int column, Action<ICell> setValue, ICellStyle style = null)
{
var cell = row.CreateCell(column);
setValue(cell);
if (style != null)
{
cell.CellStyle = style;
}
return cell;
}
public void SetCellValue(IRow row, int index, CellType cellType, dynamic value)
{
if (value == null) value = string.Empty;
if (value is string)
{
value = Convert.ChangeType(value, TypeCode.String);
}
else
{
value = Convert.ChangeType(value, TypeCode.Double);
}
ICell cell = row.CreateCell(index);
cell.SetCellType(cellType);
cell.SetCellValue(value);
}
internal static ICell CreateCell(IWorkbook workbook, IRow row, int column, string content, bool isCentered)
{
ICellStyle style = workbook.CreateCellStyle();
ICell cell = row.CreateCell(column);
cell.SetCellValue(content);
if (isCentered)
{
style.Alignment = HorizontalAlignment.Center;
}
style.BorderBottom = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
cell.CellStyle = style;
return cell;
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dtSource"></param>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
/// <param name="writeColumnName"></param>
/// <param name="excelType"></param>
public static void WriteInToExcel <T>(this IEnumerable <T> dtSource, string fileName, string sheetName, bool writeColumnName = true, ExcelType excelType = ExcelType.XLS)
{
FileStream fs = null;
IWorkbook workbook = null;
ISheet sheet = null;
try
{
#region 初始化
if (excelType == ExcelType.XLS)
{
workbook = new HSSFWorkbook();
if (fileName.Substring(fileName.Length - 4, 4).ToLower() != ".xls")
{
fileName = fileName + ".xls";
}
}
else
{
workbook = new XSSFWorkbook();
if (fileName.Substring(fileName.Length - 4, 4).ToLower() != ".xls")
{
fileName = fileName + ".xlsx";
}
}
if (workbook != null)
{
if (sheet == null)
{
sheet = workbook.CreateSheet(sheetName);
}
}
fs = new FileStream(fileName + ".", FileMode.OpenOrCreate, FileAccess.ReadWrite);
#endregion
#region 写列名称
if (writeColumnName)
{
IRow row = sheet.CreateRow(0);
int columnIndex = 0;
typeof(T).GetProperties().AsEnumerable().ToList().ForEach(x =>
{
row.CreateCell(columnIndex).SetCellValue(x.Name != null ? x.Name : string.Empty);
columnIndex++;
});
}
#endregion
#region 写内容
int i = writeColumnName ? 1 : 0;
dtSource.ToList().ForEach(x =>
{
IRow row = sheet.CreateRow(i);
int m = 0;
typeof(T).GetProperties().AsEnumerable().ToList().ForEach(p =>
{
var value = x.GetType().GetProperty(p.Name).GetValue(x, null);
row.CreateCell(m).SetCellValue(TypeConvert.ToString(p.PropertyType, value != null ? value : string.Empty));
m++;
});
i++;
});
#endregion
workbook.Write(fs); //写入到excel
}
catch (Exception ex)
{
throw ex;
}
finally
{
fs.Dispose();
fs.Close();
}
}
protected override void Process(ISheet table)
{
var firstVisit = Visits.FirstOrDefault();
if (firstVisit == null)
{
table.FindCellByMacros(title).SetValue(GetNotNullString("Журнал выдачи полисов ОМС"));
return;
}
string partyNumber = firstVisit.PolicyParty;
table.FindCellByMacros(title).SetValue(GetNotNullString(string.Format("Журнал выдачи полисов ОМС (заявка {0})", partyNumber)));
int rowNumber = 3;
ICellStyle style = table.Workbook.CreateCellStyle();
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
for (int i = 0; i < Visits.Count; i++)
{
var clientVisit = Visits[i];
IRow row = table.CreateRow(rowNumber);
row.CreateCell(0).SetValue(GetNotNullString((i + 1).ToString()));
row.CreateCell(1).SetValue(GetNotNullString(clientVisit.TemporaryPolicyNumber));
row.CreateCell(2).SetValue(GetDateString(clientVisit.TemporaryPolicyDate, "dd.MM.yyyy"));
row.CreateCell(3).SetValue(GetNotNullString(clientVisit.UnifiedPolicyNumber));
string fio = string.Format("{0} {1} {2}",
clientVisit.Lastname ?? string.Empty,
clientVisit.Firstname ?? string.Empty,
clientVisit.Secondname ?? string.Empty);
row.CreateCell(4).SetValue(GetNotNullString(fio));
row.CreateCell(5).SetValue(GetDateString(clientVisit.Birthday, "dd.MM.yyyy"));
row.CreateCell(6).SetValue(GetNotNullString(clientVisit.DeliveryPoint));
row.CreateCell(7).SetValue(GetNotNullString(clientVisit.DeliveryCenter.Name));
row.CreateCell(8);
row.CreateCell(9);
if (!string.IsNullOrEmpty(clientVisit.Comment))
{
clientVisit.Comment = clientVisit.Comment.Replace(" ", string.Empty);
}
row.CreateCell(10).SetValue(GetNotNullString(clientVisit.Comment));
row.GetCell(0).CellStyle = style;
row.GetCell(1).CellStyle = style;
row.GetCell(2).CellStyle = style;
row.GetCell(3).CellStyle = style;
row.GetCell(4).CellStyle = style;
row.GetCell(5).CellStyle = style;
row.GetCell(6).CellStyle = style;
row.GetCell(7).CellStyle = style;
row.GetCell(8).CellStyle = style;
row.GetCell(9).CellStyle = style;
row.GetCell(10).CellStyle = style;
rowNumber++;
}
}
/// <summary>
/// Excel导出文件流（服务项目调用）
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelHeadTitle">文件第一行标题</param>
public static MemoryStream GetExcelStream(DataTable dtSource, string excelHeadTitle = "")
{
HSSFWorkbook workbook = new HSSFWorkbook();
ExcelInformation information = new ExcelInformation();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = information.Company;
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = information.Author; //填加xls文件作者信息
si.ApplicationName = information.ApplicationName; //填加xls文件创建程序信息
si.LastAuthor = information.LastAuthor; //填加xls文件最后保存者信息
si.Comments = information.Comments; //填加xls文件作者信息
si.Title = information.Title; //填加xls文件标题信息
si.Subject = information.Subject; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
if (!string.IsNullOrWhiteSpace(excelHeadTitle))
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelHeadTitle);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.IsBold = true;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = string.IsNullOrWhiteSpace(excelHeadTitle) ? sheet.CreateRow(0) : sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.IsBold = true;
//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;
var arr = (arrColWidth[column.Ordinal] + 1) * 256;
//限定宽度
if (arrColWidth[column.Ordinal] > 100)
{
arrColWidth[column.Ordinal] = 100;
}
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = string.IsNullOrWhiteSpace(excelHeadTitle) ? 1 : 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();
switch (column.DataType.BaseType.FullName)
{
case "System.Enum": //枚举类型
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
continue;
}
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":
//long intV = 0;
//long.TryParse(drValue, out intV);
newCell.SetCellValue(drValue);
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();
return(ms);
}
}
public void TestLoadSave()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("WithMoreVariousData.xlsx");
ICreationHelper CreateHelper = workbook.GetCreationHelper();
Assert.AreEqual(3, workbook.NumberOfSheets);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
// Check hyperlinks
Assert.AreEqual(4, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
// Write out, and check
// Load up again, check all links still there
XSSFWorkbook wb2 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
Assert.AreEqual(3, wb2.NumberOfSheets);
Assert.IsNotNull(wb2.GetSheetAt(0));
Assert.IsNotNull(wb2.GetSheetAt(1));
Assert.IsNotNull(wb2.GetSheetAt(2));
sheet = (XSSFSheet)wb2.GetSheetAt(0);
// Check hyperlinks again
Assert.AreEqual(4, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
// Add one more, and re-check
IRow r17 = sheet.CreateRow(17);
ICell r17c = r17.CreateCell(2);
IHyperlink hyperlink = CreateHelper.CreateHyperlink(HyperlinkType.Url);
hyperlink.Address = ("http://poi.apache.org/spreadsheet/");
hyperlink.Label = "POI SS Link";
r17c.Hyperlink = (hyperlink);
Assert.AreEqual(5, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
Assert.AreEqual(HyperlinkType.Url,
sheet.GetRow(17).GetCell(2).Hyperlink.Type);
Assert.AreEqual("POI SS Link",
sheet.GetRow(17).GetCell(2).Hyperlink.Label);
Assert.AreEqual("http://poi.apache.org/spreadsheet/",
sheet.GetRow(17).GetCell(2).Hyperlink.Address);
// Save and re-load once more
XSSFWorkbook wb3 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb2);
Assert.AreEqual(3, wb3.NumberOfSheets);
Assert.IsNotNull(wb3.GetSheetAt(0));
Assert.IsNotNull(wb3.GetSheetAt(1));
Assert.IsNotNull(wb3.GetSheetAt(2));
sheet = (XSSFSheet)wb3.GetSheetAt(0);
Assert.AreEqual(5, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
Assert.AreEqual(HyperlinkType.Url,
sheet.GetRow(17).GetCell(2).Hyperlink.Type);
Assert.AreEqual("POI SS Link",
sheet.GetRow(17).GetCell(2).Hyperlink.Label);
Assert.AreEqual("http://poi.apache.org/spreadsheet/",
sheet.GetRow(17).GetCell(2).Hyperlink.Address);
}
/// <summary>
/// 创建一行数据
/// </summary>
/// <param name="row">Row 对象</param>
/// <param name="rowDatas">填入 Row 的只读数据集合</param>
/// <param name="userStringFormat">是否是否使用 string 数据格式，默认 false</param>
private void CreateRow(IRow row, object[] rowDatas, bool userStringFormat = false)
{
for (var i = 0; i < rowDatas.Length; i++)
{
var cell = row.CreateCell(i);
var data = rowDatas[i];
var dataType = data == null || userStringFormat ? typeof(string) : data.GetType();
WriteToCell(cell, dataType, data);
}
}
/// <summary>
/// 根据datarow 创建 cells.
/// </summary>
/// <param name="excelRow"></param>
/// <param name="columns"></param>
/// <param name="row">如果为null 则该行所有cell的值为空</param>
/// <param name="isHead">如果是true 则创建表头.</param>
private void CreateCellForRow(IRow excelRow, DataColumnCollection columns, DataRow row, bool isHead)
{
for (int i = 0; i < columns.Count; i++)
{
var cell = excelRow.CreateCell(i);
if (isHead)
{
cell.SetCellValue(columns[i].ColumnName);
}
else
{
string cellValue = string.Empty;
if (row != null) { cellValue = row[i].ToString(); }
//该地址是图片
if (NeedInsertImage&& Regex.IsMatch(cellValue, @"\.jpg|\.png|\.tiff|\.tif", RegexOptions.IgnoreCase))
{
string filePath = System.Web.HttpContext.Current.Server.MapPath("/ProductImages/original/" + cellValue);
if (File.Exists(filePath))
{
try
{
System.Drawing.Image image = System.Drawing.Image.FromFile(filePath);
System.Drawing.Bitmap targetImage = ThumbnailMaker.DrawThumbnail(image, ThumbnailType.GeometricScalingByWidth, 150, 0);
excelRow.Sheet.SetColumnWidth(i, MSExcelUtil.pixel2WidthUnits(targetImage.Width - 2));
excelRow.HeightInPoints = (float)((targetImage.Height - 2) * 0.75);
using (MemoryStream ms = new MemoryStream())
{
targetImage.Save(ms, image.RawFormat);
InsertImageToCell(ms, i, excelRow.RowNum);
}
targetImage.Dispose();
image.Dispose();
}
catch (Exception ex)
{
NLogger.Logger.Debug(filePath+ex.Message);
}
}
}
else
{
cell.SetCellValue(cellValue);
}
}
}
}
/// <summary>
/// 分表导出到多个Sheet
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strHeaderText"></param>
/// <param name="hidText"></param>
/// <param name="FileName"></param>
/// <returns></returns>
public static MemoryStream ExportMultipleSheet(List <DataTable> dtSource, List <string> strHeaderText, List <string> hidText, string FileName = null)
{
IWorkbook workbook = null;
if (!string.IsNullOrWhiteSpace(FileName))
{
if (FileName.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook();
}
// 2003版本
else if (FileName.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook();
}
}
else
{
workbook = new HSSFWorkbook();
}
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = CompanyName;
//workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = CompanyName; //填加xls文件作者信息
si.ApplicationName = CompanyName; //填加xls文件创建程序信息
si.LastAuthor = CompanyName; //填加xls文件最后保存者信息
si.Comments = CompanyName; //填加xls文件作者信息
si.Title = CompanyName; //填加xls文件标题信息
si.Subject = CompanyName; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
//workbook.SummaryInformation = si;
}
#endregion
//表头样式
ICellStyle headStyleOne = workbook.CreateCellStyle();
headStyleOne.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 18;
font.Boldweight = 700;
headStyleOne.SetFont(font);
//表头1
ICellStyle headStyleTwo = workbook.CreateCellStyle();
headStyleTwo.Alignment = HorizontalAlignment.Right;
headStyleTwo.Indention = 50;
//列头样式
ICellStyle headStyleThree = workbook.CreateCellStyle();
headStyleThree.Alignment = HorizontalAlignment.Left;
headStyleThree.VerticalAlignment = VerticalAlignment.Center;
IFont fonts = workbook.CreateFont();
fonts.FontHeightInPoints = 10;
fonts.Boldweight = 700;
headStyleThree.SetFont(fonts);
//每个表头名
var HeadText = string.Empty;
//每个表头二
var HeadTextTwo = string.Empty;
//遍历多张表(每一张表创建一个Sheet)
for (int i = 0; i < dtSource.Count; i++)
{
HeadText = strHeaderText[i];
HeadTextTwo = hidText[i];
#region 处理cell样式和大小
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource[i].Columns.Count];
foreach (DataColumn item in dtSource[i].Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int r = 0; r < dtSource[i].Rows.Count; r++)
{
for (int j = 0; j < dtSource[i].Columns.Count; j++)
{
var intTemp = Encoding.GetEncoding(936).GetBytes(dtSource[i].Rows[r][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
ICellStyle tdStyle = null;
if (!string.IsNullOrWhiteSpace(FileName))
{
//2007版本
if (FileName.IndexOf(".xlsx") > 0)
{
tdStyle = (XSSFCellStyle)workbook.CreateCellStyle();
}
// 2003版本
else if (FileName.IndexOf(".xls") > 0)
{
tdStyle = (HSSFCellStyle)workbook.CreateCellStyle();
}
}
else
{
tdStyle = (HSSFCellStyle)workbook.CreateCellStyle();
}
#endregion
int rowIndex = 0;
ISheet sheet = workbook.CreateSheet();
foreach (DataRow row in dtSource[i].Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 30;
headerRow.CreateCell(0).SetCellValue(HeadText);
headerRow.GetCell(0).CellStyle = headStyleOne;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[i].Columns.Count - 1));
}
#endregion
{
IRow headerRow = sheet.CreateRow(1);
headerRow.CreateCell(0).SetCellValue(HeadTextTwo + "| 导出时间:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm"));
headerRow.HeightInPoints = 18;
headerRow.GetCell(0).CellStyle = headStyleTwo;
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, dtSource[i].Columns.Count - 1));
}
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(2);
headerRow.HeightInPoints = 18;
foreach (DataColumn column in dtSource[i].Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyleThree;
//设置列宽
if (column.ColumnName == "款号图片")
{
sheet.SetColumnWidth(column.Ordinal, 20 * 256);
}
else
{
var columnWidth = (arrColWidth[column.Ordinal] + 1) * 256;
sheet.SetColumnWidth(column.Ordinal, columnWidth > (255 * 256) ? 6000 : columnWidth);
//sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
#endregion
rowIndex = 3;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.HeightInPoints = 18;
//if (rowIndex % 2 == 0)
// tdStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
//else
// tdStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index;
//tdStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index;
//tdStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
////设置单元格边框
//tdStyle.BorderBottom = BorderStyle.THIN;
//tdStyle.BorderLeft = BorderStyle.THIN;
//tdStyle.BorderRight = BorderStyle.THIN;
//tdStyle.BorderTop = BorderStyle.THIN;
tdStyle.VerticalAlignment = VerticalAlignment.Center;
short datet = workbook.CreateDataFormat().GetFormat("yyyy-mm-dd");
bool isCell = dtSource[i].Rows.Count < 4000; //4000行bug
InitRowData(dtSource[i], tdStyle, row, dataRow, isCell, workbook, rowIndex, HeadText);
#endregion
rowIndex++;
}
}
ExcelStream stream = new ExcelStream();
workbook.Write(stream);
stream.CanDispose = true;
return(stream);
}
/// <summary>
/// 创建 Row
/// </summary>
/// <param name="row">Row 对象</param>
/// <param name="datas">填入 Row 的只读数据集合</param>
/// <param name="isFormat">是否数据有格式化，默认 true</param>
private void CreateRow(IRow row, IReadOnlyList<object> datas, bool isFormat = true)
{
for (var i = 0; i < this._columnNames.Length; i++)
{
var cell = row.CreateCell(i);
var dataType = isFormat ? this._columnTypes[i] : typeof (string);
this.WriteToCell(cell, dataType, datas[i]);
}
}
public void DailyExportToExcel()
{
System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog();
sfd.Filter = "Excel 2007 (*.xlsx)|*.xlsx";
sfd.FilterIndex = 1;
sfd.RestoreDirectory = true;
if (sfd.ShowDialog() == DialogResult.OK)
{
string localFilePath = sfd.FileName.ToString(); //获得文件路径
File.Copy(@"\\qqqqqq-ms2\Templates\Daily.xlsx", localFilePath, true);
IWorkbook wk = WorkbookFactory.Create(localFilePath);
//导出明细
ISheet tb = wk.GetSheet(wk.GetSheetName(0));
int t = 1;
foreach (BPM_Daily temmodul in allDailyList)
{
//制令单号 工号 姓名 日期 班别 b25 工序 标准工时 生产工时 投入数量 良品数 不良数
IRow row = tb.CreateRow(t);
row.CreateCell(0).SetCellValue(temmodul.OrderID); //工单单号
row.CreateCell(1).SetCellValue(temmodul.JobNum); //工号
row.CreateCell(2).SetCellValue(temmodul.Name); //姓名
row.CreateCell(3).SetCellValue(temmodul.Date.ToString()); //日期
row.CreateCell(4).SetCellValue(temmodul.ClassType); //班别
row.CreateCell(5).SetCellValue(temmodul.ProcessID); //工序ＩＤ
row.CreateCell(6).SetCellValue(temmodul.ProcessName); //工序名称
row.CreateCell(7).SetCellValue(Convert.ToDouble(temmodul.StandardHours.ToString())); //标准工时
row.CreateCell(8).SetCellValue(Convert.ToDouble(temmodul.WorkHours.ToString())); //生产工时
row.CreateCell(9).SetCellValue(Convert.ToDouble(temmodul.Qty.ToString())); //数量
row.CreateCell(10).SetCellValue(Convert.ToDouble(temmodul.QtyOK.ToString())); //良品数量
row.CreateCell(11).SetCellValue(Convert.ToDouble(temmodul.QtyNG.ToString())); //不良品数量
row.CreateCell(12).SetCellValue(temmodul.Workstation); //站别
t++;
}
//导出未作业人员列表
ISheet tb2 = wk.GetSheet(wk.GetSheetName(1));
int t2 = 1;
foreach (HR_User temmodul in NotWorkUserList)
{
//制令单号 工号 姓名 日期 班别 b25 工序 标准工时 生产工时 投入数量 良品数 不良数
IRow row = tb2.CreateRow(t2);
row.CreateCell(0).SetCellValue(temmodul.Job_Num); //工号
row.CreateCell(1).SetCellValue(temmodul.Name); //姓名
row.CreateCell(2).SetCellValue(temmodul.Workstation); //站别
t2++;
}
////导出汇总信息
//var totalDailyUserList = new List<BPM_Daily>();
//foreach (var daily in allDailyList)
//{
// //用户汇总 总览表
// if (totalDailyUserList.FirstOrDefault(x => x.JobNum == daily.JobNum) == null) //如果未找到了该作业元的汇总资料
// {
// totalDailyUserList.Add(new BPM_Daily()
// {
// JobNum = daily.JobNum,
// Name = daily.Name,
// WorkHours = allDailyList.Where(x => x.JobNum == daily.JobNum).Sum(x => x.WorkHours),
// NotWorkHours = allDailyList.Where(x => x.JobNum == daily.JobNum).Sum(x => x.NotWorkHours),
// QtyNG = allDailyList.Where(x => x.JobNum == daily.JobNum).Sum(x => x.QtyNG),
// QtyOK = allDailyList.Where(x => x.JobNum == daily.JobNum).Sum(x => x.QtyOK),
// Workstation = daily.Workstation
// });
// }
//}
//导出汇总信息
var totalDailyUserList = new List <BPM_Daily>();
foreach (var te in allDailyList)
{
//用户汇总 总览表
if (totalDailyUserList.FirstOrDefault(m => m.JobNum == te.JobNum) == null)
{
var temModel = new Server.Model.BPM_Daily();
temModel.JobNum = te.JobNum;
temModel.Name = te.Name;
temModel.Workstation = te.Workstation;
temModel.WorkHours = allDailyList.Where(x => x.JobNum == te.JobNum).Sum(x => x.WorkHours);
temModel.NotWorkHours = allDailyList.Where(x => x.JobNum == te.JobNum).Sum(x => x.NotWorkHours);
temModel.QtyNG = allDailyList.Where(x => x.JobNum == te.JobNum).Sum(x => x.QtyNG);
temModel.QtyOK = allDailyList.Where(x => x.JobNum == te.JobNum).Sum(x => x.QtyOK);
temModel.TotalWorkHoursStandard = allDailyList.Where(m => m.JobNum == te.JobNum).Sum(m => m.TotalWorkHoursStandard);
temModel.TotalWorkHoursNotRelax = allDailyList.Where(m => m.JobNum == te.JobNum).Sum(m => m.TotalWorkHoursNotRelax);
temModel.WorkHours = allDailyList.Where(m => m.JobNum == te.JobNum).Sum(m => m.WorkHours);
temModel.Efficiency = Convert.ToDouble(temModel.TotalWorkHoursStandard / temModel.WorkHours);
// temModel.TotalWorkHoursNotRelax = Convert.ToDecimal(temModel.TotalWorkHoursNotRelax / temModel.WorkHours);
totalDailyUserList.Add(temModel);
}
}
ISheet tb3 = wk.GetSheet(wk.GetSheetName(2));
int t3 = 1;
foreach (BPM_Daily temmodul in totalDailyUserList)
{
//制令单号 工号 姓名 日期 班别 b25 工序 标准工时 生产工时 投入数量 良品数 不良数
IRow row = tb3.CreateRow(t3);
row.CreateCell(1).SetCellValue(temmodul.JobNum); //工号
row.CreateCell(2).SetCellValue(temmodul.Name); //姓名
row.CreateCell(8).SetCellValue(Convert.ToDouble(temmodul.WorkHours.ToString())); //生产工时
row.CreateCell(10).SetCellValue(Convert.ToDouble(temmodul.QtyOK.ToString())); //良品数量
row.CreateCell(11).SetCellValue(Convert.ToDouble(temmodul.QtyNG.ToString())); //不良品数量
row.CreateCell(12).SetCellValue(temmodul.Workstation); //站别
row.CreateCell(13).SetCellValue(Convert.ToDouble(temmodul.TotalWorkHoursNotRelax)); //得到工时
row.CreateCell(14).SetCellValue(Convert.ToDouble(temmodul.TotalWorkHoursStandard)); //宽放后得到工时
row.CreateCell(15).SetCellValue(Convert.ToDouble(temmodul.TotalWorkHoursNotRelax / temmodul.WorkHours)); //宽放后得到工时
row.CreateCell(16).SetCellValue(Convert.ToDouble(temmodul.TotalWorkHoursStandard / temmodul.WorkHours)); //宽放后得到工时
t3++;
}
FileStream fs2 = File.Create(localFilePath);
wk.Write(fs2); //向打开的这个xls文件中写入mySheet表并保存。
ZhuifengLib.MessageShow.Message.MessageInfo("提示：创建成功！");
}
}
public void Run()
{
IWorkbook workbook = new XSSFWorkbook();
ICellStyle hlink_style = workbook.CreateCellStyle();
IFont hlink_font = workbook.CreateFont();
hlink_font.Underline = FontUnderlineType.Single;
hlink_font.Color = HSSFColor.Blue.Index;
hlink_style.SetFont(hlink_font);
ICell cell;
ISheet sheet = workbook.CreateSheet("Hyperlinks");
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new XSSFHyperlink(HyperlinkType.File);
link.Address = ("link1.xls");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new XSSFHyperlink(HyperlinkType.Email);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//Create a target sheet and cell
ISheet sheet2 = workbook.CreateSheet("Target ISheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new XSSFHyperlink(HyperlinkType.Document);
link.Address = ("'Target ISheet'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
// font Test용 Sheet생성
ISheet sheet1 = workbook.CreateSheet("Font_Test Sheet");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = workbook.CreateFont();
font1.Color = IndexedColors.Red.Index;
font1.IsItalic = true;
font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = workbook.CreateFont();
font2.Color = IndexedColors.OliveGreen.Index;
font2.IsStrikeout = true;
font2.FontHeightInPoints = 15;
font2.FontName = "굴림체";
//bind font with style 2
ICellStyle style2 = workbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = sheet1.CreateRow(1).CreateCell(1);
cell1.SetCellValue("Hello World!");
cell1.CellStyle = style1;
ICell cell2 = sheet1.CreateRow(3).CreateCell(1);
cell2.SetCellValue("早上好！");
cell2.CellStyle = style2;
////cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
XSSFRichTextString richtext = new XSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = workbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3 = workbook.CreateFont();
font3.TypeOffset = FontSuperScript.Super;
font3.IsItalic = true;
font3.Color = IndexedColors.Blue.Index;
font3.FontHeightInPoints = 8;
richtext.ApplyFont(16, 18, font3);
cell3.SetCellValue(richtext);
/*
* BorderStryle
* */
ISheet sheet3 = workbook.CreateSheet("BorderStype");
IRow row = sheet3.CreateRow(1);
// Create a cell and put a value in it.
cell = row.CreateCell(1);
cell.SetCellValue(4);
// Style the cell with borders all around.
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = IndexedColors.Black.Index;
style.BorderLeft = BorderStyle.DashDotDot;
style.LeftBorderColor = IndexedColors.Green.Index;
style.BorderRight = BorderStyle.Hair;
style.RightBorderColor = IndexedColors.Blue.Index;
style.BorderTop = BorderStyle.MediumDashed;
style.TopBorderColor = IndexedColors.Orange.Index;
//create border diagonal
style.BorderDiagonalLineStyle = BorderStyle.Medium; //this property must be set before BorderDiagonal and BorderDiagonalColor
style.BorderDiagonal = BorderDiagonal.Forward;
style.BorderDiagonalColor = IndexedColors.Gold.Index;
cell.CellStyle = style;
// Create a cell and put a value in it.
cell2 = row.CreateCell(2);
cell2.SetCellValue(5);
style2 = workbook.CreateCellStyle();
style2.BorderDiagonalLineStyle = BorderStyle.Medium;
style2.BorderDiagonal = BorderDiagonal.Backward;
style2.BorderDiagonalColor = IndexedColors.Red.Index;
cell2.CellStyle = style2;
using (FileStream fs = File.Create(@"C:\00.Dev\temp\HyprtLink_Font.xlsx"))
{
workbook.Write(fs);
}
}
private static void CreatSheet(ISheet sheet, List <HaikanSmartTownCockpit.Api.Entities.Cunzhenmb> query)
{
IRow headerRow = sheet.CreateRow(0);
List <string> list = new List <string>()
{
"姓名", "职务", "性别", "出生日期", "身份证号", "联系方式", "政治面貌", "文化程度", "户籍地", "工作单位或家庭地址", "班级"
};
//表头
for (int i = 0; i < list.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(list[i]);
}
int rowIndex = 1;
foreach (var row in query)
{
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.CreateCell(0).SetCellValue(row.RealName);
dataRow.CreateCell(1).SetCellValue(row.Residence);
dataRow.CreateCell(2).SetCellValue(row.Sex);
dataRow.CreateCell(3).SetCellValue(row.Birth);
dataRow.CreateCell(4).SetCellValue(row.IdentityCard);
dataRow.CreateCell(5).SetCellValue(row.Phone);
dataRow.CreateCell(6).SetCellValue(row.Nation);
dataRow.CreateCell(7).SetCellValue(row.Education);
dataRow.CreateCell(8).SetCellValue(row.Domicile);
dataRow.CreateCell(9).SetCellValue(row.Address);
dataRow.CreateCell(10).SetCellValue(row.Wechat);
rowIndex++;
}
}
/// <summary>
/// npoi合并单元格测试
/// </summary>
private static void testnpoi()
{
string filetime = DateTime.Now.ToString("yyyyMMddmm");
string _webpath = AppDomain.CurrentDomain.BaseDirectory + "\\Excel\\EndMarketing";
if (!Directory.Exists(_webpath))
{
Directory.CreateDirectory(_webpath);
}
string filename = "(年终营销活动)_" + filetime + ".xls";
string path = _webpath + "\\" + filename;
MemoryStream ms = new MemoryStream();
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("年终营销活动-");
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 1, 2));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 3, 4));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 5, 6));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 7, 8));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 9, 10));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 11, 12));
IRow row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("区县");
row1.CreateCell(1).SetCellValue("区县1");
row1.CreateCell(3).SetCellValue("区县3");
row1.CreateCell(5).SetCellValue("区县5");
row1.CreateCell(7).SetCellValue("区县7");
row1.CreateCell(9).SetCellValue("区县9");
row1.CreateCell(11).SetCellValue("区县11");
IRow row2 = sheet.CreateRow(1);
row2.CreateCell(1).SetCellValue("区县1");
row2.CreateCell(2).SetCellValue("区县2");
row2.CreateCell(3).SetCellValue("区县3");
row2.CreateCell(4).SetCellValue("区县4");
row2.CreateCell(5).SetCellValue("区县5");
row2.CreateCell(6).SetCellValue("区县6");
row2.CreateCell(7).SetCellValue("区县7");
row2.CreateCell(8).SetCellValue("区县8");
row2.CreateCell(9).SetCellValue("区县9");
row2.CreateCell(10).SetCellValue("区县10");
row2.CreateCell(11).SetCellValue("区县11");
row2.CreateCell(12).SetCellValue("区县12");
workbook.Write(ms);
ms.Position = 0;
ms.Close();
ms.Flush();
SaveToFile(ms, path);
}
public void GenerarExcel()
{
var NombreExcel = "Producto - Sistemas de Ventas ";
// Recuperamos la data de las consulta DB
var data = (List <Annies.Entities.Producto>)Session["ReporteProducto"];
// Creación del libro excel xlsx.
var wb = new XSSFWorkbook();
// Creación del la hoja y se especifica un nombre
var fileName = WorkbookUtil.CreateSafeSheetName("Productos");
ISheet sheet = wb.CreateSheet(fileName);
// Contadores para filas y columnas.
int rownum = 0;
int cellnum = 0;
// Creacion del estilo de la letra para las cabeceras.
var fontCab = wb.CreateFont();
fontCab.FontHeightInPoints = 10;
fontCab.FontName = "Calibri";
fontCab.Boldweight = (short)FontBoldWeight.Bold;
fontCab.Color = HSSFColor.White.Index;
// Creacion del color del estilo.
var colorCab = new XSSFColor(new byte[] { 7, 105, 173 });
// Se crea el estilo y se agrega el font al estilo
var styleCab = (XSSFCellStyle)wb.CreateCellStyle();
styleCab.SetFont(fontCab);
styleCab.FillForegroundXSSFColor = colorCab;
styleCab.FillPattern = FillPattern.SolidForeground;
string[] Cabezeras =
{
"Código Producto", "Stock", "Codigo Almacén", "Marca", "Talla", "Talla Vendida", "Precio", "Estado", "Fecha"
};
// Se crea la primera fila para las cabceras.
IRow row = sheet.CreateRow(rownum++);
ICell cell;
foreach (var item in Cabezeras)
{
// Se crea celdas y se agrega las cabeceras
cell = row.CreateCell(cellnum++);
cell.SetCellValue(item);
cell.CellStyle = styleCab;
sheet.AutoSizeColumn(cellnum);
}
// Creacion del estilo de la letra para la data.
var fontBody = wb.CreateFont();
fontBody.FontHeightInPoints = 10;
fontBody.FontName = "Arial";
var styleBody = (XSSFCellStyle)wb.CreateCellStyle();
styleBody.SetFont(fontBody);
// Impresión de la data
foreach (var item in data)
{
cellnum = 0;
row = sheet.CreateRow(rownum++);
sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Cod_Prod.ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Stock_Prod.ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Codigo_Al, styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Marca_Prod, styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Talla_Prod, styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Talla_Vendida_Prod, styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Precio_Prod.ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Estado_Prod == 1 ? "Activo" : "Inactivo".ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.FechaDesde.ToString().Substring(6, 2) + "/" + item.FechaDesde.ToString().Substring(4, 2) + "/" + item.FechaDesde.ToString().Substring(0, 4), styleBody); sheet.AutoSizeColumn(cellnum);
}
var nameFile = NombreExcel + DateTime.Now.ToString("dd_MM_yyyy HH:mm:ss") + ".xlsx";
Response.AddHeader("content-disposition", "attachment; filename=" + nameFile);
Response.ContentType = "application/octet-stream";
Stream outStream = Response.OutputStream;
wb.Write(outStream);
outStream.Close();
Response.End();
}
internal static ICell SetCellRightBorder(IWorkbook workbook, IRow row, int column)
{
ICellStyle style = workbook.CreateCellStyle();
ICell cell = row.CreateCell(column);
cell.SetCellValue("");
style.BorderRight = BorderStyle.Thin;
cell.CellStyle = style;
return cell;
}
private static void InitRowData(DataTable dtSource, ICellStyle tdStyle, DataRow row, IRow dataRow, bool isCell, IWorkbook workbook = null, int rowNum = 0, string reportType = "")
{
tdStyle.WrapText = true;
if (ExportPictureTiltes.Contains(reportType))
{
dataRow.Height = 2000;
}
for (int i = 0; i < dtSource.Columns.Count; i++)
{
var column = dtSource.Columns[i];
ICell newCell = dataRow.CreateCell(column.Ordinal);
if (isCell)
{
newCell.CellStyle = tdStyle;
}
string drValue = row[column].ToString();
newCell.SetCellValue(drValue);
if (column.ColumnName == "款号图片" && !string.IsNullOrWhiteSpace(drValue))
{
#region 款号图片
System.Drawing.Image originalImage = null;
////新建一个bmp图片
//System.Drawing.Image bitmap = null;
////新建一个画板
//System.Drawing.Graphics g = null;
//double toheight = 100;
//double towidth = 100;
if (File.Exists(drValue))
{
originalImage = System.Drawing.Image.FromFile(drValue);
//double proportion1;
//double proportion2;
//int x = 0;
//int y = 0;
////原图的宽
//int ow = originalImage.Width;
////原图的高
//int oh = originalImage.Height;
//towidth = toheight * ow / oh;
//proportion1 = toheight / Convert.ToDouble(oh);
//proportion2 = towidth / Convert.ToDouble(ow);
////新建一个bmp图片
//bitmap = new System.Drawing.Bitmap(Convert.ToInt32(towidth), Convert.ToInt32(toheight));
////新建一个画板
//g = System.Drawing.Graphics.FromImage(bitmap);
////设置高质量插值法
//g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.High;
////设置高质量,低速度呈现平滑程度
//g.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;
////清空画布并以透明背景色填充
//g.Clear(System.Drawing.Color.Transparent);
////在指定位置并且按指定大小绘制原图片的指定部分
//g.DrawImage(originalImage, new System.Drawing.Rectangle(0, 0, Convert.ToInt32(towidth), Convert.ToInt32(toheight)), new System.Drawing.Rectangle(x, y, ow, oh), System.Drawing.GraphicsUnit.Pixel);
////以jpg格式保存缩略图WebControls
////File.Delete(thumbnailPath);
//MemoryStream stream = new MemoryStream();
//bitmap.Save(stream, ImageFormat.Jpeg);
//bitmap.Dispose();
ImageConverter imgconv = new ImageConverter();
byte[] imgByte = (byte[])imgconv.ConvertTo(originalImage, typeof(byte[]));
int pictureIdx = workbook.AddPicture(imgByte, PictureType.PNG);
IDrawing patriarch = newCell.Sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(5, 5, 1023, 250, i, rowNum, i, rowNum);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
pict.LineWidth = 100;
//pict.Resize();
}
else
{
newCell.SetCellValue("图片不存在");
}
#endregion
}
}
}
internal static void topBottomBorder(IWorkbook workbook, ICell cell, IRow row, int numCells)
{
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
for (int i = 1; i <= numCells; i++)
{
ICell newCell = row.CreateCell(cell.ColumnIndex + i);
newCell.CellStyle = cellStyle;
}
}
private static void WriteHeaderCell(IRow row, int i, String text, ICellStyle style)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(new HSSFRichTextString(text));
cell.CellStyle = (style);
}
private static int InsertCell(IRow row, int cellIndex, string value)
{
row.CreateCell(cellIndex).SetCellValue(value);
cellIndex++;
return cellIndex;
}
protected static void CreateCell(IRow row, int cellIdx, string txt, CellType type = CellType.String, ICellStyle style = null)
{
ICell cell = row.CreateCell(cellIdx, type);
if (style != null) cell.CellStyle = style;
cell.SetCellValue(txt);
}
/// <summary>
/// 设置行
/// </summary>
/// <param name="row"></param>
/// <param name="obj"></param>
private static void SetRow(IRow row, object obj)
{
//获取属性
var columns = obj.GetType().GetProperties();
//一个属性一个格
for (int i = 0; i < obj.GetType().GetProperties().Length; i++)
{
//创建格
ICell cell = row.CreateCell(i);
//设置单元格的内容
SetCell(cell, columns[i], obj);
}
}
public async Task <ActionResult> ExportToExcel(MaterialReceiptDetailQueryViewModel model)
{
IList <MaterialReceiptDetail> lst = new List <MaterialReceiptDetail>();
using (MaterialReceiptServiceClient client = new MaterialReceiptServiceClient())
{
await Task.Run(() =>
{
PagingConfig cfg = new PagingConfig()
{
IsPaging = false,
OrderBy = "CreateTime Desc,Key.ReceiptNo,Key.ItemNo",
Where = GetWhereCondition(model)
};
MethodReturnResult <IList <MaterialReceiptDetail> > result = client.GetDetail(ref cfg);
if (result.Code == 0)
{
lst = result.Data;
}
});
}
//创建工作薄。
IWorkbook wb = new HSSFWorkbook();
//设置EXCEL格式
ICellStyle style = wb.CreateCellStyle();
style.FillForegroundColor = 10;
//有边框
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
IFont font = wb.CreateFont();
font.Boldweight = 10;
style.SetFont(font);
ICell cell = null;
IRow row = null;
ISheet ws = null;
for (int j = 0; j < lst.Count; j++)
{
if (j % 65535 == 0)
{
ws = wb.CreateSheet();
row = ws.CreateRow(0);
#region //列名
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_ReceiptNo); //领料号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_OrderNumber); //工单号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_ReceiptDate); //领料日期
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_ItemNo); //项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_LineStoreName); //线别仓
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_MaterialCode); //物料编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("物料名称"); //物料名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_MaterialLot); //物料批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_Qty); //数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_SupplierMaterialLot); //供应商批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_SupplierCode); //供应商编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("供应商名称"); //供应商名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("效率档"); //效率档
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("描述"); //描述
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("编辑人"); //编辑人
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("编辑时间"); //编辑时间
#endregion
font.Boldweight = 5;
}
MaterialReceiptDetail obj = lst[j];
MaterialReceipt mrObj = model.GetMaterialReceipt(obj.Key.ReceiptNo);
Material m = model.GetMaterial(obj.MaterialCode);
Supplier s = model.GetSupplier(obj.SupplierCode);
row = ws.CreateRow(j + 1);
#region //数据
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Key.ReceiptNo); //领料号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(mrObj == null ? string.Empty : mrObj.OrderNumber); //工单号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(mrObj == null ? string.Empty : string.Format("{0:yyyy-MM-dd}", mrObj.ReceiptDate)); //领料日期
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Key.ItemNo); //项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.LineStoreName); //线别仓
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.MaterialCode); //物料编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(m == null ? string.Empty : m.Name); //物料名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.MaterialLot); //物料批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Qty); //数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.SupplierMaterialLot); //供应商批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.SupplierCode); //供应商编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(s == null ? string.Empty : s.Name); //供应商名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Attr1); //效率档
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Description); //描述
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Editor); //编辑人
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.EditTime)); //编辑时间
#endregion
}
MemoryStream ms = new MemoryStream();
wb.Write(ms);
ms.Flush();
ms.Position = 0;
return(File(ms, "application/vnd.ms-excel", "MaterialReceiptData.xls"));
}
public static void OutputExcel(DataGridView dgv, string Title, string savePath, string sSTName, bool IsConver)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
{
int num = 5;
int num2 = 0x106;
DocumentSummaryInformation information = PropertySetFactory.CreateDocumentSummaryInformation();
information.Company = "";
workbook.DocumentSummaryInformation = information;
SummaryInformation information2 = PropertySetFactory.CreateSummaryInformation();
information2.Subject = "";
workbook.SummaryInformation = information2;
if (sSTName.Length > 0x1f)
{
sSTName = "报表";
}
ISheet sheet = workbook.CreateSheet(sSTName);
int rownum = 3;
int num4 = 0;
int num5 = 0;
int num6 = 1;
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.ShrinkToFit = true;
NPOI.SS.UserModel.IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
IRow row = sheet.CreateRow(rownum);
num5 = num4;
foreach (DataGridViewColumn column in dgv.Columns)
{
if (column.Visible && (column.CellType != typeof(DataGridViewCheckBoxCell)))
{
num5++;
ICell cell = row.CreateCell(num5);
cell.SetCellValue(column.HeaderText);
cell.CellStyle = style;
style.WrapText = true;
sheet.AutoSizeColumn(num5);
}
}
if (IsConver)
{
for (int i = dgv.Rows.Count - 1; i >= 0; i--)
{
DataGridViewRow row2 = dgv.Rows[i];
if (!row2.IsNewRow)
{
rownum++;
num5 = num4;
IRow row3 = sheet.CreateRow(rownum);
foreach (DataGridViewColumn column2 in dgv.Columns)
{
if (column2.Visible && (column2.CellType != typeof(DataGridViewCheckBoxCell)))
{
num5++;
if ((row2.Cells[column2.Index].Value != null) && !row2.IsNewRow)
{
row3.CreateCell(num5).SetCellValue(row2.Cells[column2.Index].Value.ToString());
int num8 = sheet.GetColumnWidth(row3.GetCell(num5).ColumnIndex);
int num9 = (Encoding.GetEncoding("gb2312").GetBytes(row2.Cells[column2.Index].Value.ToString()).Length + num) * num2;
num8 = (num9 < num8) ? num8 : num9;
sheet.SetColumnWidth(num5, num8);
if (column2.CellType == typeof(DataGridViewTextBoxCell))
{
sheet.GetRow(rownum).GetCell(num5).CellStyle = style;
}
}
}
}
}
}
}
else
{
foreach (DataGridViewRow row4 in (IEnumerable)dgv.Rows)
{
if (!row4.IsNewRow)
{
rownum++;
num5 = num4;
IRow row5 = sheet.CreateRow(rownum);
foreach (DataGridViewColumn column3 in dgv.Columns)
{
if (column3.Visible && (column3.CellType != typeof(DataGridViewCheckBoxCell)))
{
num5++;
if ((row4.Cells[column3.Index].Value != null) && !row4.IsNewRow)
{
row5.CreateCell(num5).SetCellValue(row4.Cells[column3.Index].Value.ToString());
int num10 = sheet.GetColumnWidth(row5.GetCell(num5).ColumnIndex);
int num11 = (Encoding.GetEncoding("gb2312").GetBytes(row4.Cells[column3.Index].Value.ToString()).Length + num) * num2;
num10 = (num11 < num10) ? num10 : num11;
sheet.SetColumnWidth(num5, num10);
if (column3.CellType == typeof(DataGridViewTextBoxCell))
{
sheet.GetRow(rownum).GetCell(num5).CellStyle = style;
}
}
}
}
continue;
}
}
}
int num12 = rownum + 1;
num5 = num4;
foreach (DataGridViewColumn column4 in dgv.Columns)
{
if (column4.Visible)
{
num5++;
}
}
num12 = rownum + 2;
IRow row6 = sheet.CreateRow(num12);
ICell cell2 = row6.CreateCell(1);
cell2.SetCellValue("合计:");
ICellStyle style2 = workbook.CreateCellStyle();
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style2.VerticalAlignment = VerticalAlignment.CENTER;
cell2.CellStyle = style2;
int columnWidth = sheet.GetColumnWidth(1);
int num14 = (Encoding.GetEncoding("gb2312").GetBytes("合计:").Length + num) * num2;
columnWidth = (num14 < columnWidth) ? columnWidth : num14;
sheet.SetColumnWidth(1, columnWidth);
cell2 = row6.CreateCell(2);
cell2.SetCellValue(dgv.Rows.Count.ToString() + "条记录");
style2 = workbook.CreateCellStyle();
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
style2.VerticalAlignment = VerticalAlignment.CENTER;
cell2.CellStyle = style2;
int width = sheet.GetColumnWidth(2);
int num16 = (Encoding.GetEncoding("gb2312").GetBytes(dgv.Rows.Count.ToString() + "条记录").Length + num) * num2;
width = (num16 < columnWidth) ? width : num16;
sheet.SetColumnWidth(2, width);
num6 = 1;
ICell cell3 = sheet.CreateRow(1).CreateCell(num6);
cell3.SetCellValue(Title);
ICellStyle style3 = workbook.CreateCellStyle();
style3.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style3.VerticalAlignment = VerticalAlignment.CENTER;
NPOI.SS.UserModel.IFont font2 = workbook.CreateFont();
font2.FontName = "宋体";
font2.FontHeightInPoints = 11;
style3.SetFont(font2);
cell3.CellStyle = style3;
sheet.AddMergedRegion(new CellRangeAddress(1, 1, num6, num5));
sheet.GetRow(1).HeightInPoints = 35f;
if (string.IsNullOrEmpty(savePath))
{
savePath = @"C:\Documents and Settings\Administrator\My Documents\Report.xls";
}
FileStream stream = new FileStream(savePath, FileMode.Create);
workbook.Write(stream);
stream.Close();
MessageBox.Show("数据已经成功导出到：" + savePath, "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
Record.execFileRecord("导出报表", exception.ToString());
}
}
private static void CreateApplyStyleToCell(IRow row, int columnId, ICellStyle style, string value)
{
var cell = row.GetCell(columnId) ?? row.CreateCell(columnId);
cell.SetCellValue(value);
cell.CellStyle = style;
}
public void PrintSheetTable(int rowStartIndexIn, int maxRowsIn, SimpleExcelColumn[] columnsIn, IEnumerable <object> reportObjectsIn)
{
int highestIndex = 0;
bool overflow = false;
// Create headers.
int rowIndex = rowStartIndexIn;
IRow row = this.sheet.CreateRow(rowIndex);
foreach (SimpleExcelColumn column in columnsIn)
{
row.CreateCell(column.Index).SetCellValue(column.Title);
row.GetCell(column.Index).CellStyle = column.TitleStyle;
// Figure out the highest index for later.
if (column.Index > highestIndex)
{
highestIndex = column.Index;
}
}
foreach (object rowIter in reportObjectsIn)
{
// Create the row for this connection.
rowIndex++;
if (this.maxRows <= rowIndex)
{
overflow = true;
break;
}
row = this.sheet.CreateRow(rowIndex);
foreach (SimpleExcelColumn column in columnsIn)
{
string cellString = column.CellPrintCallback(rowIter).Trim();
int cellNumber;
if (column.BlankIsUnknown && string.IsNullOrEmpty(cellString))
{
row.CreateCell(column.Index).SetCellValue("Unknown");
}
else if (int.TryParse(cellString, out cellNumber))
{
row.CreateCell(column.Index).SetCellValue(cellNumber);
}
else
{
row.CreateCell(column.Index).SetCellValue(cellString);
}
if (null != column.CellStyleCallback)
{
row.GetCell(column.Index).CellStyle = column.CellStyleCallback(rowIter);
}
}
}
// Size the columns.
foreach (SimpleExcelColumn column in columnsIn)
{
if (SimpleExcelColumnSize.Automatic == column.SizeMethod)
{
AutoSizeColumn(this.sheet, column.Index);
}
else if (0 <= column.SizeWidth)
{
this.sheet.SetColumnWidth(column.Index, column.SizeWidth);
}
}
// Turn on filtering/sorting.
sheet.SetAutoFilter(new CellRangeAddress(
rowStartIndexIn,
row.RowNum,
0,
highestIndex
));
if (overflow)
{
throw new SimpleExcelSheetOverflowException("More rows specified than current format allows.");
}
}
private static void CreateCell(string value, IRow row, int index, ICellStyle cellStyle)
{
ICell cell = row.CreateCell(index);
cell.SetCellValue(value);
cell.CellStyle = cellStyle;
}
protected void btnExcel_Click(object sender, EventArgs e)
{
_cusName = DTRequest.GetString("txtCusName");
_cid = DTRequest.GetString("hCusId");
_method = DTRequest.GetFormString("ddlmethod");
_isconfirm = DTRequest.GetFormString("ddlisConfirm");
_sforedate = DTRequest.GetFormString("txtsforedate");
_eforedate = DTRequest.GetFormString("txteforedate");
_sdate = DTRequest.GetFormString("txtsdate");
_edate = DTRequest.GetFormString("txtedate");
_num = DTRequest.GetFormString("txtNum");
_chk = DTRequest.GetFormString("txtChk");
BLL.ReceiptPay bll = new BLL.ReceiptPay();
DataTable dt = bll.GetList(this.pageSize, this.page, "rp_type=1 " + CombSqlTxt(), "isnull(rp_date,'3000-01-01') desc,isnull(pm_sort,-1) asc,rp_id desc", out this.totalCount, out _tmoney, out _tunmoney, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=收款通知列表.xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("明细");
IFont font = hssfworkbook.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 11;
#region 表格样式
//设置单元格的样式：水平垂直对齐居中
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BottomBorderColor = HSSFColor.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.Black.Index;
cellStyle.RightBorderColor = HSSFColor.Black.Index;
cellStyle.TopBorderColor = HSSFColor.Black.Index;
cellStyle.WrapText = true;//自动换行
//设置表头的样式：水平垂直对齐居中，加粗
ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle();
titleCellStyle.Alignment = HorizontalAlignment.Center;
titleCellStyle.VerticalAlignment = VerticalAlignment.Center;
titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色
titleCellStyle.FillPattern = FillPattern.SparseDots; //图案样式
titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色
//设置边框
titleCellStyle.BorderBottom = BorderStyle.Thin;
titleCellStyle.BorderLeft = BorderStyle.Thin;
titleCellStyle.BorderRight = BorderStyle.Thin;
titleCellStyle.BorderTop = BorderStyle.Thin;
titleCellStyle.BottomBorderColor = HSSFColor.Black.Index;
titleCellStyle.LeftBorderColor = HSSFColor.Black.Index;
titleCellStyle.RightBorderColor = HSSFColor.Black.Index;
titleCellStyle.TopBorderColor = HSSFColor.Black.Index;
//设置字体
titleCellStyle.SetFont(font);
#endregion
//表头
IRow headRow = sheet.CreateRow(0);
headRow.HeightInPoints = 25;
headRow.CreateCell(0).SetCellValue("收款对象");
headRow.CreateCell(1).SetCellValue("凭证");
headRow.CreateCell(2).SetCellValue("收款内容");
headRow.CreateCell(3).SetCellValue("收款金额");
headRow.CreateCell(4).SetCellValue("未分配金额");
headRow.CreateCell(5).SetCellValue("预收日期");
headRow.CreateCell(6).SetCellValue("收款方式");
headRow.CreateCell(7).SetCellValue("实收日期");
headRow.CreateCell(8).SetCellValue("申请人");
headRow.CreateCell(9).SetCellValue("确认收款");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
headRow.GetCell(3).CellStyle = titleCellStyle;
headRow.GetCell(4).CellStyle = titleCellStyle;
headRow.GetCell(5).CellStyle = titleCellStyle;
headRow.GetCell(6).CellStyle = titleCellStyle;
headRow.GetCell(7).CellStyle = titleCellStyle;
headRow.GetCell(8).CellStyle = titleCellStyle;
headRow.GetCell(9).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 15 * 256);
sheet.SetColumnWidth(6, 20 * 256);
sheet.SetColumnWidth(7, 20 * 256);
sheet.SetColumnWidth(8, 20 * 256);
sheet.SetColumnWidth(9, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(dt.Rows[i]["c_name"].ToString() + (Utils.StrToBool(Utils.ObjectToStr(dt.Rows[i]["rp_isExpect"]), false) ? "[预]" : ""));
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["ce_num"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_content"]));
row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_money"]));
row.CreateCell(4).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["undistribute"]));
row.CreateCell(5).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rp_foredate"]).Value.ToString("yyyy-MM-dd"));
row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["pm_name"]));
row.CreateCell(7).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rp_date"]) == null ? "" : ConvertHelper.toDate(dt.Rows[i]["rp_date"]).Value.ToString("yyyy-MM-dd"));
row.CreateCell(8).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_personName"]));
row.CreateCell(9).SetCellValue(Utils.StrToBool(Utils.ObjectToStr(dt.Rows[i]["rp_isConfirm"]), false) ? "已收款" : "待收款");
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
row.GetCell(3).CellStyle = cellStyle;
row.GetCell(4).CellStyle = cellStyle;
row.GetCell(5).CellStyle = cellStyle;
row.GetCell(6).CellStyle = cellStyle;
row.GetCell(7).CellStyle = cellStyle;
row.GetCell(8).CellStyle = cellStyle;
row.GetCell(9).CellStyle = cellStyle;
}
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
//Datatable导出Excel
public static void DatatableToExcelByNPOI(DataTable dt, string strExcelFileName)
{
HSSFWorkbook workbook = null;
try
{
workbook = new HSSFWorkbook();
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);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.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 = 1;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dt.Columns)
{
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();
//MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_successfully"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
//ILog log = LogManager.GetLogger("Exception Log");
_logger.Error(ex.Message + Environment.NewLine + ex.StackTrace);
//记录AuditTrail
//CCFS.Framework.BLL.AuditTrailBLL.LogAuditTrail(ex);
//MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_failed"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally { workbook = null; }
}
/// <summary>
///
/// </summary>
/// <typeparam name="T">必须是有构造函数的实体类</typeparam>
/// <param name="dtSource"></param>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
/// <param name="writeColumnName"></param>
/// <param name="excelType"></param>
public static void WriteStringsInToExcel(this IEnumerable <string> dtSource, string fileName, string sheetName, bool writeColumnName = true, string columnName = "Null Column Title", ExcelType excelType = ExcelType.XLS)
{
FileStream fs = null;
IWorkbook workbook = null;
ISheet sheet = null;
try
{
#region 初始化
if (excelType == ExcelType.XLS)
{
workbook = new HSSFWorkbook();
if (fileName.Substring(fileName.Length - 4, 4).ToLower() != ".xls")
{
fileName = fileName + ".xls";
}
}
else
{
workbook = new XSSFWorkbook();
if (fileName.Substring(fileName.Length - 4, 4).ToLower() != ".xls")
{
fileName = fileName + ".xlsx";
}
}
if (workbook != null)
{
if (sheet == null)
{
sheet = workbook.CreateSheet(sheetName);
}
}
fs = new FileStream(fileName + ".", FileMode.OpenOrCreate, FileAccess.ReadWrite);
#endregion
var list = dtSource.ToList();
#region 写列名称和内容
if (writeColumnName)
{
list.Insert(0, columnName);
}
var rowBeginIndex = 0;
list.ForEach(x => {
IRow row = sheet.CreateRow(rowBeginIndex);
var cell = row.CreateCell(0, CellType.String);
cell.CellStyle.WrapText = true;
if (rowBeginIndex == 0 && writeColumnName)
{
ICellStyle style = workbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中，有边框
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = HSSFColor.White.Index;
style.FillBackgroundColor = HSSFColor.Red.Index; //指定背景颜色
//将新的样式赋给单元格
cell.CellStyle = style;
}
cell.SetCellValue(x);
rowBeginIndex++;
});
#endregion
workbook.Write(fs); //写入到excel
}
catch (Exception ex)
{
throw ex;
}
finally
{
fs.Dispose();
fs.Close();
}
}
/// <summary>
/// 处理每个工作表数据
/// </summary>
/// <param name="dtData"></param>
/// <param name="helpEntity"></param>
/// <returns></returns>
private string BuildSinpleSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtData)
{
string errorMessage = string.Empty;
// 在工作簿建立空白工作表
ISheet sheet = null;
if (!string.IsNullOrEmpty(sheetEntity.SheetName))
{
sheet = workBook.CreateSheet(sheetEntity.SheetName);
}
else
{
sheet = workBook.CreateSheet();
}
// 看是否有跳过
int beginRow = 0 + helpEntity.SkipRowNum;
int beginCol = 0 + helpEntity.SkipColNum;
IRow rowHead = sheet.CreateRow(beginRow);
// 循环添加表头
int colIndex = beginCol;
foreach (var item in helpEntity.ExportColumns)
{
if (item.Hidden)
{
continue;
}
ICell cell = rowHead.CreateCell(colIndex);
cell.SetCellValue(item.ExcelName);
cell.CellStyle = item.CellStyle;
sheet.SetColumnWidth(colIndex, item.Width);
rowHead.Height = helpEntity.THeight;
IName iname = workBook.CreateName();
iname.NameName = item.ColumnName;
iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1);
colIndex++;
}
if (helpEntity.FreezeTitleRow)
{
sheet.CreateFreezePane(0, 0 + helpEntity.SkipRowNum + 1, 0, helpEntity.SkipRowNum + 1);
}
colIndex = beginCol;
beginRow++;
//循环赋值内容
foreach (DataRow dr in dtData.Rows)
{
IRow rowContent = sheet.CreateRow(beginRow);
ICell cell = null;
colIndex = beginCol;
foreach (var item in helpEntity.ExportColumns)
{
if (item.Hidden)
{
continue;
}
cell = rowContent.CreateCell(colIndex);
object curValue = dr[item.ColumnName];
cell.SetCellValue(curValue, item, item.CellStyle);
colIndex++;
}
rowContent.Height = (short)helpEntity.CHeight;
beginRow++;
}
// 筛选
if (helpEntity.AutoFilter)
{
CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum, 0 + helpEntity.SkipRowNum, beginCol, colIndex);
sheet.SetAutoFilter(c);
}
sheet.DisplayGridlines = helpEntity.ShowGridLine;
ProcessSheet(sheet, helpEntity);
return(errorMessage);
}
public void WriteXLSX(DataSet ds, string path, Estilo estilo)
{
//Cria o arquivo Excel
IWorkbook workbook = new XSSFWorkbook();
foreach (DataTable dt in ds.Tables)
{
//Cria uma planilha dentro do arquivo e define o nome
ISheet sheet = workbook.CreateSheet(dt.TableName);
int colIndex = 0;
IRow linha = sheet.CreateRow(0);
foreach (DataColumn dc in dt.Columns)
{
ICell cell = linha.CreateCell(colIndex);
cell.CellStyle = BuscaEstiloCabecalho(estilo, workbook);
cell.SetCellValue(dc.ColumnName);
colIndex++;
}
int rowIndex = 1;
foreach (DataRow dr in dt.Rows)
{
linha = sheet.CreateRow(rowIndex);
colIndex = 0;
foreach (DataColumn dc in dt.Columns)
{
ICell cell = linha.CreateCell(colIndex);
cell.CellStyle = BuscaEstiloLinhas(estilo, workbook);
cell.SetCellValue(dr[dc].ToString());
colIndex++;
}
rowIndex++;
}
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
}
CultureInfo ci = new CultureInfo("en-US");
Thread.CurrentThread.CurrentCulture = ci;
Thread.CurrentThread.CurrentUICulture = ci;
//salva o arquivo
FileStream file;
file = new FileStream(path, FileMode.Create, FileAccess.Write);
workbook.Write(file);
file.Close();
}
private string BuildMergeSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtData)
{
string errorMessage = string.Empty;
// 设置开始行、列
int beginRow = 0 + helpEntity.SkipRowNum;
int beginCol = 0 + helpEntity.SkipColNum;
ISheet sheet = workBook.CreateSheet();
#region 标题处理
IRow rowTitle1 = sheet.CreateRow(beginRow);
rowTitle1.Height = helpEntity.THeight;
int colIndex = beginCol;
// 先赋值合并行标题
foreach (var mergeName in sheetEntity.MergeNames)
{
var temp = helpEntity.ExportColumns.Where(t => t.MergeName == mergeName);
var len = temp.Count();
// 获取合并标题开始列位置
int index = GetIndex(helpEntity.ExportColumns, mergeName);
colIndex = index;
ICell cellMerge = rowTitle1.CreateCell(colIndex);
cellMerge.SetCellValue(mergeName);
//cellMerge.CellStyle = cellStyleHead;
// 设置合并
sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, colIndex, colIndex + len - 1));
//for (int tempIndex = colIndex; tempIndex < colIndex + len; tempIndex++)
// HSSFCellUtil.GetCell(rowTitle1, tempIndex).CellStyle = cellStyleHead;
}
beginRow++;
IRow rowTitle2 = sheet.CreateRow(beginRow);
rowTitle2.Height = helpEntity.THeight;
colIndex = beginCol;
foreach (var item in helpEntity.ExportColumns)
{
ICell cellTitle = rowTitle2.CreateCell(colIndex);
cellTitle.SetCellValue(item.ExcelName);
cellTitle.CellStyle = item.CellStyle;
// 如果此标题在上述没有合并，则向上合并一列，保持美观
if (string.IsNullOrEmpty(item.MergeName))
{
sheet.AddMergedRegion(new CellRangeAddress(beginRow - 1, beginRow, colIndex, colIndex));
ICell cellTemp = HSSFCellUtil.GetCell(rowTitle1, colIndex);
//cellTemp.CellStyle = cellStyleHead;
cellTemp.SetCellValue(item.ExcelName);
}
sheet.SetColumnWidth(colIndex, item.Width);
colIndex++;
}
// 冻结标题行
if (helpEntity.FreezeTitleRow)
{
sheet.CreateFreezePane(beginCol + helpEntity.MergeColNum, 2, beginCol + helpEntity.MergeColNum, 2);
}
#endregion
beginRow++;
// 循环赋值列表内容
foreach (DataRow dr in dtData.Rows)
{
IRow rowContent = sheet.CreateRow(beginRow);
rowContent.Height = helpEntity.CHeight;
colIndex = beginCol;
ICell cell = null;
// 赋值内容
foreach (var item in helpEntity.ExportColumns)
{
if (item.Hidden)
{
continue;
}
cell = rowContent.CreateCell(colIndex);
object curValue = dr[item.ColumnName];
cell.SetCellValue(curValue, item, item.CellStyle);
colIndex++;
}
rowContent.Height = helpEntity.CHeight;
beginRow++;
}
// 筛选
if (helpEntity.AutoFilter)
{
CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum + 1, 0 + helpEntity.SkipRowNum + 1, beginCol, colIndex);
sheet.SetAutoFilter(c);
}
sheet.DisplayGridlines = helpEntity.ShowGridLine;
ProcessSheet(sheet, helpEntity);
return(errorMessage);
}
private static void WriteHeaderCell(IRow row, int i, String text, ICellStyle style)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(new HSSFRichTextString(text));
cell.CellStyle = (style);
}
private string BuildRecSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataRow drMain, DataTable dtSub)
{
string errorMessage = string.Empty;
ISheet sheet = null;
if (!string.IsNullOrEmpty(sheetEntity.SheetName))
{
sheet = workBook.CreateSheet(sheetEntity.SheetName);
}
else
{
sheet = workBook.CreateSheet();
}
// 设置开始行和列
int beginRow = 0 + helpEntity.SkipRowNum;
int beginCol = 0 + helpEntity.SkipColNum;
#region 对表头数据进行赋值
// 标题行
IRow rowTitle = sheet.CreateRow(beginRow);
ICell cellTitle = rowTitle.CreateCell(beginCol);
cellTitle.SetCellValue(sheetEntity.SheetTitle);
// 设置下划线
IFont fontLine = workBook.CreateFont();
fontLine.Underline = FontUnderlineType.Single;
if (helpEntity.TitleBoldMark == true)
{
fontLine.FontHeight = (double)FontBoldWeight.Bold;
}
cellTitle.CellStyle.SetFont(fontLine);
cellTitle.CellStyle.Alignment = HorizontalAlignment.Center;
cellTitle.CellStyle.VerticalAlignment = VerticalAlignment.Center;
rowTitle.Height = helpEntity.THeight;
// 标题行合并
sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, beginCol, beginCol + helpEntity.ExportColumns.Count() - 1));
beginRow++;
// 表头字段集合
var masterColumn = helpEntity.ExportColumns.Where(t => t.PrimaryMark == true);
var subColumn = helpEntity.ExportColumns.Where(t => t.PrimaryMark == false);
int maxColIndex = masterColumn.Select(t => t.ColIndex).Max();
int minColIndex = masterColumn.Select(t => t.ColIndex).Min();
var listTitleContent = masterColumn;
var temp = masterColumn.Select(t => t.RowIndex).Distinct();
int colIndex = beginCol;
// 循环赋值表头数据
foreach (var rowIndex in temp)
{
IRow rowContent = sheet.CreateRow(beginRow);
var curRow = listTitleContent.Where(t => t.RowIndex == rowIndex).OrderBy(t => t.ColIndex);
colIndex = beginCol;
var curColMinIndex = curRow.Select(t => t.ColIndex).Min();
var diff = curColMinIndex - minColIndex;
// 此处判断主要是为了有跳过列的
if (diff > 0)
{
// 标题
colIndex += diff;
// 内容
colIndex += diff;
}
foreach (var item in curRow)
{
colIndex = colIndex - item.diffNum;
// 先赋值标题，再赋值
ICell curTitle = rowContent.CreateCell(colIndex);
curTitle.SetCellValue(item.ExcelName);
curTitle.CellStyle = item.CellStyle;
IName iname = workBook.CreateName();
iname.NameName = item.ColumnName;
iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1);
colIndex++;
ICell cell = rowContent.CreateCell(colIndex);
string curColumn = item.ColumnName;
object curValue = drMain[curColumn];
if (item.TitleColSpan > 1)
{
sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, colIndex, colIndex + item.TitleColSpan - 1));
colIndex++;
}
cell.SetCellValue(curValue, item, item.CellStyle);
colIndex++;
}
beginRow++;
}
#endregion
// 获取和循环设置表体表体行
var listTitle = helpEntity.ExportColumns;
IRow rowSubTitle = sheet.CreateRow(beginRow);
colIndex = beginCol;
foreach (var item in listTitle)
{
ICell cellSubTitle = rowSubTitle.CreateCell(colIndex);
cellSubTitle.SetCellValue(item.ExcelName);
sheet.SetColumnWidth(colIndex, item.Width);
colIndex++;
}
beginRow++;
// 冻结上述行和列
if (helpEntity.FreezeTitleRow)
{
sheet.CreateFreezePane(beginCol, beginRow - 1, beginCol, beginRow - 1);
}
// 循环赋值列表数据
foreach (DataRow dr in dtSub.Rows)
{
IRow rowSubContent = sheet.CreateRow(beginRow);
colIndex = beginCol;
foreach (var item in listTitle)
{
ICell cellSubContent = rowSubContent.CreateCell(colIndex);
object curValue = dr[item.ColumnName];
// 设置表体内容
cellSubContent.SetCellValue(curValue, item, item.CellStyle);
colIndex++;
}
beginRow++;
}
// 筛选
if (helpEntity.AutoFilter)
{
CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum + 1, 0 + helpEntity.SkipRowNum + 1, beginCol, colIndex);
sheet.SetAutoFilter(c);
}
sheet.DisplayGridlines = helpEntity.ShowGridLine;
ProcessSheet(sheet, helpEntity);
return(errorMessage);
}
internal static void SetCellTopBorder(IWorkbook workbook, IRow row, int p1, int p2, List<int> list)
{
for (int i = p1; i <= p2; i++)
{
if (!list.Contains(i))
{
ICellStyle style = workbook.CreateCellStyle();
ICell cell = row.CreateCell(i);
style.BorderTop = BorderStyle.Thin;
cell.CellStyle = style;
}
}
}
private string BuildBillSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtMain, DataTable[] dtSub)
{
string errorMessage = string.Empty;
// 在工作簿建立空白工作表
ISheet sheet = null;
if (!string.IsNullOrEmpty(sheetEntity.SheetName))
{
sheet = workBook.CreateSheet(sheetEntity.SheetName);
}
else
{
sheet = workBook.CreateSheet();
}
// 看是否有跳过
int beginRow = 0 + helpEntity.SkipRowNum;
int beginCol = 0 + helpEntity.SkipColNum;
IRow rowHead = sheet.CreateRow(beginRow);
// 循环添加表头
int colIndex = beginCol;
var tempMast = helpEntity.ExportColumns.Where(t => t.PrimaryMark == true);
var tempSub = helpEntity.ExportColumns.Where(t => t.PrimaryMark == false);
var tempCol = tempMast.Union(tempSub);
foreach (var item in tempCol)
{
if (item.Hidden)
{
continue;
}
ICell cell = rowHead.CreateCell(colIndex);
cell.SetCellValue(item.ExcelName);
cell.CellStyle = item.CellStyle;
sheet.SetColumnWidth(colIndex, item.Width);
rowHead.Height = helpEntity.THeight;
IName iname = workBook.CreateName();
iname.NameName = item.ColumnName;
iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1);
colIndex++;
}
if (helpEntity.FreezeTitleRow)
{
sheet.CreateFreezePane(0, 0 + helpEntity.SkipRowNum + 1, 0, helpEntity.SkipRowNum + 1);
}
colIndex = beginCol;
beginRow++;
//循环赋值内容
for (int i = 0; i < dtMain.Rows.Count; i++)
{
DataRow drMain = dtMain.Rows[i];
DataTable dtData = dtSub[i];
for (int j = 0; j < dtData.Rows.Count; j++)
{
DataRow drSub = dtData.Rows[j];
IRow row = sheet.CreateRow(beginRow);
ICell cell = null;
colIndex = beginCol;
foreach (var col in tempCol)
{
if (col.Hidden)
{
continue;
}
cell = row.CreateCell(colIndex);
object value = null;
if (col.PrimaryMark == true)
{
value = drMain[col.ColumnName];
if (helpEntity.OneMain == true && j > 0)
{
colIndex++;
continue;
}
}
value = drSub[col.ColumnName];
cell.SetCellValue(value, col, col.CellStyle);
colIndex++;
}
beginRow++;
}
}
// 筛选
if (helpEntity.AutoFilter)
{
CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum, 0 + helpEntity.SkipRowNum, beginCol, colIndex);
sheet.SetAutoFilter(c);
}
sheet.DisplayGridlines = helpEntity.ShowGridLine;
ProcessSheet(sheet, helpEntity);
return(errorMessage);
}
/// <summary>
/// 导出加工工艺表
/// </summary>
/// <param name="partIds"></param>
/// <returns></returns>
public virtual async Task <string> ExportPartProcessInfo(int[] partIds)
{
var parts = await Manager.GetAll()
.Include(o => o.Project).ThenInclude(o => o.Unit)
.Include("ProcessTasks.ProcessType")
.Where(o => partIds.Contains(o.Id))
.Select(o => new {
o.Project.ProjectSN,
o.Project.ProjectName,
ProjectCharger = o.Project.GetPropertyValue <string>("ProjectCharger"),
UnitName = o.Project.Unit != null?o.Project.Unit.UnitName:"",
o.PartName,
o.PartImg,
o.PartSpecification,
Tasks = o.ProcessTasks.Select(t => new {
t.ProcessType.ProcessTypeName,
t.AppointDate,
t.RequireDate,
t.StartDate,
t.EndDate
})
})
.ToListAsync();
#region 导出方法
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("模具制作工艺单");
sheet.DefaultColumnWidth = 300 * 256;//貌似没有作用
var rowIndex = 0;
var cellIndex = 0;
//模具信息行
IRow row = sheet.CreateRow(rowIndex++);
parts.ForEach(o => {
row.CreateCell(cellIndex++).SetCellValue(o.ProjectSN);
row.CreateCell(cellIndex++).SetCellValue(o.ProjectName);
row.CreateCell(cellIndex++).SetCellValue(o.ProjectCharger);
row.CreateCell(cellIndex++).SetCellValue(o.UnitName);
});
//零件信息行
row = sheet.CreateRow(rowIndex++);
cellIndex = 0;
parts.ForEach(o => {
row.CreateCell(cellIndex++).SetCellValue(o.PartName);
row.CreateCell(cellIndex++).SetCellValue(o.PartSpecification);
row.CreateCell(cellIndex++).SetCellValue("");
row.CreateCell(cellIndex++).SetCellValue("");
});
//图片行
row = sheet.CreateRow(rowIndex++);
row.Height = 80 * 20;
cellIndex = 0;
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
parts.ForEach(o => {
sheet.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex - 1, cellIndex, cellIndex + 3));
if (!string.IsNullOrEmpty(o.PartImg))
{
try
{
byte[] bytes = System.IO.File.ReadAllBytes(Common.PathHelper.VirtualPathToAbsolutePath(o.PartImg));
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, cellIndex, rowIndex - 1, cellIndex + 4, rowIndex);
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
catch (Exception ex)
{
}
}
cellIndex += 4;
});
//工序表头
row = sheet.CreateRow(rowIndex++);
cellIndex = 0;
parts.ForEach(o =>
{
row.CreateCell(cellIndex++).SetCellValue("序号");
row.CreateCell(cellIndex++).SetCellValue("加工流程");
row.CreateCell(cellIndex++).SetCellValue("计划时间");
row.CreateCell(cellIndex++).SetCellValue("实际时间");
});
//工序内容
var maxProcessTypeCount = parts.Max(o => o.Tasks.Count());
for (var i = 0; i < maxProcessTypeCount; i++)
{
//先构建下面所有行
sheet.CreateRow(rowIndex + i);
}
cellIndex = 0;
for (var i = 0; i < parts.Count; i++)
{
var part = parts[i];
for (var j = 0; j < part.Tasks.Count(); j++)
{
var task = part.Tasks.ElementAt(j);
row = sheet.GetRow(rowIndex + j);
row.CreateCell(i * 4).SetCellValue(j + 1);
row.CreateCell(i * 4 + 1).SetCellValue(task.ProcessTypeName);
row.CreateCell(i * 4 + 2).SetCellValue(task.AppointDate?.ToString("MM-dd") + "-" + task.RequireDate?.ToString("MM-dd"));
row.CreateCell(i * 4 + 3).SetCellValue(task.StartDate?.ToString("MM-dd") + "-" + task.EndDate?.ToString("MM-dd"));
}
}
//边框
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BottomBorderColor = HSSFColor.Black.Index;
style.LeftBorderColor = HSSFColor.Black.Index;
style.RightBorderColor = HSSFColor.Black.Index;
style.TopBorderColor = HSSFColor.Black.Index;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
for (int j = 0; j <= row.LastCellNum; j++)
{
var cell = row.GetCell(j);
if (cell != null)
{
cell.CellStyle = style;
}
}
}
#endregion
var filePath = $"/Temp/{Guid.NewGuid()}.xls";
System.IO.Directory.CreateDirectory(Common.PathHelper.VirtualPathToAbsolutePath("/Temp/"));
using (var fs = new FileStream(Common.PathHelper.VirtualPathToAbsolutePath(filePath), FileMode.Create, FileAccess.ReadWrite))
{
workbook.Write(fs); //写入到excel
}
workbook = null;
return(filePath);
}
/// <summary>
/// 获取单元格，如果不存在则创建
/// </summary>
private ICell GetOrCreateCell( IRow row, int columnIndex ) {
return row.GetCell( columnIndex ) ?? row.CreateCell( columnIndex );
}
private static ICell GetOrCreateCell (IRow row, int cellIndex)
{
var cell = row.GetCell (cellIndex);
return cell ?? row.CreateCell (cellIndex);
}
/// <summary>
/// 写入数据到某一行
/// </summary>
/// <param name="row"></param>
/// <param name="startColNumber"></param>
/// <param name="data"></param>
/// <param name="cols"></param>
public void WriteRow(IRow row, int startColNumber, DataColumnCollection cols, DataRow data)
{
for (int j = 0; j < cols.Count; j++)
{
var cell = row.CreateCell(startColNumber + j);
CellDataType cellDataType = CellDataType.None;
if (data[j] != null)
{
cellDataType = GetCellDataTypeByColumn(cols[j]);
}
WriteCell(cell, data[j], cellDataType);
}
}
/// <summary>
/// 根据datarow 创建 cells.
/// </summary>
/// <param name="excelRow"></param>
/// <param name="columns"></param>
/// <param name="row">如果为null 则该行所有cell的值为空</param>
/// <param name="isHead">如果是true 则创建表头.</param>
private void CreateCellForRow(IRow excelRow, DataColumnCollection columns, DataRow row,bool isHead)
{
for (int i=0;i<columns.Count;i++)
{
var cell = excelRow.CreateCell(i);
if (isHead)
{
cell.SetCellValue(columns[i].ColumnName);
}
else
{
string cellValue = string.Empty;
if (row != null) { cellValue = row[i].ToString(); }
cell.SetCellValue(cellValue);
}
}
}
/// <summary>NPOI新增儲存格資料</summary>
/// <param name="Word">顯示文字</param>
/// <param name="ContentRow">NPOI IROW</param>
/// <param name="CellIndex">儲存格列數</param>
/// <param name="cellStyleBoder">ICellStyle樣式</param>
private static void CreateCell(string Word, IRow ContentRow, int CellIndex, ICellStyle cellStyleBoder)
{
ICell _cell = ContentRow.CreateCell(CellIndex);
_cell.SetCellValue(Word);
_cell.CellStyle = cellStyleBoder;
}
public MemoryStream GetExcelReport(Report report, Dictionary <string, dynamic> parameters)
{
InitializeWorkbook();
var _date = DateTime.Now;
#region
ICellStyle journal_name_style = _workbook.CreateCellStyle();
journal_name_style.Alignment = HorizontalAlignment.Center;
//create a font style
IFont font = _workbook.CreateFont();
font.FontHeight = 20 * 20;
journal_name_style.SetFont(font);
journal_name_style.BorderRight = BorderStyle.Thin;
journal_name_style.BorderBottom = BorderStyle.Thin;
ICellStyle journal_period_style = _workbook.CreateCellStyle();
journal_period_style.Alignment = HorizontalAlignment.Center;
//create a font style
IFont font1 = _workbook.CreateFont();
font1.FontHeight = 14 * 14;
journal_period_style.SetFont(font1);
ICellStyle header = _workbook.CreateCellStyle();
header.VerticalAlignment = VerticalAlignment.Top;
header.WrapText = true;
header.BorderRight = BorderStyle.Thin;
header.BorderBottom = BorderStyle.Thin;
ICellStyle redrow = _workbook.CreateCellStyle();
redrow.FillForegroundColor = HSSFColor.Red.Index;
redrow.FillPattern = FillPattern.SolidForeground;
redrow.Alignment = HorizontalAlignment.Center;
redrow.BorderRight = BorderStyle.Thin;
redrow.BorderBottom = BorderStyle.Thin;
ICellStyle greenrow = _workbook.CreateCellStyle();
greenrow.FillForegroundColor = HSSFColor.LightGreen.Index;
greenrow.FillPattern = FillPattern.SolidForeground;
greenrow.Alignment = HorizontalAlignment.Center;
greenrow.BorderRight = BorderStyle.Thin;
greenrow.BorderBottom = BorderStyle.Thin;
ICellStyle grey40row = _workbook.CreateCellStyle();
grey40row.FillForegroundColor = HSSFColor.Grey80Percent.Index;
grey40row.FillPattern = FillPattern.SolidForeground;
grey40row.Alignment = HorizontalAlignment.Center;
grey40row.BorderRight = BorderStyle.Thin;
grey40row.BorderBottom = BorderStyle.Thin;
ICellStyle grey20row = _workbook.CreateCellStyle();
grey20row.FillForegroundColor = HSSFColor.Grey25Percent.Index;
grey20row.FillPattern = FillPattern.SolidForeground;
grey20row.Alignment = HorizontalAlignment.Center;
grey20row.BorderRight = BorderStyle.Thin;
grey20row.BorderBottom = BorderStyle.Thin;
ICellStyle bluerow = _workbook.CreateCellStyle();
bluerow.FillForegroundColor = HSSFColor.LightBlue.Index;
bluerow.FillPattern = FillPattern.SolidForeground;
bluerow.Alignment = HorizontalAlignment.Center;
bluerow.BorderRight = BorderStyle.Thin;
bluerow.BorderBottom = BorderStyle.Thin;
ICellStyle zerorow = _workbook.CreateCellStyle();
zerorow.Alignment = HorizontalAlignment.Center;
zerorow.BorderRight = BorderStyle.Thin;
zerorow.BorderBottom = BorderStyle.Thin;
#endregion
foreach (var parameter in parameters)
{
if (parameter.Key == "StartDate")
{
_date = Convert.ToDateTime(Convert.ToString(parameter.Value));
}
}
ICell cell;
IRow row;
string head = "Суточная отчетность ГСУ Метели";
//end of styles
var col = 0;
var rown = 0;
var icell = 0;
col = report.Head.Count - 1;
rown = 0;
HSSFSheet sheet = (HSSFSheet)_workbook.CreateSheet(head);
IRow _name = sheet.CreateRow(rown);
_name.HeightInPoints = 30;
cell = _name.CreateCell(0);
//set the title of the sheet
cell.SetCellValue(head);
cell.CellStyle = journal_name_style;
var headerr = new CellRangeAddress(rown, rown, 0, col);
sheet.AddMergedRegion(headerr);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(headerr, NPOI.SS.UserModel.BorderStyle.Thin, 8);
rown++;
IRow jor_name = sheet.CreateRow(rown);
jor_name.HeightInPoints = 30;
cell = jor_name.CreateCell(0);
//set the title of the sheet
cell.SetCellValue(report.Name);
cell.CellStyle = journal_name_style;
var region_name = new CellRangeAddress(rown, rown, 0, col);
sheet.AddMergedRegion(region_name);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region_name, NPOI.SS.UserModel.BorderStyle.Thin, 8);
rown++;
IRow row_period = sheet.CreateRow(rown);
row_period.HeightInPoints = 30;
ICell cell_period = row_period.CreateCell(0);
cell_period.SetCellValue("Отчетные сутки: " + _date.Day + "." + _date.Month + "." + _date.Year + "");
cell_period.CellStyle = journal_period_style;
var region_date = new CellRangeAddress(rown, rown, 0, col);
sheet.AddMergedRegion(region_date);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region_date, NPOI.SS.UserModel.BorderStyle.Thin, 8);
rown++;
IRow date_form = sheet.CreateRow(rown);
ICell date = date_form.CreateCell(0);
var dateTime = DateTime.Now;
date.SetCellValue("Журнал сформирован: " + dateTime + "");
CellRangeAddress region = new CellRangeAddress(rown, rown, 0, col);
sheet.AddMergedRegion(region);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, 8);
rown++;
//Create new Excel Sheet
IRow headerRow = sheet.CreateRow(rown);
rown++;
for (var rc = 0; rc < report.Head.Count; rc++)
{
headerRow.CreateCell(rc).SetCellValue(report.Head[rc]);
}
for (var j = 0; j < report.Head.Count; j++)
{
headerRow.GetCell(j).CellStyle = header;
}
//замопозить область
//sheet.CreateFreezePane(0, rown);
//filters
//sheet.SetAutoFilter(new CellRangeAddress(rown - 1, report.Head.Count + rown, 0, col));
foreach (List <string> t in report.Rows)
{
row = sheet.CreateRow(rown);
for (var j = 0; j < t.Count; j++)
{
row.CreateCell(j).SetCellValue(t[j]);
row.GetCell(j).CellStyle = zerorow;
}
rown++;
}
rown++;
rown++;
row = sheet.CreateRow(rown);
row.CreateCell(0).SetCellValue("Смену сдал:");
row.CreateCell(Convert.ToInt32(col / 2)).SetCellValue("Смену принял:");
rown++;
row = sheet.CreateRow(rown);
row.CreateCell(0).SetCellValue("Ф.И.О:");
row.CreateCell(Convert.ToInt32(col / 2)).SetCellValue("Ф.И.О:");
rown++;
for (int i = 0; i < col + 1; i++)
{
sheet.AutoSizeColumn(i);
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 3 * 512);
}
if (_workbook != null)
{
var output = new MemoryStream();
_workbook.Write(output);
return(output);
}
else
{
return(null);
}
}
private void WriteRow(IRow row, Dictionary<string, object> data, KeyValuePair<int, string>[] titles)
{
foreach (var title in titles)
{
object value;
if (!data.TryGetValue(title.Value, out value)) continue;
var cell = row.GetCell(title.Key);
if (cell == null) cell = row.CreateCell(title.Key);
if (value is string)
{
cell.SetCellValue(value as string);
}
else
{
cell.SetCellValue(Convert.ToDouble(value));
}
}
}
/// <summary>
/// 写入数据到Row
/// </summary>
/// <param name="row"></param>
/// <param name="columns"></param>
public void WriteTitle(IRow row, DataColumnCollection columns)
{
for (int j = 0; j < columns.Count; j++)
{
var cell = row.CreateCell(j);
cell.SetCellValue(columns[j].ColumnName);
cell.CellStyle = titleStyle;
}
}
/// <summary>
/// 由DataGridView导出
/// </summary>
/// <param name="grid"></param>
/// <param name="sheetName"></param>
/// <param name="filePath"></param>
/// <returns></returns>
public static string GridViewToExcel(DataGridView grid, string sheetName = "result", string filePath = null)
{
if (grid.Rows.Count <= 0)
{
return(null);
}
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath))
{
return(null);
}
bool isCompatible = GetIsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
ICellStyle cellStyle = GetCellStyle(workbook);
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < grid.Columns.Count; i++)
{
if (grid.Columns[i].Visible)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(grid.Columns[i].HeaderText);
cell.CellStyle = cellStyle;
}
}
int rowIndex = 1;
foreach (DataGridViewRow row in grid.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int n = 0; n < grid.Columns.Count; n++)
{
if (grid.Columns[n].Visible)
{
dataRow.CreateCell(n).SetCellValue((row.Cells[n].Value ?? "").ToString());
}
}
rowIndex++;
}
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
return(filePath);
}
/// <summary>
/// 写入数据到单元格
/// </summary>
/// <param name="row">要写入数据的行实例</param>
/// <param name="columnNumber">要写入数据的列号</param>
/// <param name="value">要写入的数据</param>
/// <param name="dataType">要写入数据的类型</param>
public void WriteCell(IRow row, int columnNumber, object value, CellDataType dataType)
{
var cell = row.GetCell(columnNumber);
if (cell == null)
{
cell = row.CreateCell(columnNumber);
}
WriteCell(cell, value, dataType);
}
public static HSSFWorkbook ExportToExcel(DataTable dt)
{
HSSFWorkbook workbook = new HSSFWorkbook();
if (dt.Rows.Count > 0)
{
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 = HorizontalAlignment.Center;
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
HeadercellStyle.FillPattern = FillPattern.SolidForeground;
HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn dc in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(dc.ColumnName);
cell.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 = 0;
foreach (DataRow dr in dt.Rows)
{
int iCellIndex = 0;
IRow irow = sheet.CreateRow(iRowIndex + 1);
for (int i = 0; i < dt.Columns.Count; i++)
{
string strsj = string.Empty;
if (dr[i] != null)
{
strsj = dr[i].ToString();
}
ICell cell = irow.CreateCell(iCellIndex);
cell.SetCellValue(strsj);
cell.CellStyle = cellStyle;
iCellIndex++;
}
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//using (MemoryStream ms = new MemoryStream())
//{
// workbook.Write(ms);
// HttpContext curContext = HttpContext.Current;
// // 设置编码和附件格式
// curContext.Response.ContentType = "application/vnd.ms-excel";
// curContext.Response.ContentEncoding = Encoding.UTF8;
// curContext.Response.Charset = "";
// curContext.Response.AppendHeader("Content-Disposition",
// "attachment;filename=" + HttpUtility.UrlEncode(Name + "_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8));
// curContext.Response.BinaryWrite(ms.GetBuffer());
// workbook = null;
// ms.Close();
// ms.Dispose();
// curContext.Response.End();
//}
}
return(workbook);
}
public static ICell CopyCell(IRow row, int sourceIndex, int targetIndex)
{
if (sourceIndex == targetIndex)
throw new ArgumentException("sourceIndex and targetIndex cannot be same");
// Grab a copy of the old/new cell
ICell oldCell = row.GetCell(sourceIndex);
// If the old cell is null jump to next cell
if (oldCell == null)
{
return null;
}
ICell newCell = row.GetCell(targetIndex);
if (newCell == null) //not exist
{
newCell = row.CreateCell(targetIndex);
}
else
{
//TODO:shift cells
}
// Copy style from old cell and apply to new cell
if (oldCell.CellStyle != null)
{
newCell.CellStyle = oldCell.CellStyle;
}
// If there is a cell comment, copy
if (oldCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.BLANK:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.BOOLEAN:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.ERROR:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.FORMULA:
newCell.SetCellFormula(oldCell.CellFormula);
break;
case CellType.NUMERIC:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.STRING:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
}
return newCell;
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker worker = sender as BackgroundWorker;
HSSFWorkbook book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet("季度费率");
sheet.SetColumnWidth(0, 12 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 12 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 16 * 256);
sheet.SetColumnWidth(6, 12 * 256);
int rowcount = 0;
IRow row = sheet.CreateRow(rowcount);
row.CreateCell(0).SetCellValue("营业部");
row.CreateCell(1).SetCellValue("办事处ID");
row.CreateCell(2).SetCellValue("办事处");
row.CreateCell(3).SetCellValue("归属季度ID");
row.CreateCell(4).SetCellValue("归属季度");
row.CreateCell(5).SetCellValue("季度预算费率");
row.CreateCell(6).SetCellValue("季度实际费率");
row.CreateCell(7).SetCellValue("导入标志");
IList <Addr_OrganizeCity> _cityList = Addr_OrganizeCityBLL.GetModelList("Level=4 Order By Level3_SuperID");
foreach (Addr_OrganizeCity city in _cityList)
{
row = sheet.CreateRow(++rowcount);
row.CreateCell(0).SetCellValue(new Addr_OrganizeCityBLL(city.SuperID).Model.Name);
row.CreateCell(1).SetCellValue(city.ID);
row.CreateCell(2).SetCellValue(city.Name);
row.CreateCell(3).SetCellValue(cmb_Quarter.SelectedValue.ToString());
row.CreateCell(4).SetCellValue(cmb_Quarter.SelectedText);
row.CreateCell(5).SetCellValue("");
row.CreateCell(6).SetCellValue("");
row.CreateCell(7).SetCellValue("");
worker.ReportProgress(rowcount * 100 / _cityList.Count, rowcount);
}
//保存
using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
{
book.Write(fs);
}
book = null;
sheet = null;
}
/**
* Get a specific cell from a row. If the cell doesn't exist, then create it.
*
*@param row The row that the cell is part of
*@param columnIndex The column index that the cell is in.
*@return The cell indicated by the column.
*/
public static ICell GetCell(IRow row, int columnIndex)
{
ICell cell = row.GetCell(columnIndex);
if (cell == null)
{
cell = row.CreateCell(columnIndex);
}
return cell;
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(DataTable dtSource, ExcelConfig excelConfig)
{
int colint = 0;
for (int i = 0; i < dtSource.Columns.Count;)
{
DataColumn column = dtSource.Columns[i];
if (excelConfig.ColumnEntity[colint].Column != column.ColumnName)
{
dtSource.Columns.Remove(column.ColumnName);
}
else
{
i++;
if (colint < excelConfig.ColumnEntity.Count - 1)
{
colint++;
}
}
}
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
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[dtSource.Columns.Count];
string[] arrColName = new string[dtSource.Columns.Count]; //列名
ICellStyle[] arryColumStyle = new ICellStyle[dtSource.Columns.Count]; //样式表
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
if (excelConfig.Background != new Color())
{
if (excelConfig.Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
}
}
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig.TitlePoint;
if (excelConfig.ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig.ForeColor);
}
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
#endregion
#region 设置内容单元格样式
foreach (DataColumn item in dtSource.Columns)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
arrColName[item.Ordinal] = item.ColumnName.ToString();
if (excelConfig.ColumnEntity != null)
{
ColumnModel columnentity = excelConfig.ColumnEntity.Find(t => t.Column == item.ColumnName);
if (columnentity != null)
{
arrColName[item.Ordinal] = columnentity.ExcelColumn;
if (columnentity.Width != 0)
{
arrColWidth[item.Ordinal] = columnentity.Width;
}
if (columnentity.Background != new Color())
{
if (columnentity.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
}
}
if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
{
IFont columnFont = workbook.CreateFont();
columnFont.FontHeightInPoints = 10;
if (columnentity.Font != null)
{
columnFont.FontName = columnentity.Font;
}
if (columnentity.Point != 0)
{
columnFont.FontHeightInPoints = columnentity.Point;
}
if (columnentity.ForeColor != new Color())
{
columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
}
columnStyle.SetFont(font);
}
columnStyle.Alignment = getAlignment(columnentity.Alignment);
}
}
arryColumStyle[item.Ordinal] = columnStyle;
}
if (excelConfig.IsAllSizeColumn)
{
#region 根据列中最长列的长度取得列宽
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
if (arrColWidth[j] != 0)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
}
#endregion
}
#endregion
int rowIndex = 0;
#region 表头及样式
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(rowIndex);
rowIndex++;
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(rowIndex);
rowIndex++;
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle;
//设置列宽
//sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
int colWidth = (arrColWidth[column.Ordinal] + 1) * 256;
if (colWidth < 255 * 256)
{
sheet.SetColumnWidth(column.Ordinal, colWidth < 3000 ? 3000 : colWidth);
}
else
{
sheet.SetColumnWidth(column.Ordinal, 6000);
}
}
#endregion
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535)
{
sheet = workbook.CreateSheet();
rowIndex = 0;
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(rowIndex);
rowIndex++;
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(rowIndex);
rowIndex++;
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
#endregion
}
#endregion
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = arryColumStyle[column.Ordinal];
string drValue = row[column].ToString();
SetCell(newCell, dateStyle, column.DataType, drValue);
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(List <T> lists, ExcelConfig excelConfig)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
Type type = typeof(T);
PropertyInfo[] properties = type.GetProperties();
#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
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[properties.Length];
string[] arrColName = new string[properties.Length]; //列名
ICellStyle[] arryColumStyle = new ICellStyle[properties.Length]; //样式表
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
if (excelConfig.Background != new Color())
{
if (excelConfig.Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
}
}
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig.TitlePoint;
if (excelConfig.ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig.ForeColor);
}
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
#endregion
#region 设置内容单元格样式
int i = 0;
foreach (PropertyInfo column in properties)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(column.Name).Length;
arrColName[i] = column.Name;
if (excelConfig.ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == column.Name);
if (columnentity != null)
{
arrColName[i] = columnentity.ExcelColumn;
if (columnentity.Width != 0)
{
arrColWidth[i] = columnentity.Width;
}
if (columnentity.Background != new Color())
{
if (columnentity.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
}
}
if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
{
IFont columnFont = workbook.CreateFont();
columnFont.FontHeightInPoints = 10;
if (columnentity.Font != null)
{
columnFont.FontName = columnentity.Font;
}
if (columnentity.Point != 0)
{
columnFont.FontHeightInPoints = columnentity.Point;
}
if (columnentity.ForeColor != new Color())
{
columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
}
columnStyle.SetFont(font);
}
}
}
arryColumStyle[i] = columnStyle;
i++;
}
#endregion
#region 填充数据
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
foreach (T item in lists)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(0);
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, lists.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
int headIndex = 0;
foreach (PropertyInfo column in properties)
{
headerRow.CreateCell(headIndex).SetCellValue(arrColName[headIndex]);
headerRow.GetCell(headIndex).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256);
headIndex++;
}
#endregion
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int ordinal = 0;
foreach (PropertyInfo column in properties)
{
ICell newCell = dataRow.CreateCell(ordinal);
newCell.CellStyle = arryColumStyle[ordinal];
string drValue = column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString();
SetCell(newCell, dateStyle, column.PropertyType, drValue);
ordinal++;
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
public static ICell GetCell(IRow sheetRow, int colIndex)
{
ICell cell = sheetRow.GetCell(colIndex);
if (cell == null)
cell = sheetRow.CreateCell(colIndex);
return cell;
}
