/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(DataTable[] dtSource, ExcelConfig[] excelConfig)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
for (int dtIndex = 0, length = dtSource.Length; dtIndex < length; dtIndex++)
{
// int colint = 0;
DataTable data = dtSource[dtIndex].Copy();
var tatalCount = dtSource[dtIndex].Columns.Count;
// var index = 0;
/* for (int i = 0; i < dtSource[dtIndex].Columns.Count;)
* {
* index++;
* DataColumn column = dtSource[dtIndex].Columns[i];
* if (excelConfig[dtIndex].ColumnEntity[colint].Column != column.ColumnName)
* {
* dtSource[dtIndex].Columns.Remove(column.ColumnName);
* }
* else
* {
* i++;
* colint++;
* if (colint == excelConfig[dtIndex].ColumnEntity.Count)
* {
* for (var j = index; j < tatalCount; j++)
* {
* DataColumn column1 = data.Columns[j];
* dtSource[dtIndex].Columns.Remove(column1.ColumnName);
* }
* break;
* }
* }
* }*/
ISheet sheet = workbook.CreateSheet((dtIndex + 1).ToString());
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "administrator"; //填加xls文件作者信息
si.ApplicationName = "ZoonTop"; //填加xls文件创建程序信息
si.LastAuthor = "administrator"; //填加xls文件最后保存者信息
si.Comments = "ZoonTop自动生成excel"; //填加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[dtIndex].Columns.Count];
string[] arrColName = new string[dtSource[dtIndex].Columns.Count]; //列名
ColumnEntity[] columnModel = new ColumnEntity[dtSource[dtIndex].Columns.Count]; //航头属性
ICellStyle[] arryColumStyle = new ICellStyle[dtSource[dtIndex].Columns.Count]; //样式表
if (excelConfig[dtIndex].Background != new Color())
{
if (excelConfig[dtIndex].Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig[dtIndex].Background);
}
}
//title文字
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig[dtIndex].TitlePoint;
if (excelConfig[dtIndex].ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig[dtIndex].ForeColor);
}
font.Boldweight = 700;
font.FontHeightInPoints = 20;
headStyle.SetFont(font);
headStyle.ShrinkToFit = true;
//垂直居中,水平居中
headStyle.VerticalAlignment = VerticalAlignment.Center;
headStyle.Alignment = HorizontalAlignment.Center;
//边框样式
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
headStyle.BorderBottom = BorderStyle.Thin;
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = 15; // excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
//边框样式
cHeadStyle.BorderLeft = BorderStyle.Thin;
cHeadStyle.BorderRight = BorderStyle.Thin;
cHeadStyle.BorderTop = BorderStyle.Thin;
cHeadStyle.BorderBottom = BorderStyle.Thin;
//垂直居中
cHeadStyle.VerticalAlignment = VerticalAlignment.Center;
#endregion
#region 设置内容单元格样式
foreach (DataColumn item in dtSource[dtIndex].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[dtIndex].ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig[dtIndex].ColumnEntity.Find(t => t.Column == item.ColumnName);
if (columnentity != null)
{
arrColName[item.Ordinal] = columnentity.ExcelColumn;
columnModel[item.Ordinal] = columnentity;
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);
}
}
//边框样式
columnStyle.BorderLeft = BorderStyle.Thin;
columnStyle.BorderRight = BorderStyle.Thin;
columnStyle.BorderTop = BorderStyle.Thin;
columnStyle.BorderBottom = BorderStyle.Thin;
//单元格文字
IFont columnsFont = workbook.CreateFont();
columnsFont.FontHeightInPoints = 12;
columnStyle.SetFont(columnsFont);
//columnStyle.ShrinkToFit = true;
columnStyle.WrapText = true;//自动换行
//垂直居中
columnStyle.VerticalAlignment = VerticalAlignment.Center;
arryColumStyle[item.Ordinal] = columnStyle;
}
if (excelConfig[dtIndex].IsAllSizeColumn)
{
#region 根据列中最长列的长度取得列宽
for (int i = 0; i < dtSource[dtIndex].Rows.Count; i++)
{
for (int j = 0; j < dtSource[dtIndex].Columns.Count; j++)
{
if (arrColWidth[j] != 0)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource[dtIndex].Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
}
#endregion
}
#endregion
#region 填充数据
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
string xh = dtSource[dtIndex].Columns[0].ToString(); //第一个参数，用来合并单元格
int rowIndex = 0;
int createIndex = 0; //已经创建的行数
int dataIndex = 1;
int[] mergeRows = new int[dtSource[dtIndex].Columns.Count]; //需要合并的行数
IRow[] headerRows;
IRow headerRow;
bool IsCellRangeAddress;
int titleRow = 0;
double[] totalCount = new double[dtSource[dtIndex].Columns.Count];
if (dtSource[dtIndex].Rows.Count == 0)//当列表头大于65536行时会报错，但是....
{
#region 新建表，填充表头，填充列头，样式
#region 表头及样式
if (excelConfig[dtIndex].Title != null)
{
headerRow = sheet.CreateRow(0);
if (excelConfig[dtIndex].TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig[dtIndex].TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.Height = 45 * 20;
headerRow.CreateCell(0).SetCellValue(excelConfig[dtIndex].Title);
headerRow.GetCell(0).CellStyle = headStyle;
//标题的宽高
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[dtIndex].Columns.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
IsCellRangeAddress = false;
rowIndex = 1;
foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
{
if (columnEntity.IsCellRangeAddress)
{
rowIndex = rowIndex > columnEntity.Bottom ? rowIndex : columnEntity.Bottom;
IsCellRangeAddress = true;
}
}
headerRows = new IRow[rowIndex];
for (int hi = 0; hi < rowIndex; hi++)
{
headerRow = sheet.CreateRow(hi + 1);
headerRow.Height = 30 * 20;
headerRows[hi] = headerRow;
}
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
if (!IsCellRangeAddress)//简单表头
{
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
headerRows[0].CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRows[0].GetCell(column.Ordinal).CellStyle = cHeadStyle;
sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
}
}
else//复杂表头
{
int[] indexs = new int[rowIndex];
int max = 0;
foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
{
int headerIndex = columnEntity.Top - 1;
int index = indexs[headerIndex];
for (int i = 0; i < columnEntity.Left - index; i++)//填充合并项的空缺
{
headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue("");
headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
indexs[headerIndex]++;
}
headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue(columnEntity.ExcelColumn);
headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
sheet.SetColumnWidth(indexs[headerIndex], columnEntity.Width * 32);
if (columnEntity.IsCellRangeAddress)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(new CellRangeAddress(
columnEntity.Top,
columnEntity.Bottom,
columnEntity.Left,
columnEntity.Right
));
}
indexs[headerIndex]++;
max = max > indexs[headerIndex] ? max : indexs[headerIndex];
}
//填充表头的空缺
for (int headerDefectCell = 0; headerDefectCell < indexs.Length; headerDefectCell++)
{
for (int headerDefectCellNum = indexs[headerDefectCell]; headerDefectCellNum < max; headerDefectCellNum++)
{
headerRows[headerDefectCell].CreateCell(headerDefectCellNum).SetCellValue("");
headerRows[headerDefectCell].GetCell(headerDefectCellNum).CellStyle = cHeadStyle;
}
}
#endregion
}
rowIndex += 1;
createIndex = rowIndex;
#endregion
#endregion
#region 合计
IRow totalRow = sheet.CreateRow(rowIndex);
totalRow.Height = 30 * 20;
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
if (column.Ordinal == 0)
{
totalRow.CreateCell(column.Ordinal).SetCellValue("合计");
}
else
{
if (column.DataType.FullName.Equals("System.Double"))
{
totalRow.CreateCell(column.Ordinal).SetCellValue(0);
}
else
{
totalRow.CreateCell(column.Ordinal).SetCellValue("");
}
}
totalRow.GetCell(column.Ordinal).CellStyle = arryColumStyle[column.Ordinal];
sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
}
#endregion
}
foreach (DataRow row in dtSource[dtIndex].Rows)
{
#region 新建表，填充表头，填充列头，样式 如果没有数据表头为空
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)//数据过多创建新的sheet页
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
if (excelConfig[dtIndex].Title != null)
{
headerRow = sheet.CreateRow(0);
if (excelConfig[dtIndex].TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig[dtIndex].TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.Height = 45 * 20;
headerRow.CreateCell(0).SetCellValue(excelConfig[dtIndex].Title);
headerRow.GetCell(0).CellStyle = headStyle;
//标题的宽高
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[dtIndex].Columns.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
IsCellRangeAddress = false;
rowIndex = 1;
foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
{
if (columnEntity.IsCellRangeAddress)
{
rowIndex = rowIndex > columnEntity.Bottom ? rowIndex : columnEntity.Bottom;
IsCellRangeAddress = true;
}
}
headerRows = new IRow[rowIndex];
for (int hi = 0; hi < rowIndex; hi++)
{
headerRow = sheet.CreateRow(hi + 1);
headerRow.Height = 30 * 20;
headerRows[hi] = headerRow;
}
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
if (!IsCellRangeAddress)//简单表头
{
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
headerRows[0].CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRows[0].GetCell(column.Ordinal).CellStyle = cHeadStyle;
//设置列宽 // 第二个参数的单位是1/256个字符宽度，但与前端不一致，故改为48
//sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
}
}
else//复杂表头
{
int[] indexs = new int[rowIndex];
int max = 0;
foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
{
int headerIndex = columnEntity.Top - 1;
int index = indexs[headerIndex];
for (int i = 0; i < columnEntity.Left - index; i++)//填充合并项的空缺
{
headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue("");
headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
indexs[headerIndex]++;
}
headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue(columnEntity.ExcelColumn);
headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
sheet.SetColumnWidth(indexs[headerIndex], columnEntity.Width * 32);
if (columnEntity.IsCellRangeAddress)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(new CellRangeAddress(
columnEntity.Top,
columnEntity.Bottom,
columnEntity.Left,
columnEntity.Right
));
}
indexs[headerIndex]++;
max = max > indexs[headerIndex] ? max : indexs[headerIndex];
}
//填充表头的空缺
for (int headerDefectCell = 0; headerDefectCell < indexs.Length; headerDefectCell++)
{
for (int headerDefectCellNum = indexs[headerDefectCell]; headerDefectCellNum < max; headerDefectCellNum++)
{
headerRows[headerDefectCell].CreateCell(headerDefectCellNum).SetCellValue("");
headerRows[headerDefectCell].GetCell(headerDefectCellNum).CellStyle = cHeadStyle;
}
}
#endregion
}
rowIndex += 1;
createIndex = rowIndex;
titleRow = rowIndex;
#endregion
}
#endregion
#region 填充内容
if (createIndex <= rowIndex)
{
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.Height = 30 * 20;
createIndex++;
}
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
if (columnModel[column.Ordinal].Merge == "row")//合并行，行和列不可能同时出现在一个单元格
{
ICell newCell = sheet.GetRow(rowIndex).CreateCell(column.Ordinal);
newCell.CellStyle = arryColumStyle[column.Ordinal];
string drValue = row[column].ToString();
if (mergeRows[column.Ordinal] == 0)//计算要合并几行
{
//不计算父子关系，同级关系，只要和下一个相同，就合并 to edit
//改成只有序号一样的合并 to edit
//增加父级序号也要一致(父级序号是子级的子集：父级：B，子级BB)
for (int subDataIndex = dataIndex, total = dtSource[dtIndex].Rows.Count; subDataIndex < total; subDataIndex++)
{
/*if (drValue.Equals(dtSource[dtIndex].Rows[subDataIndex][column].ToString()))
* {
* mergeRows[column.Ordinal]++;
* }*/
string[] arr = column.ColumnName.ToString().Split('_');
string mark = "";
if (arr.Length > 1)
{
int markCount = 0;
int markLength = arr[0].Length;
bool isAllEqually = true;
foreach (DataColumn parentColumn in dtSource[dtIndex].Columns)
{
if (markCount <= markLength)
{
mark = markCount > 0 ? arr[0].Substring(0, markCount) + "_XH" : "XH";
if (parentColumn.ColumnName.Equals(mark))
{
if (!row[parentColumn.ColumnName].ToString().Equals(dtSource[dtIndex].Rows[subDataIndex][parentColumn.ColumnName].ToString()))
{
isAllEqually = false;
}
markCount++;
}
}
else
{
break;
}
}
if (isAllEqually)
{
mergeRows[column.Ordinal]++;
}
}
else
{
mark = "XH";
//当前序号一致 最外层
if (row[mark].ToString().Equals(dtSource[dtIndex].Rows[subDataIndex][mark].ToString()))
{
mergeRows[column.Ordinal]++;
}
}
}
for (int i = 1; i <= mergeRows[column.Ordinal]; i++)//预创建行
{
if (sheet.GetRow(rowIndex + i) != null)
{
IRow dataRow = sheet.CreateRow(rowIndex + i);
dataRow.Height = 30 * 20;
createIndex++;
}
}
sheet.AddMergedRegion(new CellRangeAddress(
rowIndex,
rowIndex + mergeRows[column.Ordinal],
column.Ordinal,
column.Ordinal
));
}
else
{
drValue = "";
mergeRows[column.Ordinal]--;
}
SetCell(newCell, dateStyle, column.DataType, drValue);
}
else if (columnModel[column.Ordinal].Merge == "col")//合并列
{
}
else//无合并单元格
{
ICell newCell = sheet.GetRow(rowIndex).CreateCell(column.Ordinal);
newCell.CellStyle = arryColumStyle[column.Ordinal];
string drValue = row[column].ToString();
SetCell(newCell, dateStyle, column.DataType, drValue);
}
if (column.DataType.FullName.Equals("System.Double"))
{
double value;
try
{
value = double.Parse(row[column].ToString());
totalCount[column.Ordinal] += value;
}
catch
{
totalCount[column.Ordinal] += 0;
}
}
}
#endregion
#region 合计
if (rowIndex == 65534 || dtSource[dtIndex].Rows.Count + titleRow == rowIndex + 1)//最后一行 或者所有数据填充完
{
IRow totalRow = sheet.CreateRow(rowIndex + 1);
totalRow.Height = 30 * 20;
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
if (column.Ordinal == 0)
{
totalRow.CreateCell(column.Ordinal).SetCellValue("合计");
}
else
{
if (column.DataType.FullName.Equals("System.Double"))
{
if (column.ColumnName.IndexOf("_") > 0)//子表 包含小计
{
totalRow.CreateCell(column.Ordinal).SetCellValue(totalCount[column.Ordinal] / 2);
}
else
{
totalRow.CreateCell(column.Ordinal).SetCellValue(totalCount[column.Ordinal]);
}
}
else
{
totalRow.CreateCell(column.Ordinal).SetCellValue("");
}
}
totalRow.GetCell(column.Ordinal).CellStyle = arryColumStyle[column.Ordinal];
sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
}
rowIndex++;
}
#endregion
rowIndex++;
dataIndex++;
}
#endregion
}
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 = "Yingmei"; //填加xls文件作者信息
si.ApplicationName = "映美Me云打印"; //填加xls文件创建程序信息
si.LastAuthor = "Yingmei"; //填加xls文件最后保存者信息
si.Comments = "Yingmei"; //填加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);
}
}
/// <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++;
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 = "XXX"; //填加xls文件作者信息
si.ApplicationName = "卓软信息"; //填加xls文件创建程序信息
si.LastAuthor = "XXX"; //填加xls文件最后保存者信息
si.Comments = "XXX"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = DateTimeHelper.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)
{
ColumnEntity 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
#region 填充数据
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#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, dtSource.Columns.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
#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
rowIndex = 2;
}
#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(.xls)
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="headRow">列头行数</param>
private static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText = "", int headRow = 1)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "Harry"; //填加xls文件作者信息
si.ApplicationName = ""; //填加xls文件创建程序信息
si.LastAuthor = "Harry"; //填加xls文件最后保存者信息
si.Comments = "Harry"; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int ExcelrowIndex = 0;
int DtRowIndex = 0;//
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (ExcelrowIndex == 65535 || ExcelrowIndex == 0)
{
if (ExcelrowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
#region 无用，准备删除
// HSSFRow headerRow = (HSSFRow)sheet.CreateRow(ExcelrowIndex);
// headerRow.HeightInPoints = 25;
// HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// //headStyle.Alignment = HorizontalAlignment.Center;//水平居中
// //headStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
// HSSFFont font = (HSSFFont)workbook.CreateFont();
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// headStyle.SetFont(font);
// headerRow.GetCell(0).CellStyle = headStyle;
// //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// //headerRow.Dispose();
// ExcelrowIndex++;
#endregion
HSSFCellStyle hssfcellstyle = (HSSFCellStyle)workbook.CreateCellStyle();
if (!string.IsNullOrEmpty(strHeaderText))
{
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
HSSFRow headerRow = NewHSSFRow(ref sheet, workbook, ref hssfcellstyle, ExcelrowIndex, false);
NewFoot(ref hssfcellstyle, workbook, 15);
headerRow.CreateCell(0).SetCellValue(strHeaderText);
headerRow.GetCell(0).CellStyle = hssfcellstyle;
ExcelrowIndex++;
}
}
#endregion
#region 列头及样式
{
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFRow headerRow = NewHSSFRow(ref sheet, workbook, ref headStyle, ExcelrowIndex, true);
NewFoot(ref headStyle, workbook, 10);
#region 无用，准备删除
//HSSFRow headerRow = (HSSFRow)sheet.CreateRow(ExcelrowIndex);
//headerRow.HeightInPoints = 25;
//HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.WrapText = true;//自动换行
////headStyle.Alignment = HorizontalAlignment.Center;//水平居中
//headStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
//HSSFFont font = (HSSFFont)workbook.CreateFont();
//font.FontHeightInPoints = 10;
//font.Boldweight = 700;
//headStyle.SetFont(font);
#endregion
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
//headerRow.Dispose();
ExcelrowIndex++;
}
//添加更多列
{
for (int i = 1; i < headRow; i++)
{
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFRow headerRow = NewHSSFRow(ref sheet, workbook, ref headStyle, ExcelrowIndex, true);
NewFoot(ref headStyle, workbook, 10);
foreach (DataColumn column in dtSource.Columns)
{
string drValue = row[column].ToString();
headerRow.CreateCell(column.Ordinal).SetCellValue(drValue);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, ((arrColWidth[column.Ordinal] + 1) * 256) > 10000 ? 10000 :
((arrColWidth[column.Ordinal] + 1) * 256)); //宽度10000可自定义
}
ExcelrowIndex++;
}
}
#endregion
}
#endregion
//跳过多行列头情况下已添加过的列
DtRowIndex++;
if (DtRowIndex < headRow)
{
continue;
}
#region 填充内容
#region 无用，准备删除
//HSSFRow dataRow = (HSSFRow)sheet.CreateRow(ExcelrowIndex);
//HSSFCellStyle rowStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//rowStyle.WrapText = true;//自动换行
//rowStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
#endregion
HSSFCellStyle rowStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFRow dataRow = NewHSSFRow(ref sheet, workbook, ref rowStyle, ExcelrowIndex, true, 15);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
dataRow.GetCell(column.Ordinal).CellStyle = rowStyle;
}
#endregion
ExcelrowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public Output Execute()
{
Output transform = new Output(new SourceLineNumber(this.TransformFile));
transform.Type = OutputType.Transform;
// get the summary information table
using (SummaryInformation summaryInformation = new SummaryInformation(this.TransformFile))
{
Table table = transform.EnsureTable(this.TableDefinitions["_SummaryInformation"]);
for (int i = 1; 19 >= i; i++)
{
string value = summaryInformation.GetProperty(i);
if (0 < value.Length)
{
Row row = table.CreateRow(transform.SourceLineNumbers);
row[0] = i;
row[1] = value;
}
}
}
// create a schema msi which hopefully matches the table schemas in the transform
Output schemaOutput = new Output(null);
string msiDatabaseFile = Path.Combine(this.IntermediateFolder, "schema.msi");
foreach (TableDefinition tableDefinition in this.TableDefinitions)
{
// skip unreal tables and the Patch table
if (!tableDefinition.Unreal && "Patch" != tableDefinition.Name)
{
schemaOutput.EnsureTable(tableDefinition);
}
}
Hashtable addedRows = new Hashtable();
Table transformViewTable;
// Bind the schema msi.
this.GenerateDatabase(schemaOutput, msiDatabaseFile);
// apply the transform to the database and retrieve the modifications
using (Database msiDatabase = new Database(msiDatabaseFile, OpenDatabase.Transact))
{
// apply the transform with the ViewTransform option to collect all the modifications
msiDatabase.ApplyTransform(this.TransformFile, TransformErrorConditions.All | TransformErrorConditions.ViewTransform);
// unbind the database
var unbindCommand = new UnbindDatabaseCommand(this.Messaging, msiDatabase, msiDatabaseFile, OutputType.Product, this.ExportBasePath, this.IntermediateFolder, false, false, skipSummaryInfo: true);
Output transformViewOutput = unbindCommand.Execute();
// index the added and possibly modified rows (added rows may also appears as modified rows)
transformViewTable = transformViewOutput.Tables["_TransformView"];
Hashtable modifiedRows = new Hashtable();
foreach (Row row in transformViewTable.Rows)
{
string tableName = (string)row[0];
string columnName = (string)row[1];
string primaryKeys = (string)row[2];
if ("INSERT" == columnName)
{
string index = String.Concat(tableName, ':', primaryKeys);
addedRows.Add(index, null);
}
else if ("CREATE" != columnName && "DELETE" != columnName && "DROP" != columnName && null != primaryKeys) // modified row
{
string index = String.Concat(tableName, ':', primaryKeys);
modifiedRows[index] = row;
}
}
// create placeholder rows for modified rows to make the transform insert the updated values when its applied
foreach (Row row in modifiedRows.Values)
{
string tableName = (string)row[0];
string columnName = (string)row[1];
string primaryKeys = (string)row[2];
string index = String.Concat(tableName, ':', primaryKeys);
// ignore information for added rows
if (!addedRows.Contains(index))
{
Table table = schemaOutput.Tables[tableName];
this.CreateRow(table, primaryKeys, true);
}
}
}
// Re-bind the schema output with the placeholder rows.
this.GenerateDatabase(schemaOutput, msiDatabaseFile);
// apply the transform to the database and retrieve the modifications
using (Database msiDatabase = new Database(msiDatabaseFile, OpenDatabase.Transact))
{
try
{
// apply the transform
msiDatabase.ApplyTransform(this.TransformFile, TransformErrorConditions.All);
// commit the database to guard against weird errors with streams
msiDatabase.Commit();
}
catch (Win32Exception ex)
{
if (0x65B == ex.NativeErrorCode)
{
// this commonly happens when the transform was built
// against a database schema different from the internal
// table definitions
throw new WixException(ErrorMessages.TransformSchemaMismatch());
}
}
// unbind the database
var unbindCommand = new UnbindDatabaseCommand(this.Messaging, msiDatabase, msiDatabaseFile, OutputType.Product, this.ExportBasePath, this.IntermediateFolder, false, false, skipSummaryInfo: true);
Output output = unbindCommand.Execute();
// index all the rows to easily find modified rows
Hashtable rows = new Hashtable();
foreach (Table table in output.Tables)
{
foreach (Row row in table.Rows)
{
rows.Add(String.Concat(table.Name, ':', row.GetPrimaryKey('\t', " ")), row);
}
}
// process the _TransformView rows into transform rows
foreach (Row row in transformViewTable.Rows)
{
string tableName = (string)row[0];
string columnName = (string)row[1];
string primaryKeys = (string)row[2];
Table table = transform.EnsureTable(this.TableDefinitions[tableName]);
if ("CREATE" == columnName) // added table
{
table.Operation = TableOperation.Add;
}
else if ("DELETE" == columnName) // deleted row
{
Row deletedRow = this.CreateRow(table, primaryKeys, false);
deletedRow.Operation = RowOperation.Delete;
}
else if ("DROP" == columnName) // dropped table
{
table.Operation = TableOperation.Drop;
}
else if ("INSERT" == columnName) // added row
{
string index = String.Concat(tableName, ':', primaryKeys);
Row addedRow = (Row)rows[index];
addedRow.Operation = RowOperation.Add;
table.Rows.Add(addedRow);
}
else if (null != primaryKeys) // modified row
{
string index = String.Concat(tableName, ':', primaryKeys);
// the _TransformView table includes information for added rows
// that looks like modified rows so it sometimes needs to be ignored
if (!addedRows.Contains(index))
{
Row modifiedRow = (Row)rows[index];
// mark the field as modified
int indexOfModifiedValue = -1;
for (int i = 0; i < modifiedRow.TableDefinition.Columns.Length; ++i)
{
if (columnName.Equals(modifiedRow.TableDefinition.Columns[i].Name, StringComparison.Ordinal))
{
indexOfModifiedValue = i;
break;
}
}
modifiedRow.Fields[indexOfModifiedValue].Modified = true;
// move the modified row into the transform the first time its encountered
if (RowOperation.None == modifiedRow.Operation)
{
modifiedRow.Operation = RowOperation.Modify;
table.Rows.Add(modifiedRow);
}
}
}
else // added column
{
ColumnDefinition column = table.Definition.Columns.Single(c => c.Name.Equals(columnName, StringComparison.Ordinal));
column.Added = true;
}
}
}
return(transform);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="myDgv">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream DataGridViewToExcel(DataGridView myDgv, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[myDgv.Columns.Count];
foreach (DataGridViewColumn item in myDgv.Columns)
{
arrColWidth[item.Index] = Encoding.GetEncoding(936).GetBytes(item.HeaderText.ToString()).Length;
}
for (int i = 0; i < myDgv.Rows.Count; i++)
{
for (int j = 0; j < myDgv.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(myDgv.Rows[i].Cells[j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataGridViewRow row in myDgv.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataGridViewColumn column in myDgv.Columns)
{
headerRow.CreateCell(column.Index).SetCellValue(column.HeaderText);
headerRow.GetCell(column.Index).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Index, (arrColWidth[column.Index] + 1) * 256);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
if (row.Index > 0)
{
foreach (DataGridViewColumn column in myDgv.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Index);
string drValue = myDgv[column.Index, row.Index - 1].Value.ToString();
switch (column.ValueType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
}
else
{
rowIndex--;
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public static void ExportDT(DataTable dtSource, string filename)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://www.huobanplus.com";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "杭州火图科技有限公司"; //填加xls文件作者信息
si.ApplicationName = "火淘助手"; //填加xls文件创建程序信息
si.LastAuthor = "hot"; //填加xls文件最后保存者信息
si.Comments = ""; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
#region 表头及样式
#endregion
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
if (arrColWidth[column.Ordinal] > 255)
{
arrColWidth[column.Ordinal] = 254;
}
else
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
#endregion
//rowIndex = 2;
rowIndex = 1;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (Stream stream = File.OpenWrite(filename))
{
workbook.Write(stream);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream ExportExForWTP(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
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 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
#region 自己添加的
headerRow.CreateCell(0).SetCellValue("序号");
headerRow.GetCell(0).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(0, (arrColWidth[0] + 1) * 256);
#endregion
foreach (DataColumn column in dtSource.Columns)
{
DataTable dt = DAL.Waste.QueryWaste(column.ColumnName, "");
if (dt.Rows.Count > 0)
{
headerRow.CreateCell(column.Ordinal + 1).SetCellValue(dt.Rows[0]["WasteName"].ToString());
}
else
{
headerRow.CreateCell(column.Ordinal + 1).SetCellValue(column.ColumnName);
}
if (column.ColumnName.Contains("Pond"))
{
headerRow.CreateCell(column.Ordinal + 1).SetCellValue("罐池号");
}
headerRow.GetCell(column.Ordinal + 1).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal + 1, (arrColWidth[column.Ordinal] + 2) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
ICellStyle rowStyle = workbook.CreateCellStyle();
rowStyle.Alignment = HorizontalAlignment.CENTER;
ICell newCells = dataRow.CreateCell(0);
newCells.CellStyle = rowStyle;
newCells.SetCellValue(rowIndex - 1);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal + 1);
newCell.CellStyle = rowStyle;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
//MemoryStream ms = new MemoryStream();
//workbook.Write(ms);
//return ms;
sheet.SetColumnHidden(1, true);
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
private static MemoryStream Export2Delivery(DeliveryNote note)
{
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "ChuTian"; //填加xls文件作者信息
si.ApplicationName = "ExcelOrderHelper"; //填加xls文件创建程序信息
si.LastAuthor = "ChuTian"; //填加xls文件最后保存者信息
si.Comments = ""; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
//CellStyle dateStyle = workbook.CreateCellStyle();
//DataFormat format = workbook.CreateDataFormat();
//dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 1;
sheet.SetColumnWidth(0, (int)((9.5 + 0.72) * 256));
sheet.SetColumnWidth(1, (int)((18.5 + 0.72) * 256));
sheet.SetColumnWidth(2, (int)((9.5 + 0.72) * 256));
sheet.SetColumnWidth(3, (int)((12.5 + 0.72) * 256));
sheet.SetColumnWidth(4, (int)((12.5 + 0.72) * 256));
sheet.SetColumnWidth(5, (int)((15.5 + 0.72) * 256));
sheet.SetColumnWidth(6, (int)((20 + 0.72) * 256));
//所有的字体
Font font18 = workbook.CreateFont();
font18.FontName = "华文彩云";
font18.FontHeightInPoints = 18;
font18.IsItalic = true;
Font font11Bold = workbook.CreateFont();
font11Bold.FontHeightInPoints = 11;
font11Bold.Boldweight = 700;
Font font11Normal = workbook.CreateFont();
font11Normal.FontHeightInPoints = 11;
Font font20Bold = workbook.CreateFont();
font20Bold.FontHeightInPoints = 20;
font20Bold.Boldweight = 700;
Font foot10Bold = workbook.CreateFont();
foot10Bold.FontHeightInPoints = 10;
foot10Bold.Boldweight = 700;
CellStyle companyStyle = workbook.CreateCellStyle();
companyStyle.Alignment = HorizontalAlignment.CENTER;
companyStyle.SetFont(font18);
Row companyRow = sheet.CreateRow(rowIndex);
companyRow.HeightInPoints = 30;
companyRow.CreateCell(0).SetCellValue(" 湖北楚天通讯材料有限公司");
companyRow.GetCell(0).CellStyle = companyStyle;
rowIndex++;
//创建表头
CellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.CENTER;
titleStyle.SetFont(font20Bold);
Row titleRow = sheet.CreateRow(rowIndex);
titleRow.HeightInPoints = 29;
titleRow.CreateCell(0).SetCellValue(" 送 货 单");
titleRow.GetCell(0).CellStyle = titleStyle;
//创建Logo
string strLogoPath = AppDomain.CurrentDomain.BaseDirectory + "ctlogo.png";
byte[] bLogo = System.IO.File.ReadAllBytes(strLogoPath);
int pictureIdx = workbook.AddPicture(bLogo, PictureType.PNG);
var patriarch = sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 82, 39, 0, 2, 0, 2);
var pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
//sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
rowIndex++;
rowIndex++;
//创建父表内容
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.LEFT;
headStyle.SetFont(font11Bold);
CellStyle headDescStyle = workbook.CreateCellStyle();
headDescStyle.Alignment = HorizontalAlignment.RIGHT;
headDescStyle.SetFont(font11Normal);
Row parentRow1 = sheet.CreateRow(rowIndex);
parentRow1.CreateCell(0).SetCellValue("客户:");
parentRow1.CreateCell(1).SetCellValue(note.customer);
parentRow1.CreateCell(4).SetCellValue("送货单号：");
parentRow1.CreateCell(5).SetCellValue(note.deliverid.ToString().PadLeft(3, '0'));
parentRow1.CreateCell(6).SetCellValue(note.description);
parentRow1.GetCell(0).CellStyle = headStyle;
parentRow1.GetCell(1).CellStyle = headStyle;
parentRow1.GetCell(4).CellStyle = headStyle;
parentRow1.GetCell(5).CellStyle = headStyle;
parentRow1.GetCell(6).CellStyle = headDescStyle;
rowIndex++;
Row parentRow2 = sheet.CreateRow(rowIndex);
parentRow2.CreateCell(0).SetCellValue("型号:");
parentRow2.CreateCell(1).SetCellValue(note.model);
parentRow2.CreateCell(4).SetCellValue("发货时间：");
parentRow2.CreateCell(5).SetCellValue(note.deliverdate.ToString("yyyy.MM.dd"));
parentRow2.GetCell(0).CellStyle = headStyle;
parentRow2.GetCell(1).CellStyle = headStyle;
parentRow2.GetCell(4).CellStyle = headStyle;
parentRow2.GetCell(5).CellStyle = headStyle;
rowIndex++;
Row parentRow3 = sheet.CreateRow(rowIndex);
parentRow3.CreateCell(0).SetCellValue("货物名称:");
parentRow3.CreateCell(1).SetCellValue(note.goodname);
parentRow3.CreateCell(4).SetCellValue("出厂批号：");
parentRow3.CreateCell(5).SetCellValue(note.batch);
parentRow3.GetCell(0).CellStyle = headStyle;
parentRow3.GetCell(1).CellStyle = headStyle;
parentRow3.GetCell(4).CellStyle = headStyle;
parentRow3.GetCell(5).CellStyle = headStyle;
rowIndex++;
//表格样式
CellStyle tableStyle = workbook.CreateCellStyle();
CellStyle tableLeftStyle = workbook.CreateCellStyle();
tableStyle.Alignment = HorizontalAlignment.CENTER;
tableLeftStyle.Alignment = HorizontalAlignment.LEFT;
tableStyle.SetFont(font11Normal);
tableLeftStyle.SetFont(font11Normal);
tableStyle.BorderTop = CellBorderType.THIN;
tableStyle.BorderBottom = CellBorderType.THIN;
tableStyle.BorderLeft = CellBorderType.THIN;
tableStyle.BorderRight = CellBorderType.THIN;
tableLeftStyle.BorderTop = CellBorderType.THIN;
tableLeftStyle.BorderBottom = CellBorderType.THIN;
tableLeftStyle.BorderLeft = CellBorderType.THIN;
tableLeftStyle.BorderRight = CellBorderType.THIN;
//小计和合计样式
CellStyle tableSumStyle = workbook.CreateCellStyle();
CellStyle tableSumLeftStyle = workbook.CreateCellStyle();
tableSumStyle.Alignment = HorizontalAlignment.CENTER;
tableSumLeftStyle.Alignment = HorizontalAlignment.LEFT;
tableSumStyle.SetFont(font11Bold);
tableSumLeftStyle.SetFont(font11Bold);
tableSumStyle.BorderTop = CellBorderType.THIN;
tableSumStyle.BorderBottom = CellBorderType.THIN;
tableSumStyle.BorderLeft = CellBorderType.THIN;
tableSumStyle.BorderRight = CellBorderType.THIN;
tableSumLeftStyle.BorderTop = CellBorderType.THIN;
tableSumLeftStyle.BorderBottom = CellBorderType.THIN;
tableSumLeftStyle.BorderLeft = CellBorderType.THIN;
tableSumLeftStyle.BorderRight = CellBorderType.THIN;
Row colHeaderRow = sheet.CreateRow(rowIndex);
colHeaderRow.CreateCell(0).SetCellValue("件号");
colHeaderRow.CreateCell(1).SetCellValue("规格");
colHeaderRow.CreateCell(2).SetCellValue("盘数");
colHeaderRow.CreateCell(3).SetCellValue("净重（KG)");
colHeaderRow.CreateCell(4).SetCellValue("单价");
colHeaderRow.CreateCell(5).SetCellValue("金额");
colHeaderRow.CreateCell(6).SetCellValue("合同号");
colHeaderRow.CreateCell(7).SetCellValue("毛重");
colHeaderRow.CreateCell(8).SetCellValue("管芯重量");
colHeaderRow.GetCell(0).CellStyle = tableLeftStyle;
colHeaderRow.GetCell(1).CellStyle = tableStyle;
colHeaderRow.GetCell(2).CellStyle = tableStyle;
colHeaderRow.GetCell(3).CellStyle = tableStyle;
colHeaderRow.GetCell(4).CellStyle = tableStyle;
colHeaderRow.GetCell(5).CellStyle = tableStyle;
colHeaderRow.GetCell(6).CellStyle = tableStyle;
colHeaderRow.GetCell(7).CellStyle = tableStyle;
colHeaderRow.GetCell(8).CellStyle = tableStyle;
rowIndex++;
Dictionary <string, List <DeliveryItem> > dicDelivery = DeliveryItemGroup(note.items);
int iDisoTotal = 0;
double dWeightTotal = 0;
double dPriceTotal = 0;
foreach (string delivertyId in dicDelivery.Keys)
{
int iDisoSum = 0;
double dWeightSum = 0;
double dPriceSum = 0;
List <DeliveryItem> noteItems = dicDelivery[delivertyId];
foreach (DeliveryItem item in noteItems)
{
iDisoSum = iDisoSum + item.discnum;
dWeightSum = dWeightSum + item.weight;
dPriceSum = dPriceSum + item.totalprice;
iDisoTotal = iDisoTotal + item.discnum;
dWeightTotal = dWeightTotal + item.weight;
dPriceTotal = dPriceTotal + item.totalprice;
Row colItemRow = sheet.CreateRow(rowIndex);
colItemRow.CreateCell(0).SetCellValue(item.jiannum);
colItemRow.GetCell(0).CellStyle = tableLeftStyle;
colItemRow.CreateCell(1).SetCellValue(item.specifications + "*" + item.lenght.ToString());
colItemRow.GetCell(1).CellStyle = tableStyle;
colItemRow.CreateCell(2).SetCellValue(item.discnum);
colItemRow.GetCell(2).CellStyle = tableStyle;
colItemRow.CreateCell(3).SetCellValue(item.weight.ToString("f2"));
colItemRow.GetCell(3).CellStyle = tableStyle;
colItemRow.CreateCell(4).SetCellValue(item.price.ToString("f5"));
colItemRow.GetCell(4).CellStyle = tableStyle;
colItemRow.CreateCell(5).SetCellValue(item.totalprice.ToString("f5"));
colItemRow.GetCell(5).CellStyle = tableStyle;
colItemRow.CreateCell(6).SetCellValue(item.contractno);
colItemRow.GetCell(6).CellStyle = tableStyle;
colItemRow.CreateCell(7).SetCellValue(item.netweight);
colItemRow.GetCell(7).CellStyle = tableStyle;
colItemRow.CreateCell(8).SetCellValue(item.coreweight);
colItemRow.GetCell(8).CellStyle = tableStyle;
rowIndex++;
}
if (noteItems.Count > 1 && dicDelivery.Count > 1)
{
Row colItemRow = sheet.CreateRow(rowIndex);
colItemRow.CreateCell(0).SetCellValue("小计");
colItemRow.GetCell(0).CellStyle = tableSumLeftStyle;
colItemRow.CreateCell(1).SetCellValue("");
colItemRow.GetCell(1).CellStyle = tableSumStyle;
colItemRow.CreateCell(2).SetCellValue(iDisoSum.ToString());
colItemRow.GetCell(2).CellStyle = tableSumStyle;
colItemRow.CreateCell(3).SetCellValue(dWeightSum.ToString("f2"));
colItemRow.GetCell(3).CellStyle = tableSumStyle;
colItemRow.CreateCell(4).SetCellValue("");
colItemRow.GetCell(4).CellStyle = tableSumStyle;
colItemRow.CreateCell(5).SetCellValue(dPriceSum.ToString("f2"));
colItemRow.GetCell(5).CellStyle = tableSumStyle;
colItemRow.CreateCell(6).SetCellValue("");
colItemRow.GetCell(6).CellStyle = tableSumStyle;
colItemRow.CreateCell(7).SetCellValue("");
colItemRow.GetCell(7).CellStyle = tableSumStyle;
colItemRow.CreateCell(8).SetCellValue("");
colItemRow.GetCell(8).CellStyle = tableSumStyle;
rowIndex++;
}
else
{
Row colItemRow = sheet.CreateRow(rowIndex);
colItemRow.CreateCell(0).SetCellValue("");
colItemRow.GetCell(0).CellStyle = tableSumLeftStyle;
colItemRow.CreateCell(1).SetCellValue("");
colItemRow.GetCell(1).CellStyle = tableSumStyle;
colItemRow.CreateCell(2).SetCellValue("");
colItemRow.GetCell(2).CellStyle = tableSumStyle;
colItemRow.CreateCell(3).SetCellValue("");
colItemRow.GetCell(3).CellStyle = tableSumStyle;
colItemRow.CreateCell(4).SetCellValue("");
colItemRow.GetCell(4).CellStyle = tableSumStyle;
colItemRow.CreateCell(5).SetCellValue("");
colItemRow.GetCell(5).CellStyle = tableSumStyle;
colItemRow.CreateCell(6).SetCellValue("");
colItemRow.GetCell(6).CellStyle = tableSumStyle;
colItemRow.CreateCell(7).SetCellValue("");
colItemRow.GetCell(7).CellStyle = tableSumStyle;
colItemRow.CreateCell(8).SetCellValue("");
colItemRow.GetCell(8).CellStyle = tableSumStyle;
rowIndex++;
}
}
//空行
Row colBlankRow = sheet.CreateRow(rowIndex);
colBlankRow.CreateCell(0).SetCellValue("");
colBlankRow.GetCell(0).CellStyle = tableLeftStyle;
colBlankRow.CreateCell(1).SetCellValue("");
colBlankRow.GetCell(1).CellStyle = tableStyle;
colBlankRow.CreateCell(2).SetCellValue("");
colBlankRow.GetCell(2).CellStyle = tableStyle;
colBlankRow.CreateCell(3).SetCellValue("");
colBlankRow.GetCell(3).CellStyle = tableStyle;
colBlankRow.CreateCell(4).SetCellValue("");
colBlankRow.GetCell(4).CellStyle = tableStyle;
colBlankRow.CreateCell(5).SetCellValue("");
colBlankRow.GetCell(5).CellStyle = tableStyle;
colBlankRow.CreateCell(6).SetCellValue("");
colBlankRow.GetCell(6).CellStyle = tableStyle;
colBlankRow.CreateCell(7).SetCellValue("");
colBlankRow.GetCell(7).CellStyle = tableStyle;
colBlankRow.CreateCell(8).SetCellValue("");
colBlankRow.GetCell(8).CellStyle = tableStyle;
rowIndex++;
//合计
Row colTotalRow = sheet.CreateRow(rowIndex);
colTotalRow.CreateCell(0).SetCellValue("合计");
colTotalRow.GetCell(0).CellStyle = tableSumLeftStyle;
colTotalRow.CreateCell(1).SetCellValue("");
colTotalRow.GetCell(1).CellStyle = tableSumStyle;
colTotalRow.CreateCell(2).SetCellValue(iDisoTotal.ToString());
colTotalRow.GetCell(2).CellStyle = tableSumStyle;
colTotalRow.CreateCell(3).SetCellValue(dWeightTotal.ToString("f2"));
colTotalRow.GetCell(3).CellStyle = tableSumStyle;
colTotalRow.CreateCell(4).SetCellValue("");
colTotalRow.GetCell(4).CellStyle = tableSumStyle;
colTotalRow.CreateCell(5).SetCellValue(dPriceTotal.ToString("f2"));
colTotalRow.GetCell(5).CellStyle = tableSumStyle;
colTotalRow.CreateCell(6).SetCellValue("");
colTotalRow.GetCell(6).CellStyle = tableSumStyle;
colTotalRow.CreateCell(7).SetCellValue("");
colTotalRow.GetCell(7).CellStyle = tableSumStyle;
colTotalRow.CreateCell(8).SetCellValue("");
colTotalRow.GetCell(8).CellStyle = tableSumStyle;
rowIndex++;
CellStyle footStyle = workbook.CreateCellStyle();
footStyle.Alignment = HorizontalAlignment.LEFT;
footStyle.SetFont(font11Normal);
CellStyle footBoldStyle = workbook.CreateCellStyle();
footBoldStyle.Alignment = HorizontalAlignment.LEFT;
footBoldStyle.SetFont(font11Bold);
CellStyle footRightStyle = workbook.CreateCellStyle();
footRightStyle.Alignment = HorizontalAlignment.RIGHT;
footRightStyle.SetFont(font11Normal);
Row descRow = sheet.CreateRow(rowIndex);
descRow.CreateCell(0).SetCellValue("备注：");
descRow.GetCell(0).CellStyle = footBoldStyle;
descRow.CreateCell(1).SetCellValue(note.description1);
descRow.GetCell(1).CellStyle = footBoldStyle;
rowIndex++;
Row footerRow = sheet.CreateRow(rowIndex);
footerRow.CreateCell(0).SetCellValue("请按上列货验收");
footerRow.GetCell(0).CellStyle = footStyle;
rowIndex++;
Row footerRow1 = sheet.CreateRow(rowIndex);
footerRow1.CreateCell(0).SetCellValue("收货人:");
footerRow1.GetCell(0).CellStyle = footStyle;
footerRow1.CreateCell(1).SetCellValue("");
footerRow1.GetCell(1).CellStyle = footStyle;
footerRow1.CreateCell(2).SetCellValue("送货人:");
footerRow1.GetCell(2).CellStyle = footStyle;
footerRow1.CreateCell(3).SetCellValue("");
footerRow1.GetCell(3).CellStyle = footRightStyle;
footerRow1.CreateCell(4).SetCellValue("制单:");
footerRow1.GetCell(4).CellStyle = footRightStyle;
footerRow1.CreateCell(5).SetCellValue(note.loginid);
footerRow1.GetCell(5).CellStyle = footStyle;
footerRow1.CreateCell(6).SetCellValue("审核：");
footerRow1.GetCell(6).CellStyle = footStyle;
rowIndex++;
CellStyle footStyle1 = workbook.CreateCellStyle();
footStyle1.Alignment = HorizontalAlignment.LEFT;
footStyle1.SetFont(foot10Bold);
Row footerRow2 = sheet.CreateRow(rowIndex);
footerRow2.CreateCell(0).SetCellValue("地址：湖北省汉川市马口工业园区楚天路");
footerRow2.GetCell(0).CellStyle = footStyle1;
footerRow2.CreateCell(1).SetCellValue("");
footerRow2.GetCell(1).CellStyle = footStyle1;
footerRow2.CreateCell(2).SetCellValue("");
footerRow2.GetCell(2).CellStyle = footStyle1;
footerRow2.CreateCell(3).SetCellValue(" 电话：0712-8521088 传真：0712-8512311");
footerRow2.GetCell(3).CellStyle = footStyle1;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 2));
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
workbook.Dispose();
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strSheetName">工作表名称</param>
/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
{
if (oldColumnNames.Length != newColumnNames.Length)
{
return(new MemoryStream());
}
HSSFWorkbook workbook = new HSSFWorkbook();
//HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
ISheet sheet = workbook.CreateSheet(strSheetName);
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://....../";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
if (HttpContext.Current.Session["realname"] != null)
{
si.Author = HttpContext.Current.Session["realname"].ToString();
}
else
{
if (HttpContext.Current.Session["username"] != null)
{
si.Author = HttpContext.Current.Session["username"].ToString();
}
} //填加xls文件作者信息
si.ApplicationName = "NPOI"; //填加xls文件创建程序信息
si.LastAuthor = "OA系统"; //填加xls文件最后保存者信息
si.Comments = "OA系统自动创建文件"; //填加xls文件作者信息
si.Title = strHeaderText; //填加xls文件标题信息
si.Subject = strHeaderText; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 取得列宽
int[] arrColWidth = new int[oldColumnNames.Length];
for (int i = 0; i < oldColumnNames.Length; i++)
{
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
}
/*
* 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 < oldColumnNames.Length; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
/*
* 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;
* }
* }
* */
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1);
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < oldColumnNames.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
/*
* foreach (DataColumn column in dtSource.Columns)
* {
* headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
* headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
*
* //设置列宽
* sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
* }
* */
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
//foreach (DataColumn column in dtSource.Columns)
for (int i = 0; i < oldColumnNames.Length; i++)
{
ICell newCell = dataRow.CreateCell(i);
string drValue = row[oldColumnNames[i]].ToString();
switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
sheet = null;
workbook = null;
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet("Sheet1");
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://bimpan.iok.la:88/";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = ""; //填加xls文件作者信息
si.ApplicationName = "由堃哥编写的Excel导出程序"; //填加xls文件创建程序信息
si.LastAuthor = ""; //填加xls文件最后保存者信息
si.Comments = "说明信息"; //填加xls文件作者信息
si.Title = "任务报表"; //填加xls文件标题信息
si.Subject = "任务报表"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = workbook.CreateCellStyle();
HSSFDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1);
HSSFCellStyle headStyle = workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//将英文列名转为中文
XmlDocument xmlDoc = new XmlDocument();
string xmlPath = string.Format("{0}/{1}", System.Web.HttpContext.Current.Request.PhysicalApplicationPath, "Config/TaskCHSName.xml");
xmlDoc.Load(xmlPath);
Dictionary <string, string> dicEN2CH = Common.GetNodelist(xmlDoc.InnerXml, "Root");
foreach (DataColumn column in dtSource.Columns)
{
string columnNameCH = dicEN2CH.ContainsKey(column.ColumnName.ToUpper()) ? dicEN2CH[column.ColumnName.ToUpper()] : column.ColumnName;
headerRow.CreateCell(column.Ordinal).SetCellValue(columnNameCH);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// 生成导入模版Excel信息
/// </summary>
/// <param name="templateModels">模板定义信息</param>
/// <param name="title">Sheet名称</param>
/// <returns></returns>
public static HSSFWorkbook ExportTemplate(List <ExcelTemplate> templateModels, string title)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
workbook.SetSheetName(0, title);
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = Company;
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = Author;
si.ApplicationName = ApplicationName;
si.Title = title;
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
//取得列宽
int[] arrColWidth = new int[templateModels.Count];
int columnIndex = 0;
foreach (var templateModel in templateModels)
{
arrColWidth[columnIndex] = templateModel.CellLength > 0 ? templateModel.CellLength * 2 : Encoding.UTF8.GetBytes(templateModel.Name.ToString()).Length;
columnIndex++;
}
var headerRow = sheet.CreateRow(0);
columnIndex = 0;
foreach (var templateModel in templateModels)
{
var cell = headerRow.CreateCell(columnIndex);
if (!string.IsNullOrEmpty(templateModel.ExportComments))
{
HSSFPatriarch patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFComment comment = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 16));
comment.String = new HSSFRichTextString(templateModel.ExportComments);
comment.Author = ApplicationName;
cell.CellComment = comment;
}
if (templateModel.DictionaryItems != null && templateModel.DictionaryItems.Count > 0)
{
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(templateModel.DictionaryItems.ToArray());
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
IDataValidation validation = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(validation);
}
cell.SetCellValue(templateModel.Name);
if (templateModel.IsRequred)
{
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.Color = HSSFColor.Red.Index;
font.FontHeightInPoints = 10;
font.IsBold = true;
headStyle.SetFont(font);
cell.CellStyle = headStyle;
}
else
{
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.Color = HSSFColor.Black.Index;
font.FontHeightInPoints = 10;
font.IsBold = true;
headStyle.SetFont(font);
cell.CellStyle = headStyle;
}
//设置列宽
sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256);
columnIndex++;
}
return(workbook);
}
/// <summary>
/// 导出list数据到Excel
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="exportDatas">导出的list数据</param>
/// <param name="templateModels">Excel模版信息</param>
/// <param name="title">Sheet名称</param>
/// <param name="multiHeaderInfos">多表头定义信息</param>
/// <returns></returns>
public static HSSFWorkbook Export <T>(List <T> exportDatas, List <ExcelTemplate> templateModels, string title, List <List <MultiHeaderInfo> > multiHeaderInfos = null)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
workbook.SetSheetName(0, title);
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = Company;
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = Author;
si.ApplicationName = ApplicationName;
si.Title = title;
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
//取得列宽
int[] arrColWidth = new int[templateModels.Count];
int columnIndex = 0;
foreach (var templateModel in templateModels)
{
arrColWidth[columnIndex] = templateModel.CellLength > 0 ? templateModel.CellLength * 2 : Encoding.UTF8.GetBytes(templateModel.Name.ToString()).Length;
columnIndex++;
}
int rowIndex = 0;
foreach (var exportData in exportDatas)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
if (multiHeaderInfos != null && multiHeaderInfos.Count > 0) // 复杂表头合并等
{
List <int>[] usedCellIndexs = new List <int> [multiHeaderInfos.Count];
for (var i = 0; i < multiHeaderInfos.Count; i++)
{
usedCellIndexs[i] = new List <int>();
}
for (var i = 0; i < multiHeaderInfos.Count; i++)
{
var colIndex = 0;
var headerRow = sheet.CreateRow(i);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
headStyle.VerticalAlignment = VerticalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.IsBold = true;
headStyle.SetFont(font);
foreach (var multiHeaderInfo in multiHeaderInfos[i])
{
while (true) // 找未使用的第一个单元格
{
if (!usedCellIndexs[i].Contains(colIndex))
{
break;
}
colIndex++;
}
headerRow.CreateCell(colIndex).SetCellValue(multiHeaderInfo.Name);
var oldColIndex = colIndex;
if (multiHeaderInfo.ColSpan > 1 || multiHeaderInfo.RowSpan > 1)
{
sheet.AddMergedRegion(new CellRangeAddress(i, i + multiHeaderInfo.RowSpan - 1, colIndex, colIndex + multiHeaderInfo.ColSpan - 1));
if (multiHeaderInfo.RowSpan > 1)
{
for (var j = 1; j < multiHeaderInfo.RowSpan; j++)
{
for (var k = colIndex; k < colIndex + multiHeaderInfo.ColSpan; k++)
{
usedCellIndexs[i + j].Add(k);
}
}
}
colIndex = colIndex + multiHeaderInfo.ColSpan;
}
else
{
colIndex++;
}
headerRow.GetCell(oldColIndex).CellStyle = headStyle;
}
}
rowIndex = multiHeaderInfos.Count;
}
else
{
#region 列头及样式
{
var headerRow = sheet.CreateRow(0);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.IsBold = true;
headStyle.SetFont(font);
columnIndex = 0;
foreach (var templateModel in templateModels)
{
headerRow.CreateCell(columnIndex).SetCellValue(templateModel.Name);
headerRow.GetCell(columnIndex).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256);
columnIndex++;
}
}
#endregion
rowIndex = 1;
}
}
#endregion
#region 填充内容
var dataRow = sheet.CreateRow(rowIndex);
columnIndex = 0;
foreach (var templateModel in templateModels)
{
var newCell = dataRow.CreateCell(columnIndex);
var objValue = typeof(T).GetProperty(templateModel.Field).GetValue(exportData)?.ToString();
switch (templateModel.FieldType)
{
case EFieldType.Int:
int intV = 0;
int.TryParse(objValue, out intV);
newCell.SetCellValue(intV);
break;
case EFieldType.Double:
double doubV = 0;
double.TryParse(objValue, out doubV);
newCell.SetCellValue(doubV);
break;
case EFieldType.Guid:
newCell.SetCellValue(objValue);
break;
case EFieldType.Bool:
bool boolV = false;
bool.TryParse(objValue, out boolV);
newCell.SetCellValue(boolV);
break;
case EFieldType.Date:
DateTime.TryParse(objValue, out var dateV);
if (dateV != DateTime.MinValue)
{
newCell.SetCellValue(dateV.ToString("yyyy-MM-dd"));
}
else
{
newCell.SetCellValue("");
}
break;
case EFieldType.DateTime:
DateTime.TryParse(objValue, out var datetimeV);
if (datetimeV != DateTime.MinValue)
{
newCell.SetCellValue(datetimeV.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
newCell.SetCellValue("");
}
//newCell.SetCellType(CellType.String);
//newCell.SetCellValue(datetimeV.ToString("yyyy-MM-dd HH:mm:ss"));
break;
case EFieldType.String:
newCell.SetCellValue(objValue);
break;
default:
newCell.SetCellValue(objValue);
break;
}
columnIndex++;
}
#endregion
rowIndex++;
}
for (int i = 0; i < templateModels.Count; i++)
{
sheet.AutoSizeColumn(i);
}
return(workbook);
}
//public void Create()
//{
// HSSFWorkbook book = new HSSFWorkbook();
// ISheet sheet = book.CreateSheet("Sheet1");
// IRow row = sheet.CreateRow(20);//index代表多少行
// row.HeightInPoints = 35;//行高
// ICell cell = row.CreateCell(0);//创建第一列
// cell.SetCellValue("设置单元格的值");
//}
/// <summary>
/// 导出基本操作示例方法
/// </summary>
public static void ExportExcel()
{
//初始化一个新的HSSFWorkbook实例
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//设置excel必须的文件属性（该属性用来存储 如作者、标题、标记、备注、主题等信息，右键可查看的属性信息）
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.DocumentSummaryInformation = dsi;
hssfworkbook.SummaryInformation = si;
//新建一个Workbook默认都会新建3个Sheet(标准的Excel文件有3个Sheet)。所以必须加入下面的创建Sheet的代码才能保证生成的文件正常
HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet("new sheet");
// hssfworkbook.CreateSheet("Sheet1");
// hssfworkbook.CreateSheet("Sheet2");
// hssfworkbook.CreateSheet("Sheet3");
//建创行
Row row1 = sheet.CreateRow(0);
//建单元格，比如创建A1位置的单元格：
row1.Height = 500;
CellStyle s = hssfworkbook.CreateCellStyle();
s.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
s.FillPattern = FillPatternType.SOLID_FOREGROUND;
//第一列
Cell cell1 = row1.CreateCell(0);
cell1.CellStyle = s;
Font font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 20;
//设置字体加粗样式
font.Boldweight = (short)FontBoldWeight.BOLD;
cell1.CellStyle.SetFont(font);
cell1.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cell1.SetCellValue("《大明宫保护办月报月计划》——( 行政管理部)6月工作计划");
cell1.CellStyle.BorderBottom = CellBorderType.THIN;
cell1.CellStyle.BorderLeft = CellBorderType.THIN;
cell1.CellStyle.BorderRight = CellBorderType.THIN;
cell1.CellStyle.BorderTop = CellBorderType.THIN;
sheet.SetColumnWidth(0, 4 * 256);
//第二列
Cell cell2 = row1.CreateCell(1);
cell2.CellStyle = s;
sheet.SetColumnWidth(1, 12 * 256);
//第三列
Cell cell3 = row1.CreateCell(2);
cell3.CellStyle = s;
sheet.SetColumnWidth(2, 20 * 256);
//第四列
Cell cell4 = row1.CreateCell(3);
cell4.CellStyle = s;
sheet.SetColumnWidth(3, 25 * 256);
//第五列
Cell cell5 = row1.CreateCell(4);
cell5.CellStyle = s;
sheet.SetColumnWidth(4, 35 * 256);
//第六列
Cell cell6 = row1.CreateCell(5);
cell6.CellStyle = s;
sheet.SetColumnWidth(5, 20 * 256);
//第七列
Cell cell7 = row1.CreateCell(6);
cell7.CellStyle = s;
sheet.SetColumnWidth(6, 20 * 256);
//第八列
Cell cell8 = row1.CreateCell(7);
cell8.CellStyle = s;
sheet.SetColumnWidth(7, 20 * 256);
//第9列
Cell cell9 = row1.CreateCell(8);
cell9.CellStyle = s;
sheet.SetColumnWidth(8, 20 * 256);
//第10列
Cell cell10 = row1.CreateCell(9);
cell10.CellStyle = s;
sheet.SetColumnWidth(9, 20 * 256);
//第11列
Cell cell11 = row1.CreateCell(10);
cell11.CellStyle = s;
sheet.SetColumnWidth(10, 20 * 256);
CellRangeAddress r = new CellRangeAddress(0, 0, 0, 10);
sheet.AddMergedRegion(r);
//sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, 10));
CreateRow2(hssfworkbook, sheet);
CreateRow3_4(hssfworkbook, sheet);
_1stModule _1StModule = new _1stModule()
{
_1stModuleName = "管理工作"
};
List <_2ndModule> _2NdModules = new List <_2ndModule>();
_2ndModule _2NdModule = new _2ndModule {
_2ndModuleName = "与战略地图要求相关"
};
DataItem item = new DataItem
{
Work = "保护办十年工作总结",
Result = "30日前完成保护办十年工作总结（总结部分）",
_1stWeek = "根据主要领导意见进行修改",
_2ndWeek = "进行修改",
_3rdWeek = "进行修改",
_4thWeek = "完成总结",
PersonInCharge = "雷博",
Penaty = "50"
};
_2NdModule.datas = new List <DataItem>();
_2NdModule.datas.Add(item);
_2NdModules.Add(_2NdModule);
_2NdModule = new _2ndModule {
_2ndModuleName = "与制度、流程、标准、工具相关"
};
_2NdModule.datas = new List <DataItem>();
_2NdModules.Add(_2NdModule);
_2NdModule = new _2ndModule {
_2ndModuleName = "与企业文化相关"
};
_2NdModule.datas = new List <DataItem>();
_2NdModules.Add(_2NdModule);
_2NdModule = new _2ndModule {
_2ndModuleName = "与团队建设相关"
};
item = new DataItem
{
Work = "组织公文写作培训",
Result = "30日前完成培训",
_1stWeek = "",
_2ndWeek = "与培训老师确定时间和内容",
_3rdWeek = "与培训老师确定时间和内容",
_4thWeek = "完成培训",
PersonInCharge = "王倩",
Penaty = "50"
};
_2NdModule.datas = new List <DataItem>();
_2NdModule.datas.Add(item);
_2NdModules.Add(_2NdModule);
_1StModule._2ndModules = _2NdModules;
CreateRowsByModules(_1StModule, sheet, hssfworkbook);
//把这个HSSFWorkbook实例写入文件
FileStream file = new FileStream("Example1.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
public void TestWriteWellKnown1()
{
POIDataSamples _samples = POIDataSamples.GetHPSFInstance();
using (FileStream doc1 = _samples.GetFile(POI_FS))
{
/* Read a Test document <em>doc1</em> into a POI filesystem. */
POIFSFileSystem poifs = new POIFSFileSystem(doc1);
DirectoryEntry dir = poifs.Root;
DocumentEntry siEntry = (DocumentEntry)dir.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME);
DocumentEntry dsiEntry = (DocumentEntry)dir.GetEntry(DocumentSummaryInformation.DEFAULT_STREAM_NAME);
/*
* Read the summary information stream and the document summary
* information stream from the POI filesystem.
*
* Please note that the result consists of SummaryInformation and
* DocumentSummaryInformation instances which are in memory only. To
* make them permanent they have to be written to a POI filesystem
* explicitly (overwriting the former contents). Then the POI filesystem
* should be saved to a file.
*/
DocumentInputStream dis = new DocumentInputStream(siEntry);
PropertySet ps = new PropertySet(dis);
SummaryInformation si = new SummaryInformation(ps);
dis = new DocumentInputStream(dsiEntry);
ps = new PropertySet(dis);
DocumentSummaryInformation dsi = new DocumentSummaryInformation(ps);
/*
* Write all properties supported by HPSF to the summary information
* (e.g. author, edit date, application name) and to the document
* summary information (e.g. company, manager).
*/
Calendar cal = new GregorianCalendar();
//long time1 = (long)cal.GetMilliseconds(new DateTime(2000, 6, 6, 6, 6, 6));
//long time2 = (long)cal.GetMilliseconds(new DateTime(2001, 7, 7, 7, 7, 7));
//long time3 = (long)cal.GetMilliseconds(new DateTime(2002, 8, 8, 8, 8, 8));
int nr = 4711;
String P_APPLICATION_NAME = "Microsoft Office Word";
String P_AUTHOR = "Rainer Klute";
int P_CHAR_COUNT = 125;
String P_COMMENTS = ""; //"Comments";
DateTime P_CREATE_DATE_TIME = new DateTime(2006, 2, 1, 7, 36, 0);
long P_EDIT_TIME = ++nr * 1000 * 10;
String P_KEYWORDS = "Test HPSF SummaryInformation DocumentSummaryInformation Writing";
String P_LAST_AUTHOR = "LastAuthor";
DateTime?P_LAST_PRINTED = new DateTime(2001, 7, 7, 7, 7, 7);
DateTime P_LAST_SAVE_DATE_TIME = new DateTime(2008, 9, 30, 9, 54, 0);
int P_PAGE_COUNT = 1;
String P_REV_NUMBER = "RevNumber";
int P_SECURITY = 1;
String P_SUBJECT = "Subject";
String P_TEMPLATE = "Normal.dotm";
// FIXME (byte array properties not yet implemented): byte[] P_THUMBNAIL = new byte[123];
String P_TITLE = "This document is used for testing POI HPSF¡¯s writing capabilities for the summary information stream and the document summary information stream";
int P_WORD_COUNT = 21;
int P_BYTE_COUNT = ++nr;
String P_CATEGORY = "Category";
String P_COMPANY = "Rainer Klute IT-Consulting GmbH";
// FIXME (byte array properties not yet implemented): byte[] P_DOCPARTS = new byte[123];
// FIXME (byte array properties not yet implemented): byte[] P_HEADING_PAIR = new byte[123];
int P_HIDDEN_COUNT = ++nr;
int P_LINE_COUNT = ++nr;
bool P_LINKS_DIRTY = true;
String P_MANAGER = "Manager";
int P_MM_CLIP_COUNT = ++nr;
int P_NOTE_COUNT = ++nr;
int P_PAR_COUNT = ++nr;
String P_PRESENTATION_FORMAT = "PresentationFormat";
bool P_SCALE = false;
int P_SLIDE_COUNT = ++nr;
DateTime now = DateTime.Now;
int POSITIVE_INTEGER = 2222;
long POSITIVE_LONG = 3333;
Double POSITIVE_DOUBLE = 4444;
int NEGATIVE_INTEGER = 2222;
long NEGATIVE_LONG = 3333;
Double NEGATIVE_DOUBLE = 4444;
int MAX_INTEGER = int.MaxValue;
int MIN_INTEGER = int.MinValue;
long MAX_LONG = long.MaxValue;
long MIN_LONG = long.MinValue;
Double MAX_DOUBLE = Double.MaxValue;
Double MIN_DOUBLE = Double.MinValue;
si.ApplicationName = P_APPLICATION_NAME;
si.Author = P_AUTHOR;
si.CharCount = P_CHAR_COUNT;
si.Comments = P_COMMENTS;
si.CreateDateTime = P_CREATE_DATE_TIME;
si.EditTime = P_EDIT_TIME;
si.Keywords = P_KEYWORDS;
si.LastAuthor = P_LAST_AUTHOR;
si.LastPrinted = P_LAST_PRINTED;
si.LastSaveDateTime = P_LAST_SAVE_DATE_TIME;
si.PageCount = P_PAGE_COUNT;
si.RevNumber = P_REV_NUMBER;
si.Security = P_SECURITY;
si.Subject = P_SUBJECT;
si.Template = P_TEMPLATE;
// FIXME (byte array properties not yet implemented): si.Thumbnail=P_THUMBNAIL;
si.Title = P_TITLE;
si.WordCount = P_WORD_COUNT;
dsi.ByteCount = P_BYTE_COUNT;
dsi.Category = P_CATEGORY;
dsi.Company = P_COMPANY;
// FIXME (byte array properties not yet implemented): dsi.Docparts=P_DOCPARTS;
// FIXME (byte array properties not yet implemented): dsi.HeadingPair=P_HEADING_PAIR;
dsi.HiddenCount = P_HIDDEN_COUNT;
dsi.LineCount = P_LINE_COUNT;
dsi.LinksDirty = P_LINKS_DIRTY;
dsi.Manager = P_MANAGER;
dsi.MMClipCount = P_MM_CLIP_COUNT;
dsi.NoteCount = P_NOTE_COUNT;
dsi.ParCount = P_PAR_COUNT;
dsi.PresentationFormat = P_PRESENTATION_FORMAT;
dsi.Scale = P_SCALE;
dsi.SlideCount = P_SLIDE_COUNT;
CustomProperties customProperties = dsi.CustomProperties;
if (customProperties == null)
{
customProperties = new CustomProperties();
}
customProperties.Put("Schlüssel 1", "Wert 1");
customProperties.Put("Schlüssel 2", "Wert 2");
customProperties.Put("Schlüssel 3", "Wert 3");
customProperties.Put("Schlüssel 4", "Wert 4");
customProperties.Put("positive_int", POSITIVE_INTEGER);
customProperties.Put("positive_long", POSITIVE_LONG);
customProperties.Put("positive_Double", POSITIVE_DOUBLE);
customProperties.Put("negative_int", NEGATIVE_INTEGER);
customProperties.Put("negative_long", NEGATIVE_LONG);
customProperties.Put("negative_Double", NEGATIVE_DOUBLE);
customProperties.Put("Boolean", true);
customProperties.Put("Date", now);
customProperties.Put("max_int", MAX_INTEGER);
customProperties.Put("min_int", MIN_INTEGER);
customProperties.Put("max_long", MAX_LONG);
customProperties.Put("min_long", MIN_LONG);
customProperties.Put("max_Double", MAX_DOUBLE);
customProperties.Put("min_Double", MIN_DOUBLE);
dsi.CustomProperties = customProperties;
/* Write the summary information stream and the document summary
* information stream to the POI filesystem. */
si.Write(dir, siEntry.Name);
dsi.Write(dir, dsiEntry.Name);
/* Write the POI filesystem to a (temporary) file <em>doc2</em>
* and Close the latter. */
using (FileStream doc2 = File.Create(@".\POI_HPSF_Test2.tmp"))
{
poifs.WriteFileSystem(doc2);
//doc2.Flush();
/*
* Open <em>doc2</em> for Reading and check summary information and
* document summary information. All properties written before must be
* found in the property streams of <em>doc2</em> and have the correct
* values.
*/
doc2.Flush();
doc2.Position = 0;
POIFSFileSystem poifs2 = new POIFSFileSystem(doc2);
dir = poifs2.Root;
siEntry = (DocumentEntry)dir.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME);
dsiEntry = (DocumentEntry)dir.GetEntry(DocumentSummaryInformation.DEFAULT_STREAM_NAME);
dis = new DocumentInputStream(siEntry);
ps = new PropertySet(dis);
si = new SummaryInformation(ps);
dis = new DocumentInputStream(dsiEntry);
ps = new PropertySet(dis);
dsi = new DocumentSummaryInformation(ps);
Assert.AreEqual(P_APPLICATION_NAME, si.ApplicationName);
Assert.AreEqual(P_AUTHOR, si.Author);
Assert.AreEqual(P_CHAR_COUNT, si.CharCount);
Assert.AreEqual(P_COMMENTS, si.Comments);
Assert.AreEqual(P_CREATE_DATE_TIME, si.CreateDateTime);
Assert.AreEqual(P_EDIT_TIME, si.EditTime);
Assert.AreEqual(P_KEYWORDS, si.Keywords);
Assert.AreEqual(P_LAST_AUTHOR, si.LastAuthor);
Assert.AreEqual(P_LAST_PRINTED, si.LastPrinted);
Assert.AreEqual(P_LAST_SAVE_DATE_TIME, si.LastSaveDateTime);
Assert.AreEqual(P_PAGE_COUNT, si.PageCount);
Assert.AreEqual(P_REV_NUMBER, si.RevNumber);
Assert.AreEqual(P_SECURITY, si.Security);
Assert.AreEqual(P_SUBJECT, si.Subject);
Assert.AreEqual(P_TEMPLATE, si.Template);
// FIXME (byte array properties not yet implemented): Assert.AreEqual(P_THUMBNAIL, si.Thumbnail);
Assert.AreEqual(P_TITLE, si.Title);
Assert.AreEqual(P_WORD_COUNT, si.WordCount);
Assert.AreEqual(P_BYTE_COUNT, dsi.ByteCount);
Assert.AreEqual(P_CATEGORY, dsi.Category);
Assert.AreEqual(P_COMPANY, dsi.Company);
// FIXME (byte array properties not yet implemented): Assert.AreEqual(P_, dsi.Docparts);
// FIXME (byte array properties not yet implemented): Assert.AreEqual(P_, dsi.HeadingPair);
Assert.AreEqual(P_HIDDEN_COUNT, dsi.HiddenCount);
Assert.AreEqual(P_LINE_COUNT, dsi.LineCount);
Assert.AreEqual(P_LINKS_DIRTY, dsi.LinksDirty);
Assert.AreEqual(P_MANAGER, dsi.Manager);
Assert.AreEqual(P_MM_CLIP_COUNT, dsi.MMClipCount);
Assert.AreEqual(P_NOTE_COUNT, dsi.NoteCount);
Assert.AreEqual(P_PAR_COUNT, dsi.ParCount);
Assert.AreEqual(P_PRESENTATION_FORMAT, dsi.PresentationFormat);
Assert.AreEqual(P_SCALE, dsi.Scale);
Assert.AreEqual(P_SLIDE_COUNT, dsi.SlideCount);
CustomProperties cps = dsi.CustomProperties;
//Assert.AreEqual(customProperties, cps);
Assert.IsNull(cps["No value available"]);
Assert.AreEqual("Wert 1", cps["Schlüssel 1"]);
Assert.AreEqual("Wert 2", cps["Schlüssel 2"]);
Assert.AreEqual("Wert 3", cps["Schlüssel 3"]);
Assert.AreEqual("Wert 4", cps["Schlüssel 4"]);
Assert.AreEqual(POSITIVE_INTEGER, cps["positive_int"]);
Assert.AreEqual(POSITIVE_LONG, cps["positive_long"]);
Assert.AreEqual(POSITIVE_DOUBLE, cps["positive_Double"]);
Assert.AreEqual(NEGATIVE_INTEGER, cps["negative_int"]);
Assert.AreEqual(NEGATIVE_LONG, cps["negative_long"]);
Assert.AreEqual(NEGATIVE_DOUBLE, cps["negative_Double"]);
Assert.AreEqual(true, cps["Boolean"]);
Assert.AreEqual(now, cps["Date"]);
Assert.AreEqual(MAX_INTEGER, cps["max_int"]);
Assert.AreEqual(MIN_INTEGER, cps["min_int"]);
Assert.AreEqual(MAX_LONG, cps["max_long"]);
Assert.AreEqual(MIN_LONG, cps["min_long"]);
Assert.AreEqual(MAX_DOUBLE, cps["max_Double"]);
Assert.AreEqual(MIN_DOUBLE, cps["min_Double"]);
/* Remove all properties supported by HPSF from the summary
* information (e.g. author, edit date, application name) and from the
* document summary information (e.g. company, manager). */
si.RemoveApplicationName();
si.RemoveAuthor();
si.RemoveCharCount();
si.RemoveComments();
si.RemoveCreateDateTime();
si.RemoveEditTime();
si.RemoveKeywords();
si.RemoveLastAuthor();
si.RemoveLastPrinted();
si.RemoveLastSaveDateTime();
si.RemovePageCount();
si.RemoveRevNumber();
si.RemoveSecurity();
si.RemoveSubject();
si.RemoveTemplate();
si.RemoveThumbnail();
si.RemoveTitle();
si.RemoveWordCount();
dsi.RemoveByteCount();
dsi.RemoveCategory();
dsi.RemoveCompany();
dsi.RemoveCustomProperties();
dsi.RemoveDocparts();
dsi.RemoveHeadingPair();
dsi.RemoveHiddenCount();
dsi.RemoveLineCount();
dsi.RemoveLinksDirty();
dsi.RemoveManager();
dsi.RemoveMMClipCount();
dsi.RemoveNoteCount();
dsi.RemoveParCount();
dsi.RemovePresentationFormat();
dsi.RemoveScale();
dsi.RemoveSlideCount();
/*
* <li>Write the summary information stream and the document summary
* information stream to the POI filesystem. */
si.Write(dir, siEntry.Name);
dsi.Write(dir, dsiEntry.Name);
/*
* <li>Write the POI filesystem to a (temporary) file <em>doc3</em>
* and Close the latter. */
using (FileStream doc3 = File.Create(@".\POI_HPSF_Test3.tmp"))
{
poifs2.WriteFileSystem(doc3);
doc3.Position = 0;
/*
* Open <em>doc3</em> for Reading and check summary information
* and document summary information. All properties Removed before must not
* be found in the property streams of <em>doc3</em>.
*/
POIFSFileSystem poifs3 = new POIFSFileSystem(doc3);
dir = poifs3.Root;
siEntry = (DocumentEntry)dir.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME);
dsiEntry = (DocumentEntry)dir.GetEntry(DocumentSummaryInformation.DEFAULT_STREAM_NAME);
dis = new DocumentInputStream(siEntry);
ps = new PropertySet(dis);
si = new SummaryInformation(ps);
dis = new DocumentInputStream(dsiEntry);
ps = new PropertySet(dis);
dsi = new DocumentSummaryInformation(ps);
Assert.AreEqual(null, si.ApplicationName);
Assert.AreEqual(null, si.Author);
Assert.AreEqual(0, si.CharCount);
Assert.IsTrue(si.WasNull);
Assert.AreEqual(null, si.Comments);
Assert.AreEqual(null, si.CreateDateTime);
Assert.AreEqual(0, si.EditTime);
Assert.IsTrue(si.WasNull);
Assert.AreEqual(null, si.Keywords);
Assert.AreEqual(null, si.LastAuthor);
Assert.AreEqual(null, si.LastPrinted);
Assert.AreEqual(null, si.LastSaveDateTime);
Assert.AreEqual(0, si.PageCount);
Assert.IsTrue(si.WasNull);
Assert.AreEqual(null, si.RevNumber);
Assert.AreEqual(0, si.Security);
Assert.IsTrue(si.WasNull);
Assert.AreEqual(null, si.Subject);
Assert.AreEqual(null, si.Template);
Assert.AreEqual(null, si.Thumbnail);
Assert.AreEqual(null, si.Title);
Assert.AreEqual(0, si.WordCount);
Assert.IsTrue(si.WasNull);
Assert.AreEqual(0, dsi.ByteCount);
Assert.IsTrue(dsi.WasNull);
Assert.AreEqual(null, dsi.Category);
Assert.AreEqual(null, dsi.CustomProperties);
// FIXME (byte array properties not yet implemented): Assert.AreEqual(null, dsi.Docparts);
// FIXME (byte array properties not yet implemented): Assert.AreEqual(null, dsi.HeadingPair);
Assert.AreEqual(0, dsi.HiddenCount);
Assert.IsTrue(dsi.WasNull);
Assert.AreEqual(0, dsi.LineCount);
Assert.IsTrue(dsi.WasNull);
Assert.AreEqual(false, dsi.LinksDirty);
Assert.IsTrue(dsi.WasNull);
Assert.AreEqual(null, dsi.Manager);
Assert.AreEqual(0, dsi.MMClipCount);
Assert.IsTrue(dsi.WasNull);
Assert.AreEqual(0, dsi.NoteCount);
Assert.IsTrue(dsi.WasNull);
Assert.AreEqual(0, dsi.ParCount);
Assert.IsTrue(dsi.WasNull);
Assert.AreEqual(null, dsi.PresentationFormat);
Assert.AreEqual(false, dsi.Scale);
Assert.IsTrue(dsi.WasNull);
Assert.AreEqual(0, dsi.SlideCount);
Assert.IsTrue(dsi.WasNull);
}
}
}
if (File.Exists(@".\POI_HPSF_Test3.tmp"))
{
File.Delete(@".\POI_HPSF_Test3.tmp");
}
if (File.Exists(@".\POI_HPSF_Test2.tmp"))
{
File.Delete(@".\POI_HPSF_Test2.tmp");
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="strHeaderText">Excel表头文本（例如：车辆列表）</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "lixun"; //填加xls文件作者信息
si.ApplicationName = "ExcelOrderHelper"; //填加xls文件创建程序信息
si.LastAuthor = "lixun"; //填加xls文件最后保存者信息
si.Comments = ""; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
CellStyle dateStyle = workbook.CreateCellStyle();
DataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
if (strHeaderText.Length > 0)
{
Row headerRow = sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER; // ------------------
Font font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
rowIndex++;
}
}
#endregion
#region 列头及样式
{
Row headerRow = sheet.CreateRow(rowIndex);
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER; // ------------------
headStyle.BorderTop = CellBorderType.THIN;
headStyle.BorderBottom = CellBorderType.THIN;
headStyle.BorderLeft = CellBorderType.THIN;
headStyle.BorderRight = CellBorderType.THIN;
Font font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
string sName = column.ColumnName;
if (sName.Contains("剥离"))
{
sName = "剥离";
}
else if (sName.Contains("样品1"))
{
sName = "热合样品1";
}
else if (sName.Contains("样品2"))
{
sName = "热合样品2";
}
else if (sName.Contains("泡水"))
{
sName = "泡水";
}
headerRow.CreateCell(column.Ordinal).SetCellValue(sName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 350);
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 6, 7));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 8, 10));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 11, 13));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 18, 19));
rowIndex++;
}
#endregion
}
#endregion
#region 填充内容
Row dataRow = sheet.CreateRow(rowIndex);
CellStyle mainStyle = workbook.CreateCellStyle();
mainStyle.BorderTop = CellBorderType.THIN;
mainStyle.BorderBottom = CellBorderType.THIN;
mainStyle.BorderLeft = CellBorderType.THIN;
mainStyle.BorderRight = CellBorderType.THIN;
foreach (DataColumn column in dtSource.Columns)
{
Cell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = mainStyle;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream DataTableToExcel1(DataTable dtSource, string strHeaderText)
{
Helper helper = new Helper("Data Source=172.128.2.1/veims;User ID=zte;Password=zsfyqch;");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
string dtname = row[1].ToString();
#region
string sql = string.Format(@"select cc.COMMENTS, cC.COLUMN_NAME,
CASE WHEN c.data_type='VARCHAR2' THEN '字符串型C'
WHEN c.data_type='NUMBER' THEN '数值型N'
WHEN c.data_type='CHAR' THEN '字符串型C'
WHEN c.data_type='DATE' THEN '日期时间型T'
WHEN c.data_type='FLOAT' THEN '数值型N'
WHEN c.data_type='CLOB' THEN '长文本text'
WHEN c.data_type='NCHAR' THEN '字符串型C'
WHEN c.data_type='RAW' THEN '字符串型C'
WHEN c.data_type='NVARCHAR2' THEN '字符串型C' END data_type
,
c.data_length
,'无条件共享' A1,
'用户数据校核、业务协同及大数据应用' A2,'共享平台方式'A3,'数据库'A4,'否'A5
from user_tab_columns c
LEFT JOIN
user_col_comments cc
ON cc.COLUMN_NAME=c.COLUMN_NAME AND CC.Table_Name='{0}'
where
c.Table_Name='{0}' order by c.COLUMN_NAME asc", dtname.ToUpper());
#endregion
DataTable dd = helper.ExecuteDataSet(sql).Tables[0];
int kk = 0;//列控制 0到16列、27、28列 全部合并单元格
foreach (DataColumn column in dtSource.Columns)
{
//第一列合并第三行到第五行 0 1 2 3 4
//sheet.AddMergedRegion(new CellRangeAddress(2, 4, 0, 0));
if (dd.Rows.Count > 0 && kk < 17)
{
// 0到16列全部合并单元格
// 第KK列合并第2行到第dd.Rows.Count行
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + dd.Rows.Count, kk, kk));
}
if (kk > 26 && kk < 29)
{
//0到16列全部合并单元格
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + dd.Rows.Count, kk, kk));
}
HSSFCell newCell;
string drValue;
//if ((kk > 16 && kk < 27) || (kk > 26 && kk < 29))
//{
// int f = 0;
// foreach (DataRow row1 in dd.Rows)
// {
// rowIndex++;
// foreach (DataColumn column1 in dd.Columns)
// {
// newCell = (HSSFCell)dataRow.CreateCell(kk+column1.Ordinal);
// drValue = row1[column1].ToString();
// switch (column1.DataType.ToString())
// {
// case "System.String"://字符串类型
// newCell.SetCellValue(drValue);
// break;
// case "System.DateTime"://日期类型
// System.DateTime dateV;
// System.DateTime.TryParse(drValue, out dateV);
// newCell.SetCellValue(dateV);
// newCell.CellStyle = dateStyle;//格式化显示
// break;
// case "System.Boolean"://布尔型
// bool boolV = false;
// bool.TryParse(drValue, out boolV);
// newCell.SetCellValue(boolV);
// break;
// case "System.Int16"://整型
// case "System.Int32":
// case "System.Int64":
// case "System.Byte":
// int intV = 0;
// int.TryParse(drValue, out intV);
// newCell.SetCellValue(intV);
// break;
// case "System.Decimal"://浮点型
// case "System.Double":
// double doubV = 0;
// double.TryParse(drValue, out doubV);
// newCell.SetCellValue(doubV);
// break;
// case "System.DBNull"://空值处理
// newCell.SetCellValue("");
// break;
// default:
// newCell.SetCellValue("");
// break;
// }
// }
// f++;
// }
//}
//else
{
newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
kk++;
}
#endregion
rowIndex += dd.Rows.Count;
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
private bool CreateFacadesForMergeModuleFiles(WixMergeSymbol wixMergeRow, List <FileFacade> mergeModulesFileFacades, Dictionary <string, FileFacade> indexedFileFacades)
{
var containsFiles = false;
try
{
// read the module's File table to get its FileMediaInformation entries and gather any other information needed from the module.
using (var db = new Database(wixMergeRow.SourceFile, OpenDatabase.ReadOnly))
{
if (db.TableExists("File") && db.TableExists("Component"))
{
var uniqueModuleFileIdentifiers = new Dictionary <string, FileFacade>(StringComparer.OrdinalIgnoreCase);
using (var view = db.OpenExecuteView("SELECT `File`, `Directory_` FROM `File`, `Component` WHERE `Component_`=`Component`"))
{
// add each file row from the merge module into the file row collection (check for errors along the way)
foreach (var record in view.Records)
{
// NOTE: this is very tricky - the merge module file rows are not added to the
// file table because they should not be created via idt import. Instead, these
// rows are created by merging in the actual modules.
var fileSymbol = new FileSymbol(wixMergeRow.SourceLineNumbers, new Identifier(AccessModifier.Private, record[1]));
fileSymbol.Attributes = wixMergeRow.FileAttributes;
fileSymbol.DirectoryRef = record[2];
fileSymbol.DiskId = wixMergeRow.DiskId;
fileSymbol.Source = new IntermediateFieldPathValue {
Path = Path.Combine(this.IntermediateFolder, wixMergeRow.Id.Id, record[1])
};
var mergeModuleFileFacade = new FileFacade(true, fileSymbol);
// If case-sensitive collision with another merge module or a user-authored file identifier.
if (indexedFileFacades.TryGetValue(mergeModuleFileFacade.Id, out var collidingFacade))
{
this.Messaging.Write(ErrorMessages.DuplicateModuleFileIdentifier(wixMergeRow.SourceLineNumbers, wixMergeRow.Id.Id, collidingFacade.Id));
}
else if (uniqueModuleFileIdentifiers.TryGetValue(mergeModuleFileFacade.Id, out collidingFacade)) // case-insensitive collision with another file identifier in the same merge module
{
this.Messaging.Write(ErrorMessages.DuplicateModuleCaseInsensitiveFileIdentifier(wixMergeRow.SourceLineNumbers, wixMergeRow.Id.Id, mergeModuleFileFacade.Id, collidingFacade.Id));
}
else // no collision
{
mergeModulesFileFacades.Add(mergeModuleFileFacade);
// Keep updating the indexes as new rows are added.
indexedFileFacades.Add(mergeModuleFileFacade.Id, mergeModuleFileFacade);
uniqueModuleFileIdentifiers.Add(mergeModuleFileFacade.Id, mergeModuleFileFacade);
}
containsFiles = true;
}
}
}
// Get the summary information to detect the Schema
using (var summaryInformation = new SummaryInformation(db))
{
var moduleInstallerVersionString = summaryInformation.GetProperty(14);
try
{
var moduleInstallerVersion = Convert.ToInt32(moduleInstallerVersionString, CultureInfo.InvariantCulture);
if (moduleInstallerVersion > this.OutputInstallerVersion)
{
this.Messaging.Write(WarningMessages.InvalidHigherInstallerVersionInModule(wixMergeRow.SourceLineNumbers, wixMergeRow.Id.Id, moduleInstallerVersion, this.OutputInstallerVersion));
}
}
catch (FormatException)
{
throw new WixException(ErrorMessages.MissingOrInvalidModuleInstallerVersion(wixMergeRow.SourceLineNumbers, wixMergeRow.Id.Id, wixMergeRow.SourceFile, moduleInstallerVersionString));
}
}
}
}
catch (FileNotFoundException)
{
throw new WixException(ErrorMessages.FileNotFound(wixMergeRow.SourceLineNumbers, wixMergeRow.SourceFile));
}
catch (Win32Exception)
{
throw new WixException(ErrorMessages.CannotOpenMergeModule(wixMergeRow.SourceLineNumbers, wixMergeRow.Id.Id, wixMergeRow.SourceFile));
}
return(containsFiles);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, DocumentSummaryInformation dsi, SummaryInformation si)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
if (dsi != null)
{
workbook.DocumentSummaryInformation = dsi;
}
if (si != null)
{
workbook.SummaryInformation = si;
}
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
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 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string title)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Yeli";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "yelioa"; //填加xls文件作者信息
si.ApplicationName = title; //填加xls文件创建程序信息
si.LastAuthor = "yelioa"; //填加xls文件最后保存者信息
si.Comments = "说明信息"; //填加xls文件作者信息
si.Title = title; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
//dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd 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 = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//string[] chineseHeaders;
//if(strHeaderText=="部门积分信息")
//{
// chineseHeaders =new string[] { "姓名", "部门", "月度积分", "季度积分", "年度积分", "总积分" };
//}
//else
// chineseHeaders = new string[] {"序号", "编号", "提交日期", "审批日期", "财务审批日期",
// "提交人", "部门", "费用归属部门", "产品", "费用明细", "金额", "实报金额", "状态", "审批人", "抄送人", "备注", "审批意见", "审批结果"};
for (int i = 0; i < dtSource.Columns.Count; i++)
{
int colWidth = sheet.GetColumnWidth(i) * 2;
if (colWidth < 255 * 256)
{
sheet.SetColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
else
{
sheet.SetColumnWidth(i, 6000);
}
DataColumn column = dtSource.Columns[i];
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
//sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 255);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
#region 填充内容
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
if ("".Equals(drValue))
{
newCell.SetCellValue(drValue);
break;
}
DateTime date = Convert.ToDateTime(drValue);
drValue = date.ToString("yyyy-MM-dd HH:mm:ss");
//DateTime dateV;
//DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(drValue);
//newCell.SetCellType(HSSFCellType.FORMULA);
//newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();
return(ms);
}
}
public void Export2Excel(string fileName, DataSet data)
{
MemoryStream ms = new MemoryStream();
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet();
#region 文件属性
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "xi";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "xi";
si.ApplicationName = "xi";
si.LastAuthor = "xi";
si.CreateDateTime = DateTime.Now;
hssfworkbook.SummaryInformation = si;
#endregion
#region Excel单元格格式
int rowIndex = 0;
HSSFRow headRow = null;
HSSFRow titleRow = null;
HSSFRow dataRow = null;
HSSFCell cell = null;
HSSFCellStyle headStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
HSSFFont headfont = (HSSFFont)hssfworkbook.CreateFont();
headStyle.SetFont(headfont);
HSSFCellStyle titleStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.CENTER;
HSSFFont titlefont = (HSSFFont)hssfworkbook.CreateFont();
headStyle.SetFont(titlefont);
HSSFCellStyle cellDateStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat cellDateFormat = hssfworkbook.CreateDataFormat() as HSSFDataFormat;
if ("Y" == "Y")
{
cellDateStyle.DataFormat = cellDateFormat.GetFormat("yyyy-MM-dd HH:mm");
}
else
{
cellDateStyle.DataFormat = cellDateFormat.GetFormat("yyyy-MM-dd");
}
//数量小数格式化字符串
HSSFCellStyle cellNumStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat cellNumFormat = hssfworkbook.CreateDataFormat() as HSSFDataFormat;
string formatValue = "0";
string formatStr = string.Empty;
switch (formatValue)
{
#region 格式化
case "0":
formatStr = "0";
break;
case "1":
formatStr = "0.0";
break;
case "2":
formatStr = "0.00";
break;
case "3":
formatStr = "0.000";
break;
case "4":
formatStr = "0.0000";
break;
case "5":
formatStr = "0.00000";
break;
default:
formatStr = "{0:0}";
break;
#endregion
}
if (formatStr == "0" || formatStr == "0.00")
{
cellNumStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(formatStr);
}
else
{
cellNumStyle.DataFormat = cellNumFormat.GetFormat(formatStr);
}
//日期格式字符串
string dateFormat = "yyyy-MM-dd";
if ("Y" == "Y")
{
dateFormat += " " + "HH:mm";
}
#endregion
foreach (DataRow row in data.Tables[0].Rows)
{
#region 新建sheet 填写表头 列头
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)hssfworkbook.CreateSheet();
}
#region 列头
titleRow = (HSSFRow)sheet.CreateRow(rowIndex);
for (int i = 0; i < data.Tables[0].Columns.Count; i++)
{
titleRow.CreateCell(i).SetCellValue(data.Tables[0].Columns[i].ColumnName);
titleRow.GetCell(i).CellStyle = titleStyle;
}
rowIndex++;
#endregion
}
#endregion
#region 明细
dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
for (int i = 0; i < data.Tables[0].Columns.Count; i++)
{
cell = (HSSFCell)dataRow.CreateCell(i);
string value = row[data.Tables[0].Columns[i].ColumnName].ToString();
switch (data.Tables[0].Columns[i].DataType.FullName)
{
case "System.String":
cell.SetCellValue(value);
break;
case "System.DateTime":
DateTime datevalue;
DateTime.TryParse(value, out datevalue);
if (datevalue != new DateTime())
{
cell.CellStyle = cellDateStyle;
cell.SetCellValue(DateTime.Parse(datevalue.ToString(dateFormat)));
}
else
{
cell.SetCellValue("");
}
break;
case "System.Boolean":
bool boolvalue = false;
bool.TryParse(value, out boolvalue);
cell.SetCellValue(boolvalue);
break;
case "System.Int16":
Int16 int16 = 0;
Int16.TryParse(value, out int16);
cell.SetCellValue(int16);
break;
case "System.Int32":
Int32 int32 = 0;
Int32.TryParse(value, out int32);
cell.SetCellValue(int32);
break;
case "System.Int64":
Int64 int64 = 0;
Int64.TryParse(value, out int64);
cell.SetCellValue(int64);
break;
case "System.Byte":
int intV = 0;
int.TryParse(value, out intV);
cell.SetCellValue(intV);
break;
case "System.Decimal":
double decV = 0;
double.TryParse(value, out decV);
cell.SetCellValue(decV);
cell.CellStyle = cellNumStyle;
break;
case "System.Single":
float floatV = 0;
float.TryParse(value, out floatV);
cell.SetCellValue(floatV);
cell.CellStyle = cellNumStyle;
break;
case "System.Double":
double doubV = 0;
double.TryParse(value, out doubV);
cell.SetCellValue(doubV);
cell.CellStyle = cellNumStyle;
break;
case "System.DBNull":
cell.SetCellValue("");
break;
default:
cell.SetCellValue(value);
break;
}
}
#endregion
rowIndex++;
}
hssfworkbook.Write(ms);
ms.Flush();
ms.Position = 0;
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "GB2312";
HttpContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
HttpContext.Response.BinaryWrite(ms.ToArray());
HttpContext.Response.End();
ms.Close();
sheet = null;
hssfworkbook = null;
ms = null;
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://www.yongfa365.com/";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "西安联通"; //填加xls文件作者信息
si.ApplicationName = "水电房租管理系统"; //填加xls文件创建程序信息
si.LastAuthor = ""; //填加xls文件最后保存者信息
si.Comments = ""; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
//取得列宽
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])
{
if (intTemp > 12)
{
arrColWidth[j] = 12;
}
else
{
arrColWidth[j] = intTemp;
}
}
}
}
//数据内容样式
int rowIndex = 0;
ICellStyle CellStyle = workbook.CreateCellStyle();
CellStyle.WrapText = true;
CellStyle.Alignment = HorizontalAlignment.Left; //居中
CellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
CellStyle.BorderBottom = BorderStyle.Thin;
CellStyle.BorderLeft = BorderStyle.Thin;
CellStyle.BorderRight = BorderStyle.Thin;
CellStyle.BorderTop = BorderStyle.Thin;
//表格名称样式
ICellStyle TitleStyle = workbook.CreateCellStyle();
TitleStyle.Alignment = HorizontalAlignment.Center;
TitleStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
TitleStyle.SetFont(font);
TitleStyle.BorderBottom = BorderStyle.Thin;
TitleStyle.BorderLeft = BorderStyle.Thin;
TitleStyle.BorderRight = BorderStyle.Thin;
TitleStyle.BorderTop = BorderStyle.Thin;
//表头样式
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
headStyle.VerticalAlignment = VerticalAlignment.Center;
headStyle.WrapText = true;
IFont headFont = workbook.CreateFont();
headFont.FontHeightInPoints = 10;
headFont.Boldweight = 700;
headStyle.SetFont(headFont);
headStyle.BorderBottom = BorderStyle.Thin;
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
headStyle.FillPattern = FillPattern.SolidForeground;////设置背景颜色,这两行配合设置
headStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
ICellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.Alignment = HorizontalAlignment.Left;
dateStyle.VerticalAlignment = VerticalAlignment.Center;
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
dateStyle.BorderBottom = BorderStyle.Thin;
dateStyle.BorderLeft = BorderStyle.Thin;
dateStyle.BorderRight = BorderStyle.Thin;
dateStyle.BorderTop = BorderStyle.Thin;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
headerRow.GetCell(0).CellStyle = TitleStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
if (rowIndex < 2)
{
IRow headerRow = sheet.CreateRow(1);
headerRow.HeightInPoints = 25;
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = CellStyle;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
case "System.Single":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
sheet.ForceFormulaRecalculation = true;
sheet.PrintSetup.PaperSize = 9;
sheet.PrintSetup.Landscape = true;
sheet.PrintSetup.FitHeight = 3000;
sheet.PrintSetup.FitWidth = 3000;
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "东青信息";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "Allen"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//边框
dateStyle.BorderBottom = BorderStyle.Thin;
dateStyle.BorderLeft = BorderStyle.Thin;
dateStyle.BorderRight = BorderStyle.Thin;
dateStyle.BorderTop = BorderStyle.Thin;
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
//HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = HorizontalAlignment.Center;
//HSSFFont font = (HSSFFont)workbook.CreateFont();
//font.FontHeightInPoints = 20;
//font.Boldweight = 700;
//headStyle.SetFont(font);
//headerRow.GetCell(0).CellStyle = headStyle;
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
//HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = HorizontalAlignment.Center;
//边框
//headStyle.BorderBottom = BorderStyle.Thin;
//headStyle.BorderLeft = BorderStyle.Thin;
//headStyle.BorderRight = BorderStyle.Thin;
//headStyle.BorderTop = BorderStyle.Thin;
HSSFFont font = (HSSFFont)workbook.CreateFont();
//font.FontHeightInPoints = 10;
//font.Boldweight = 700;
//headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
//headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
#region 边框,样式
//HSSFCellStyle NewCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//NewCellStyle.Alignment = HorizontalAlignment.Center;
//边框
//NewCellStyle.BorderBottom = BorderStyle.Thin;
//NewCellStyle.BorderLeft = BorderStyle.Thin;
//NewCellStyle.BorderRight = BorderStyle.Thin;
//NewCellStyle.BorderTop = BorderStyle.Thin;
#endregion
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
//newCell.CellStyle = NewCellStyle;
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);
//newCell.CellStyle = NewCellStyle;
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
// newCell.CellStyle = NewCellStyle;
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
// newCell.CellStyle = NewCellStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
// newCell.CellStyle = NewCellStyle;
break;
default:
newCell.SetCellValue("");
// newCell.CellStyle = NewCellStyle;
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable 导出到 Excel 的 MemoryStream
/// </summary>
/// <param name="dtSource">源 DataTable</param>
/// <param name="strHeaderText">表头文本 空值未不要表头标题</param>
/// <returns></returns>
public static MemoryStream ExportExcel(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 文件属性
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "517best.com";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "517best.com";
si.ApplicationName = "517best.com";
si.LastAuthor = "517best.com";
si.Comments = "";
si.Title = "";
si.Subject = "";
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding("gb2312").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("gb2312").GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
int intTop = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表、填充表头、填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
intTop = 0;
#region 表头及样式
{
if (strHeaderText.Length > 0)
{
IRow headerRow = sheet.CreateRow(intTop);
intTop += 1;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(intTop);
intTop += 1;
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = intTop;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal":
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <Author>卜永济www.cnblogs.com/BuBu/ 2013-6-7 22:21:41</Author>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
//#Region "右击文件 属性信息"
if (true)
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Your Company";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "卜永济";
//填加xls文件作者信息
si.ApplicationName = "NPOI程序导出";
//填加xls文件创建程序信息
si.LastAuthor = "卜永济";
//填加xls文件最后保存者信息
si.Comments = "说明信息";
//填加xls文件作者信息
si.Title = "CRM";
//填加xls文件标题信息
si.Subject = "NPOI测试Demo";
//填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
//#End Region
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
//dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd")
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i <= dtSource.Rows.Count - 1; i++)
{
for (int j = 0; j <= dtSource.Columns.Count - 1; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
//#Region "新建表，填充表头，填充列头，样式"
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
//#Region "表头及样式"
if (!string.IsNullOrEmpty(strHeaderText))
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
//sheet.AddMergedRegion(New Region(0, 0, 0, dtSource.Columns.Count - 1))
//headerRow.Dispose()
}
//#End Region
if (string.IsNullOrEmpty(strHeaderText))
{
rowIndex = 0;
}
else
{
rowIndex = 1;
}
//#Region "列头及样式"
if (true)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose()
}
//#End Region
if (string.IsNullOrEmpty(strHeaderText))
{
rowIndex = 1;
}
else
{
rowIndex = 2;
}
}
//#End Region
//#Region "填充内容"
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String":
//字符串类型
newCell.SetCellValue(drValue);
break; // TODO: might not be correct. Was : Exit Select
case "System.DateTime":
//日期类型
DateTime dateV = default(DateTime);
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;
//格式化显示
break; // TODO: might not be correct. Was : Exit Select
case "System.Boolean":
//布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break; // TODO: might not be correct. Was : Exit Select
//整型
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break; // TODO: might not be correct. Was : Exit Select
//浮点型
case "System.Decimal":
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break; // TODO: might not be correct. Was : Exit Select
case "System.DBNull":
//空值处理
newCell.SetCellValue("");
break; // TODO: might not be correct. Was : Exit Select
default:
newCell.SetCellValue("");
break; // TODO: might not be correct. Was : Exit Select
}
}
//#End Region
rowIndex += 1;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose()
//workbook.Dispose()
return(ms);
}
}
/// <summary>
/// DataSet 导出到 Excel 的 MemoryStream
/// </summary>
/// <param name="dsSource">源 DataSet</param>
/// <param name="strHeaderText">表头文本 空值未不要表头标题(多个表对应多个表头以英文逗号(,)分开，个数应与表相同)</param>
/// <returns></returns>
public static MemoryStream ExportExcel(DataSet dsSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
#region 文件属性
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "517best.com";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "517best.com";
si.ApplicationName = "517best.com";
si.LastAuthor = "517best.com";
si.Comments = "";
si.Title = "";
si.Subject = "";
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
#endregion
#region 注释
//ICellStyle dateStyle = workbook.CreateCellStyle();
//IDataFormat format = workbook.CreateDataFormat();
//dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//ISheet sheet = workbook.CreateSheet();
//int[] arrColWidth = new int[dtSource.Columns.Count];
//foreach (DataColumn item in dtSource.Columns)
//{
// arrColWidth[item.Ordinal] = Encoding.GetEncoding("gb2312").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("gb2312").GetBytes(dtSource.Rows[i][j].ToString()).Length;
// if (intTemp > arrColWidth[j])
// {
// arrColWidth[j] = intTemp;
// }
// }
//}
//int rowIndex = 0;
//int intTop = 0;
//foreach (DataRow row in dtSource.Rows)
//{
// #region 新建表、填充表头、填充列头，样式
// if (rowIndex == 65535 || rowIndex == 0)
// {
// if (rowIndex != 0)
// {
// sheet = workbook.CreateSheet();
// }
// intTop = 0;
// #region 表头及样式
// {
// if (strHeaderText.Length > 0)
// {
// IRow headerRow = sheet.CreateRow(intTop);
// intTop += 1;
// headerRow.HeightInPoints = 25;
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// ICellStyle headStyle = workbook.CreateCellStyle();
// headStyle.Alignment = HorizontalAlignment.CENTER;
// IFont font = workbook.CreateFont();
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// headStyle.SetFont(font);
// headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
// }
// }
// #endregion
// #region 列头及样式
// {
// IRow headerRow = sheet.CreateRow(intTop);
// intTop += 1;
// ICellStyle headStyle = workbook.CreateCellStyle();
// headStyle.Alignment = HorizontalAlignment.CENTER;
// IFont font = workbook.CreateFont();
// font.Boldweight = 700;
// headStyle.SetFont(font);
// foreach (DataColumn column in dtSource.Columns)
// {
// headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
// //设置列宽
// sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
// }
// }
// #endregion
// rowIndex = intTop;
// }
// #endregion
// #region 填充内容
// IRow dataRow = sheet.CreateRow(rowIndex);
// foreach (DataColumn column in dtSource.Columns)
// {
// ICell newCell = dataRow.CreateCell(column.Ordinal);
// string drValue = row[column].ToString();
// switch (column.DataType.ToString())
// {
// case "System.String"://字符串类型
// newCell.SetCellValue(drValue);
// break;
// case "System.DateTime"://日期类型
// DateTime dateV;
// DateTime.TryParse(drValue, out dateV);
// newCell.SetCellValue(dateV);
// newCell.CellStyle = dateStyle;//格式化显示
// break;
// case "System.Boolean"://布尔型
// bool boolV = false;
// bool.TryParse(drValue, out boolV);
// newCell.SetCellValue(boolV);
// break;
// case "System.Int16":
// case "System.Int32":
// case "System.Int64":
// case "System.Byte":
// int intV = 0;
// int.TryParse(drValue, out intV);
// newCell.SetCellValue(intV);
// break;
// case "System.Decimal":
// case "System.Double":
// double doubV = 0;
// double.TryParse(drValue, out doubV);
// newCell.SetCellValue(doubV);
// break;
// case "System.DBNull"://空值处理
// newCell.SetCellValue("");
// break;
// default:
// newCell.SetCellValue("");
// break;
// }
// }
// #endregion
// rowIndex++;
//}
#endregion
string[] strNewText = strHeaderText.Split(Convert.ToChar(","));
if (dsSource.Tables.Count == strNewText.Length)
{
for (int i = 0; i < dsSource.Tables.Count; i++)
{
ExportFromDSExcel(workbook, dsSource.Tables[i], strNewText[i]);
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
public void TestInPlaceNPOIFSWrite()
{
NPOIFSFileSystem fs = null;
DirectoryEntry root = null;
DocumentNode sinfDoc = null;
DocumentNode dinfDoc = null;
SummaryInformation sinf = null;
DocumentSummaryInformation dinf = null;
// We need to work on a File for in-place changes, so create a temp one
FileInfo copy = TempFile.CreateTempFile("Test-HPSF", "ole2");
//copy.DeleteOnExit();
// Copy a test file over to a temp location
Stream inp = _samples.OpenResourceAsStream("TestShiftJIS.doc");
FileStream out1 = new FileStream(copy.FullName, FileMode.Create);
IOUtils.Copy(inp, out1);
inp.Close();
out1.Close();
// Open the copy in Read/write mode
fs = new NPOIFSFileSystem(new FileStream(copy.FullName, FileMode.Open, FileAccess.ReadWrite),
null, false, true);
root = fs.Root;
// Read the properties in there
sinfDoc = (DocumentNode)root.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME);
dinfDoc = (DocumentNode)root.GetEntry(DocumentSummaryInformation.DEFAULT_STREAM_NAME);
sinf = (SummaryInformation)PropertySetFactory.Create(new NDocumentInputStream(sinfDoc));
Assert.AreEqual(131077, sinf.OSVersion);
dinf = (DocumentSummaryInformation)PropertySetFactory.Create(new NDocumentInputStream(dinfDoc));
Assert.AreEqual(131077, dinf.OSVersion);
// Check they start as we expect
Assert.AreEqual("Reiichiro Hori", sinf.Author);
Assert.AreEqual("Microsoft Word 9.0", sinf.ApplicationName);
Assert.AreEqual("\u7b2c1\u7ae0", sinf.Title);
Assert.AreEqual("", dinf.Company);
Assert.AreEqual(null, dinf.Manager);
// Do an in-place replace via an InputStream
new NPOIFSDocument(sinfDoc).ReplaceContents(sinf.ToInputStream());
new NPOIFSDocument(dinfDoc).ReplaceContents(dinf.ToInputStream());
// Check it didn't Get Changed
sinfDoc = (DocumentNode)root.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME);
dinfDoc = (DocumentNode)root.GetEntry(DocumentSummaryInformation.DEFAULT_STREAM_NAME);
sinf = (SummaryInformation)PropertySetFactory.Create(new NDocumentInputStream(sinfDoc));
Assert.AreEqual(131077, sinf.OSVersion);
dinf = (DocumentSummaryInformation)PropertySetFactory.Create(new NDocumentInputStream(dinfDoc));
Assert.AreEqual(131077, dinf.OSVersion);
// Start again!
fs.Close();
inp = _samples.OpenResourceAsStream("TestShiftJIS.doc");
out1 = new FileStream(copy.FullName, FileMode.Open, FileAccess.ReadWrite);
IOUtils.Copy(inp, out1);
inp.Close();
out1.Close();
fs = new NPOIFSFileSystem(new FileStream(copy.FullName, FileMode.Open, FileAccess.ReadWrite),
null, false, true);
root = fs.Root;
// Read the properties in once more
sinfDoc = (DocumentNode)root.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME);
dinfDoc = (DocumentNode)root.GetEntry(DocumentSummaryInformation.DEFAULT_STREAM_NAME);
sinf = (SummaryInformation)PropertySetFactory.Create(new NDocumentInputStream(sinfDoc));
Assert.AreEqual(131077, sinf.OSVersion);
dinf = (DocumentSummaryInformation)PropertySetFactory.Create(new NDocumentInputStream(dinfDoc));
Assert.AreEqual(131077, dinf.OSVersion);
// Have them write themselves in-place with no Changes
sinf.Write(new NDocumentOutputStream(sinfDoc));
dinf.Write(new NDocumentOutputStream(dinfDoc));
// And also write to some bytes for Checking
MemoryStream sinfBytes = new MemoryStream();
sinf.Write(sinfBytes);
MemoryStream dinfBytes = new MemoryStream();
dinf.Write(dinfBytes);
// Check that the filesystem can give us back the same bytes
sinfDoc = (DocumentNode)root.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME);
dinfDoc = (DocumentNode)root.GetEntry(DocumentSummaryInformation.DEFAULT_STREAM_NAME);
byte[] sinfData = IOUtils.ToByteArray(new NDocumentInputStream(sinfDoc));
byte[] dinfData = IOUtils.ToByteArray(new NDocumentInputStream(dinfDoc));
Assert.That(sinfBytes.ToArray(), new EqualConstraint(sinfData));
Assert.That(dinfBytes.ToArray(), new EqualConstraint(dinfData));
// Read back in as-is
sinf = (SummaryInformation)PropertySetFactory.Create(new NDocumentInputStream(sinfDoc));
Assert.AreEqual(131077, sinf.OSVersion);
dinf = (DocumentSummaryInformation)PropertySetFactory.Create(new NDocumentInputStream(dinfDoc));
Assert.AreEqual(131077, dinf.OSVersion);
Assert.AreEqual("Reiichiro Hori", sinf.Author);
Assert.AreEqual("Microsoft Word 9.0", sinf.ApplicationName);
Assert.AreEqual("\u7b2c1\u7ae0", sinf.Title);
Assert.AreEqual("", dinf.Company);
Assert.AreEqual(null, dinf.Manager);
// Now alter a few of them
sinf.Author = (/*setter*/ "Changed Author");
sinf.Title = (/*setter*/ "Le titre \u00e9tait chang\u00e9");
dinf.Manager = (/*setter*/ "Changed Manager");
// Save this into the filesystem
sinf.Write(new NDocumentOutputStream(sinfDoc));
dinf.Write(new NDocumentOutputStream(dinfDoc));
// Read them back in again
sinfDoc = (DocumentNode)root.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME);
sinf = (SummaryInformation)PropertySetFactory.Create(new NDocumentInputStream(sinfDoc));
Assert.AreEqual(131077, sinf.OSVersion);
dinfDoc = (DocumentNode)root.GetEntry(DocumentSummaryInformation.DEFAULT_STREAM_NAME);
dinf = (DocumentSummaryInformation)PropertySetFactory.Create(new NDocumentInputStream(dinfDoc));
Assert.AreEqual(131077, dinf.OSVersion);
Assert.AreEqual("Changed Author", sinf.Author);
Assert.AreEqual("Microsoft Word 9.0", sinf.ApplicationName);
Assert.AreEqual("Le titre \u00e9tait chang\u00e9", sinf.Title);
Assert.AreEqual("", dinf.Company);
Assert.AreEqual("Changed Manager", dinf.Manager);
// Close the whole filesystem, and open it once more
fs.WriteFileSystem();
fs.Close();
fs = new NPOIFSFileSystem(new FileStream(copy.FullName, FileMode.Open));
root = fs.Root;
// Re-check on load
sinfDoc = (DocumentNode)root.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME);
sinf = (SummaryInformation)PropertySetFactory.Create(new NDocumentInputStream(sinfDoc));
Assert.AreEqual(131077, sinf.OSVersion);
dinfDoc = (DocumentNode)root.GetEntry(DocumentSummaryInformation.DEFAULT_STREAM_NAME);
dinf = (DocumentSummaryInformation)PropertySetFactory.Create(new NDocumentInputStream(dinfDoc));
Assert.AreEqual(131077, dinf.OSVersion);
Assert.AreEqual("Changed Author", sinf.Author);
Assert.AreEqual("Microsoft Word 9.0", sinf.ApplicationName);
Assert.AreEqual("Le titre \u00e9tait chang\u00e9", sinf.Title);
Assert.AreEqual("", dinf.Company);
Assert.AreEqual("Changed Manager", dinf.Manager);
// Tidy up
fs.Close();
copy.Delete();
}
public void Report(string FileName)
{
DivideByFactory();
int pos = 0;
//Export to excel
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
#region Create fonts and styles
HSSFFont HeaderF1 = hssfworkbook.CreateFont();
HeaderF1.FontHeightInPoints = 11;
HeaderF1.Boldweight = 11 * 256;
HeaderF1.FontName = "Calibri";
HSSFFont HeaderF2 = hssfworkbook.CreateFont();
HeaderF2.FontHeightInPoints = 10;
HeaderF2.Boldweight = 10 * 256;
HeaderF2.FontName = "Calibri";
HSSFFont HeaderF3 = hssfworkbook.CreateFont();
HeaderF3.FontHeightInPoints = 9;
HeaderF3.Boldweight = 9 * 256;
HeaderF3.FontName = "Calibri";
HSSFFont SimpleF = hssfworkbook.CreateFont();
SimpleF.FontHeightInPoints = 10;
SimpleF.FontName = "Calibri";
HSSFCellStyle SimpleCS = hssfworkbook.CreateCellStyle();
SimpleCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SimpleCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SimpleCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SimpleCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SimpleCS.TopBorderColor = HSSFColor.BLACK.index;
SimpleCS.SetFont(SimpleF);
HSSFCellStyle CountDecCS = hssfworkbook.CreateCellStyle();
CountDecCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.000");
CountDecCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountDecCS.BottomBorderColor = HSSFColor.BLACK.index;
CountDecCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountDecCS.LeftBorderColor = HSSFColor.BLACK.index;
CountDecCS.BorderRight = HSSFCellStyle.BORDER_THIN;
CountDecCS.RightBorderColor = HSSFColor.BLACK.index;
CountDecCS.BorderTop = HSSFCellStyle.BORDER_THIN;
CountDecCS.TopBorderColor = HSSFColor.BLACK.index;
CountDecCS.SetFont(SimpleF);
HSSFCellStyle CountCS = hssfworkbook.CreateCellStyle();
CountCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
CountCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountCS.BottomBorderColor = HSSFColor.BLACK.index;
CountCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountCS.LeftBorderColor = HSSFColor.BLACK.index;
CountCS.BorderRight = HSSFCellStyle.BORDER_THIN;
CountCS.RightBorderColor = HSSFColor.BLACK.index;
CountCS.BorderTop = HSSFCellStyle.BORDER_THIN;
CountCS.TopBorderColor = HSSFColor.BLACK.index;
CountCS.SetFont(SimpleF);
HSSFCellStyle SimpleHeaderCS = hssfworkbook.CreateCellStyle();
SimpleHeaderCS.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderLeft = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderRight = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderTop = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.TopBorderColor = HSSFColor.BLACK.index;
//SimpleHeaderCS.WrapText = true;
SimpleHeaderCS.SetFont(HeaderF3);
#endregion Create fonts and styles
HSSFCell Cell1;
if (FrontsProfilDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Фасады, Профиль");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Фасад");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Квадратура");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < FrontsProfilDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["PackingDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr36"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr37"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr38"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr1"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToDouble(FrontsProfilDT.Rows[i]["Square"]));
Cell1.CellStyle = CountDecCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos = 0;
if (FrontsTPSDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Фасады, ТПС");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Фасад");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Квадратура");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < FrontsTPSDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["PackingDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr36"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr37"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr38"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr1"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToDouble(FrontsTPSDT.Rows[i]["Square"]));
Cell1.CellStyle = CountDecCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos = 0;
if (DecorProfilDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Декор, Профиль");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Артикул");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Длина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Высота");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ширина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < DecorProfilDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["PackingDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Length"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Height"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Width"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos = 0;
if (DecorTPSDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Декор, ТПС");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Артикул");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Длина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Высота");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ширина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < DecorTPSDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["PackingDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Length"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Height"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Width"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
string tempFolder = System.Environment.GetEnvironmentVariable("TEMP");
FileInfo file = new FileInfo(tempFolder + @"\" + FileName + ".xls");
int j = 1;
while (file.Exists == true)
{
file = new FileInfo(tempFolder + @"\" + FileName + "(" + j++ + ").xls");
}
FileStream NewFile = new FileStream(file.FullName, FileMode.Create);
hssfworkbook.Write(NewFile);
NewFile.Close();
System.Diagnostics.Process.Start(file.FullName);
ClearReport();
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="strHeaderText">Excel表头文本（例如：车辆列表）</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length + 5;
}
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 + 3;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
if (strHeaderText != "")
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
{
IRow headerRow;
if (strHeaderText != "")
{
headerRow = sheet.CreateRow(1);
}
else
{
headerRow = sheet.CreateRow(0);
}
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.FontName = "宋体";
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
if (strHeaderText != "")
{
rowIndex = 2;
}
else
{
rowIndex = 1;
}
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
ICellStyle contentStyle = workbook.CreateCellStyle();
{
contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.FontName = "宋体";
// font.Boldweight = 700;
contentStyle.SetFont(font);
}
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = contentStyle;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.Dispose();
return(ms);
}
}
public ToxyMetadata Parse()
{
if (!System.IO.File.Exists(Context.Path))
{
throw new System.IO.FileNotFoundException("File " + Context.Path + " is not found");
}
ToxyMetadata metadata = new ToxyMetadata();
using (Stream stream = File.OpenRead(Context.Path))
{
POIFSFileSystem poifs = new NPOI.POIFS.FileSystem.POIFSFileSystem(stream);
DirectoryNode root = poifs.Root;
SummaryInformation si = null;
DocumentSummaryInformation dsi = null;
if (root.HasEntry(SummaryInformation.DEFAULT_STREAM_NAME))
{
PropertySet ps = GetPropertySet(root, SummaryInformation.DEFAULT_STREAM_NAME);
if (ps is SummaryInformation)
{
si = ps as SummaryInformation;
if (si.Author != null)
{
metadata.Add("Author", si.Author);
}
if (si.ApplicationName != null)
{
metadata.Add("ApplicationName", si.ApplicationName);
}
if (si.ClassID != null)
{
metadata.Add("ClassID", si.ClassID.ToString());
}
if (si.CharCount != 0)
{
metadata.Add("CharCount", si.CharCount);
}
if (si.ByteOrder != 0)
{
metadata.Add("ByteOrder", si.ByteOrder);
}
if (si.CreateDateTime != null)
{
metadata.Add("CreateDateTime", si.CreateDateTime);
}
if (si.LastAuthor != null)
{
metadata.Add("LastAuthor", si.LastAuthor);
}
if (si.Keywords != null)
{
metadata.Add("Keywords", si.Keywords);
}
if (si.LastPrinted != null)
{
metadata.Add("LastPrinted", si.LastPrinted);
}
if (si.LastSaveDateTime != null)
{
metadata.Add("LastSaveDateTime", si.LastSaveDateTime);
}
if (si.PageCount != 0)
{
metadata.Add("PageCount", si.PageCount);
}
if (si.WordCount != 0)
{
metadata.Add("WordCount", si.WordCount);
}
if (si.Comments != null)
{
metadata.Add("Comments", si.Comments);
}
if (si.EditTime != 0)
{
metadata.Add("EditTime", si.EditTime);
}
if (si.RevNumber != null)
{
metadata.Add("RevNumber", si.RevNumber);
}
if (si.Security != 0)
{
metadata.Add("Security", si.Security);
}
if (si.Subject != null)
{
metadata.Add("Subject", si.Subject);
}
if (si.Title != null)
{
metadata.Add("Title", si.Title);
}
if (si.Template != null)
{
metadata.Add("Template", si.Template);
}
}
}
if (root.HasEntry(DocumentSummaryInformation.DEFAULT_STREAM_NAME))
{
PropertySet ps = GetPropertySet(root, DocumentSummaryInformation.DEFAULT_STREAM_NAME);
if (ps is DocumentSummaryInformation)
{
dsi = ps as DocumentSummaryInformation;
if (dsi.ByteCount > 0)
{
metadata.Add("ByteCount", dsi.ByteCount);
}
else if (dsi.Company != null)
{
metadata.Add("Company", dsi.Company);
}
else if (dsi.Format > 0)
{
metadata.Add("Format", dsi.Format);
}
else if (dsi.LineCount > 0)
{
metadata.Add("LineCount", dsi.LineCount);
}
else if (dsi.LinksDirty)
{
metadata.Add("LinksDirty", true);
}
else if (dsi.Manager != null)
{
metadata.Add("Manager", dsi.Manager);
}
else if (dsi.NoteCount > 0)
{
metadata.Add("NoteCount", dsi.NoteCount);
}
else if (dsi.Scale)
{
metadata.Add("Scale", dsi.Scale);
}
else if (dsi.HiddenCount > 0)
{
metadata.Add("HiddenCount", dsi.Company);
}
else if (dsi.MMClipCount > 0)
{
metadata.Add("MMClipCount", dsi.MMClipCount);
}
else if (dsi.ParCount > 0)
{
metadata.Add("ParCount", dsi.ParCount);
}
}
}
}
return(metadata);
}