public void TestRemoveMerged()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
CellRangeAddress region = new CellRangeAddress(0, 1, 0, 1);
sheet.AddMergedRegion(region);
region = new CellRangeAddress(1, 2, 0, 1);
sheet.AddMergedRegion(region);
sheet.RemoveMergedRegion(0);
region = sheet.GetMergedRegion(0);
Assert.AreEqual(1, region.FirstRow, "Left over region should be starting at row 1");
sheet.RemoveMergedRegion(0);
Assert.AreEqual(0, sheet.NumMergedRegions, "there should be no merged regions left!");
//an, Add, Remove, get(0) would null pointer
sheet.AddMergedRegion(region);
Assert.AreEqual(1, sheet.NumMergedRegions, "there should now be one merged region!");
sheet.RemoveMergedRegion(0);
Assert.AreEqual(0, sheet.NumMergedRegions, "there should now be zero merged regions!");
//Add it again!
region.LastRow = 4;
sheet.AddMergedRegion(region);
Assert.AreEqual(1, sheet.NumMergedRegions, "there should now be one merged region!");
//should exist now!
Assert.IsTrue(1 <= sheet.NumMergedRegions, "there isn't more than one merged region in there");
region = sheet.GetMergedRegion(0);
Assert.AreEqual(4, region.LastRow, "the merged row to doesnt Match the one we put in ");
}
protected static void MergeCells(HSSFSheet sheetfill, GridDefine grid, GroupCollection groups, int index, int startRow, int count)
{
if (groups == null || groups.Count <= index)
{
return;
}
Group group = groups[index];
GridCellFill fill = grid.GridCellFills[group.ColumnName];
if (fill == null || count == 0)
{
return;
}
int startRowIndex = startRow;
int rowIndex = startRowIndex;
string curvalue = Convert.ToString(YZExcelHelper2003.GetCellValue(null, sheetfill.GetRow(startRowIndex).GetCell(fill.ColumnIndex) as HSSFCell));
for (int i = 0; i < count; i++)
{
string value = null;
rowIndex = startRow + i;
value = Convert.ToString(YZExcelHelper2003.GetCellValue(null, sheetfill.GetRow(rowIndex).GetCell(fill.ColumnIndex) as HSSFCell));
if (String.Compare(value, curvalue, true) == 0)
{
continue;
}
rowIndex--;
if (rowIndex - startRowIndex >= 1)
{
CellRangeAddress range = new CellRangeAddress(startRowIndex, rowIndex, fill.ColumnIndex, fill.ColumnIndex);
sheetfill.AddMergedRegion(range);
if (index < groups.Count - 1)
{
MergeCells(sheetfill, grid, groups, index + 1, startRowIndex, rowIndex - startRowIndex + 1);
}
}
startRowIndex = rowIndex + 1;
curvalue = value;
}
if (rowIndex - startRowIndex >= 1)
{
CellRangeAddress range = new CellRangeAddress(startRowIndex, rowIndex, fill.ColumnIndex, fill.ColumnIndex);
sheetfill.AddMergedRegion(range);
if (index < groups.Count - 1)
{
MergeCells(sheetfill, grid, groups, index + 1, startRowIndex, rowIndex - startRowIndex + 1);
}
}
}
private void GenerateReportName(HSSFSheet sheet, string reportName)
{
DateTime d = DateTime.Now;
CellRangeAddress rangeAddress = new CellRangeAddress(1, 1, 1, 2);
sheet.AddMergedRegion(rangeAddress);
CellRangeAddress Address = new CellRangeAddress(1, 1, 3, 4);
sheet.AddMergedRegion(Address);
HSSFRow reportRow = sheet.CreateRow(1);
reportRow.CreateCell(1).SetCellValue(Resource.GetString("ReportName") + Resource.GetString(reportName));
reportRow.CreateCell(3).SetCellValue(Resource.GetString("ReportDate") + d.ConvertMiladiToJalali());
}
public void GenerateHeader(int pageNumber)
{
if (pageNumber == 0)
{
for (int i = 21; i < pageNumOfRows; i++)
{
hssfSheet.CreateRow(i);
hssfSheet.GetRow(i).Height = 345;
}
return;
}
// Jump In Pages
localRow = pageNumber * pageNumOfRows;
for (int i = localRow; i < localRow + pageNumOfRows; i++)
{
hssfSheet.CreateRow(i);
hssfSheet.GetRow(i).Height = 345;
}
hssfSheet.GetRow(localRow + 1).CreateCell(0).SetCellValue("Carrying Forward ");
hssfSheet.GetRow(localRow + 1).GetCell(0).CellStyle = hssfSheet.GetRow(7).GetCell(7).CellStyle;
hssfSheet.GetRow(localRow + 2).CreateCell(0).SetCellValue("Tendon reference");
hssfSheet.GetRow(localRow + 2).CreateCell(1).SetCellValue("Sequence (strand)");
hssfSheet.GetRow(localRow + 2).CreateCell(2).SetCellValue("Jacking Force KN");
hssfSheet.GetRow(localRow + 2).CreateCell(3).SetCellValue("Gauge Reading PSI");
hssfSheet.GetRow(localRow + 2).CreateCell(4).SetCellValue("Actual Extension MM");
hssfSheet.GetRow(localRow + 2).CreateCell(5).SetCellValue("Average Extension MM");
hssfSheet.GetRow(localRow + 2).CreateCell(6).SetCellValue("Theor. Extension MM");
hssfSheet.GetRow(localRow + 2).CreateCell(7).SetCellValue("Deviation %");
hssfSheet.GetRow(localRow + 2).CreateCell(8).SetCellValue("Remarks");
hssfSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));
//merged cells on mutiple rows
for (int i = 0; i < 9; i++)
{
hssfSheet.GetRow(localRow + 3).CreateCell(i).SetCellValue("");
hssfSheet.GetRow(localRow + 4).CreateCell(i).SetCellValue("");
CellRangeAddress region = new CellRangeAddress(localRow + 2, localRow + 4, i, i);
hssfSheet.AddMergedRegion(region);
hssfSheet.GetRow(localRow + 2).GetCell(i).CellStyle = hssfSheet.GetRow(13).GetCell(i).CellStyle;
hssfSheet.GetRow(localRow + 3).GetCell(i).CellStyle = hssfSheet.GetRow(14).GetCell(i).CellStyle;
hssfSheet.GetRow(localRow + 4).GetCell(i).CellStyle = hssfSheet.GetRow(15).GetCell(i).CellStyle;
}
}
static void Main(string[] args)
{
var hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet("sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue("Sales Report");
ICellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.FontHeight = 20 * 20;
style.SetFont(font);
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));
FileStream file = new FileStream(@"test.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
Console.WriteLine("完成");
Console.ReadKey();
}
/// <summary>
/// 生成Excel的表头
/// </summary>
/// <param name="sheet"></param>
/// <param name="cols"></param>
/// <param name="rowIndex"></param>
/// <param name="colIndex"></param>
/// <param name="style"></param>
/// <returns></returns>
private int MakeExcelHeader(HSSFSheet sheet, IEnumerable<IGridColumn<TModel>> cols, int rowIndex, int colIndex, ICellStyle style)
{
var row = sheet.CreateRow(rowIndex) as HSSFRow;
int maxLevel = cols.Select(x => x.MaxLevel).Max();
//循环所有列
foreach (var col in cols)
{
//添加新单元格
var cell = row.CreateCell(colIndex);
cell.CellStyle = style;
cell.SetCellValue(col.Title);
var bcount = col.BottomChildren.Count();
var rowspan = 0;
if (rowIndex == 0)
{
rowspan = maxLevel - col.MaxLevel;
}
var cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex + rowspan, colIndex, colIndex + bcount - 1);
sheet.AddMergedRegion(cellRangeAddress);
for (int i = cellRangeAddress.FirstRow; i <= cellRangeAddress.LastRow; i++)
{
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
var c = HSSFCellUtil.GetCell(HSSFCellUtil.GetRow(i, sheet), j);
c.CellStyle = style;
}
}
if (col.Children != null && col.Children.Count() > 0)
{
MakeExcelHeader(sheet, col.Children, rowIndex + rowspan + 1, colIndex, style);
}
colIndex += bcount;
}
return maxLevel;
}
//MergeCell sau khi do du lieu ra bang
public CellRangeAddress MergeCell(HSSFSheet sheet, int rowIndex, int n)
{
CellRangeAddress MergeNameCell = new CellRangeAddress(rowIndex, rowIndex + 1, n, n);
sheet.AddMergedRegion(MergeNameCell);
return(MergeNameCell);
}
private static void CopyRow(XSSFSheet srcSheet, HSSFSheet destSheet, XSSFRow srcRow, HSSFRow destRow,
Dictionary <int, XSSFCellStyle> styleMap, HSSFWorkbook retVal)
{
// manage a list of merged zone in order to not insert two times a
// merged zone
List <CellRangeAddress> mergedRegions = new List <CellRangeAddress>();
destRow.Height = srcRow.Height;
// pour chaque row
for (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j++)
{
XSSFCell oldCell = (XSSFCell)srcRow.GetCell(j); // ancienne cell
HSSFCell newCell = (HSSFCell)destRow.GetCell(j); // new cell
if (oldCell != null)
{
if (newCell == null)
{
newCell = (HSSFCell)destRow.CreateCell(j);
}
// copy chaque cell
CopyCell(oldCell, newCell, styleMap, retVal);
// copy les informations de fusion entre les cellules
CellRangeAddress mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum,
(short)oldCell.ColumnIndex);
if (mergedRegion != null)
{
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,
mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);
if (IsNewMergedRegion(newMergedRegion, mergedRegions))
{
mergedRegions.Add(newMergedRegion);
destSheet.AddMergedRegion(newMergedRegion);
}
if (newMergedRegion.FirstColumn == 0 && newMergedRegion.LastColumn == 6 && newMergedRegion.FirstRow == newMergedRegion.LastRow)
{
HSSFCellStyle style2 = (HSSFCellStyle)retVal.CreateCellStyle();
style2.VerticalAlignment = VerticalAlignment.Center;
style2.Alignment = HorizontalAlignment.Left;
style2.FillForegroundColor = HSSFColor.Teal.Index;
style2.FillPattern = FillPattern.SolidForeground;
for (int i = destRow.FirstCellNum; i <= destRow.LastCellNum; i++)
{
if (destRow.GetCell(i) != null)
{
destRow.GetCell(i).CellStyle = style2;
}
}
}
}
}
}
}
public HSSFWorkbook ExportToExcelInOneSheet2 <T>(List <T> list, string fileName)
{
if (SheetNames.Count == 0)
{
SheetNames.Add("CN");
}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(SheetNames.FirstOrDefault());
if (list.Count > 0)
{
//填充表头
Type t = list.FirstOrDefault().GetType();
System.Reflection.PropertyInfo[] ps = t.GetProperties();
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
int headIndex = 0;
foreach (System.Reflection.PropertyInfo p in ps)
{
dataRow.CreateCell(headIndex).SetCellValue(p.Name);
headIndex++;
}
int rowIndex = 1;
foreach (var item in list)
{
dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
int colIndex = 0;
foreach (System.Reflection.PropertyInfo p in ps)
{
try
{
dataRow.CreateCell(colIndex).SetCellValue(p.GetValue(item, null) + "");
}
catch
{
}
colIndex++;
}
rowIndex++;
}
for (int i = 1; i < list.Count; i += 4)
{
sheet.AddMergedRegion(new CellRangeAddress(i, i + 3, 0, 0));
}
}
return(workbook);
//保存
//SaveAndResponseFile(workbook, "数据关系视图");
//workbook.Dispose();
}
/// <summary>
/// 创建标题行 合并列
/// </summary>
public void CreateTitleRow(HSSFSheet sheet, string title, HSSFCellStyle style, int colspan, int rowIndex = 0, int rowHeight = 0)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(rowIndex);//建立一行，NPOI要选择立行才参建立CELL
if (rowHeight != 0)
{
row.Height = (short)(rowHeight * 20);
}
HSSFCell cell = (HSSFCell)row.CreateCell(rowIndex);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, colspan - 1));//合并单完格
cell.CellStyle = style;
cell.SetCellValue(title);
}
public void CreateFormat()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
HSSFRow dataRow = sheet.CreateRow(1) as HSSFRow;
dataRow = sheet.CreateRow(1) as HSSFRow;
CellRangeAddress region = new CellRangeAddress(1, 1, 1, 2);
sheet.AddMergedRegion(region);
ICell cell = dataRow.CreateCell(1);
cell.SetCellValue("test");
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BottomBorderColor = HSSFColor.Black.Index;
style.LeftBorderColor = HSSFColor.Black.Index;
style.RightBorderColor = HSSFColor.Black.Index;
style.TopBorderColor = HSSFColor.Black.Index;
//cell.CellStyle = style;
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow row = HSSFCellUtil.GetRow(i, sheet);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
using (FileStream fs = new FileStream("C:\\TestConsole.xls", FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
/// <summary>
/// 表头及样式
/// </summary>
/// <param name="headerTextDic"></param>
/// <param name="fileName"></param>
/// <param name="isShowTitle"></param>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
private static void SetDataTitle(Dictionary <string, string> headerTextDic, string fileName, bool isShowTitle, HSSFWorkbook workbook, HSSFSheet sheet)
{
if (isShowTitle)
{
var headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(fileName);
var headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.CENTER;
var font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, headerTextDic.Count - 1));
}
}
/// <summary>
/// 导出Excel（各实体详细内容）
/// </summary>
/// <param name="list"></param>
/// <param name="filePath"></param>
public void SaveDetailToExcelFile(List <DataDetailDictionaryDTO> list, string filePath)
{
try
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet = tfExcel.CreateSheet(hssfworkbook, "数据字典");
//冻结列头行
sheet.CreateFreezePane(0, 1);
tfExcel.SetColumnWidth(sheet, new List <int>()
{
15, 50, 40, 25, 55
});
HSSFCellStyle style = tfExcel.GetStyle1(hssfworkbook);
tfExcel.CreateRow(sheet, 0, style, new List <string>()
{
"项目代码", "实体名字", "实体字段", "字段类型", "注释"
});
int rowIndex = 1;
//根据相同的实体名进行分组
var groupList = list.GroupBy(a => a.EntityName).ToList();
groupList.ForEach(a =>
{
//相同的实体名进行合并
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + a.Count() - 1, 1, 1));
a.ToList().ForEach(b =>
{
tfExcel.CreateRow(sheet, rowIndex, style,
new List <string>()
{
b.ProjectCode, b.EntityName, b.TableField, b.Type, b.Summary
});
rowIndex++;
});
});
using (FileStream file = new FileStream(filePath, FileMode.Create))
{
hssfworkbook.Write(file);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public void TestAutoSizeColumn()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("43902.xls");
String sheetName = "my sheet";
HSSFSheet sheet = (HSSFSheet)wb.GetSheet(sheetName);
// Can't use literal numbers for column sizes, as
// will come out with different values on different
// machines based on the fonts available.
// So, we use ranges, which are pretty large, but
// thankfully don't overlap!
int minWithRow1And2 = 6400;
int maxWithRow1And2 = 7800;
int minWithRow1Only = 2750;
int maxWithRow1Only = 3300;
// autoSize the first column and check its size before the merged region (1,0,1,1) is set:
// it has to be based on the 2nd row width
sheet.AutoSizeColumn(0);
Assert.IsTrue(sheet.GetColumnWidth(0) >= minWithRow1And2, "Column autosized with only one row: wrong width");
Assert.IsTrue(sheet.GetColumnWidth(0) <= maxWithRow1And2, "Column autosized with only one row: wrong width");
//Create a region over the 2nd row and auto size the first column
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 1));
sheet.AutoSizeColumn(0);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb);
// Check that the autoSized column width has ignored the 2nd row
// because it is included in a merged region (Excel like behavior)
NPOI.SS.UserModel.ISheet sheet2 = wb2.GetSheet(sheetName);
Assert.IsTrue(sheet2.GetColumnWidth(0) >= minWithRow1Only);
Assert.IsTrue(sheet2.GetColumnWidth(0) <= maxWithRow1Only);
// Remove the 2nd row merged region and Check that the 2nd row value is used to the AutoSizeColumn width
sheet2.RemoveMergedRegion(1);
sheet2.AutoSizeColumn(0);
HSSFWorkbook wb3 = HSSFTestDataSamples.WriteOutAndReadBack(wb2);
NPOI.SS.UserModel.ISheet sheet3 = wb3.GetSheet(sheetName);
Assert.IsTrue(sheet3.GetColumnWidth(0) >= minWithRow1And2);
Assert.IsTrue(sheet3.GetColumnWidth(0) <= maxWithRow1And2);
}
protected static void ShiftRows(HSSFSheet sheet, int startRowIndex, int offset)
{
HSSFSheet sheetfill = sheet;
int firstRowIndex = Math.Min(startRowIndex, startRowIndex + offset);
int lastRowIndex = Math.Max(startRowIndex, startRowIndex + offset) - 1;
CellRangeAddressCollection savedRegions = new CellRangeAddressCollection();
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress region = sheet.GetMergedRegion(i);
if (region.FirstRow >= firstRowIndex && region.LastRow <= lastRowIndex)
{
sheet.RemoveMergedRegion(i);
i--;
continue;
}
if ((region.FirstRow < firstRowIndex && region.LastRow >= firstRowIndex) ||
(region.LastRow > lastRowIndex && region.FirstRow <= lastRowIndex))
{
savedRegions.Add(region);
sheet.RemoveMergedRegion(i);
i--;
continue;
}
}
if (sheet.LastRowNum >= startRowIndex)
{
sheet.ShiftRows(startRowIndex, sheet.LastRowNum, offset, true, true);
}
foreach (CellRangeAddress region in savedRegions)
{
region.LastRow += offset;
sheet.AddMergedRegion(region);
}
}
public void TestShiftMerged()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
Row row = sheet.CreateRow(0);
Cell cell = row.CreateCell(0);
cell.SetCellValue(new HSSFRichTextString("first row, first cell"));
row = sheet.CreateRow(1);
cell = row.CreateCell(1);
cell.SetCellValue(new HSSFRichTextString("second row, second cell"));
CellRangeAddress region = new CellRangeAddress(1, 1, 0, 1);
sheet.AddMergedRegion(region);
sheet.ShiftRows(1, 1, 1);
region = sheet.GetMergedRegion(0);
Assert.AreEqual(2, region.FirstRow, "Merged region not moved over to row 2");
}
//private static WriteLog wl = new WriteLog();
#region 从datatable中将数据导出到excel
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "柳永法"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// 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 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
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) 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;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#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": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
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();
return(ms);
}
}
public void MergedRegion(int firstRow, int lastRow, int firstCol, int lastCol)
{
currentSheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
/// <summary>
/// 合并单元格
/// 参数：Sheet,开始行、结束行、开始列、结束列
/// </summary>
/// <param name="sheet"></param>
/// <param name="topRowIndex"></param>
/// <param name="bottomRowIndex"></param>
/// <param name="topCellIndex"></param>
/// <param name="bottomCellIndex"></param>
private static void SetMergeCell(HSSFSheet sheet, int topRowIndex, int bottomRowIndex, int topCellIndex, int bottomCellIndex)
{
var range = new CellRangeAddress(topRowIndex, bottomRowIndex, topCellIndex, bottomCellIndex);
sheet.AddMergedRegion(range);
}
private void button1_Click(object sender, EventArgs e)
{
//查询数据，显示到表格上
ManagerInfoBll miBll = new ManagerInfoBll();
var list = miBll.GetList();
dataGridView1.DataSource = list;
//进行excel生成创建操作
//1、创建workbook,不指定参数，表示创建一个新的工作本
HSSFWorkbook workbook = new HSSFWorkbook();
//2、创建sheet
HSSFSheet sheet = workbook.CreateSheet("管理员");
//3、创建row
HSSFRow row = sheet.CreateRow(0);
//4、创建cell
HSSFCell cell0 = row.CreateCell(0);
cell0.SetCellValue("管理员列表");
//5、设置合并单元格
sheet.AddMergedRegion(new NPOI.HSSF.Util.Region(0, 0, 0, 3));
//6、设置单元格居中
HSSFCellStyle styleTitle = workbook.CreateCellStyle();
styleTitle.Alignment = 2;//居中
cell0.CellStyle = styleTitle;
//6.1设置字体
HSSFFont fontTitle = workbook.CreateFont();
fontTitle.FontHeightInPoints = 14;
styleTitle.SetFont(fontTitle);
//7、创建标题行
//7.1创建行
HSSFRow rowTitle = sheet.CreateRow(1);
//7.2创建单元格
HSSFCell cellTitle0 = rowTitle.CreateCell(0);
cellTitle0.SetCellValue("编号");
cellTitle0.CellStyle = styleTitle;
HSSFCell cellTitle1 = rowTitle.CreateCell(1);
cellTitle1.SetCellValue("姓名");
cellTitle1.CellStyle = styleTitle;
HSSFCell cellTitle2 = rowTitle.CreateCell(2);
cellTitle2.SetCellValue("密码");
cellTitle2.CellStyle = styleTitle;
HSSFCell cellTitle3 = rowTitle.CreateCell(3);
cellTitle3.SetCellValue("类型");
cellTitle3.CellStyle = styleTitle;
//8、遍历集合，创建正文数据
//8.1遍历集合
int rowIndex = 2;
foreach (var mi in list)
{
//8.2创建行
HSSFRow rowData = sheet.CreateRow(rowIndex++);
//8.3创建数据单元格
HSSFCell cellData0 = rowData.CreateCell(0);
cellData0.SetCellValue(mi.MId);
HSSFCell cellData1 = rowData.CreateCell(1);
cellData1.SetCellValue(mi.MName);
HSSFCell cellData2 = rowData.CreateCell(2);
cellData2.SetCellValue(mi.MPwd);
HSSFCell cellData3 = rowData.CreateCell(3);
cellData3.SetCellValue(mi.MType == 1?"经理":"店员");
}
//保存工作本
FileStream stream = new FileStream(@"C:\Users\q1\Desktop\t1.xls", FileMode.Create);
workbook.Write(stream);
stream.Close();
stream.Dispose();
}
private string excelaction(JsonArrayParse jp)
{
JsonObjectCollection collection = new JsonObjectCollection();
string flag = "1";
string pathName = "";
try
{
pathName = string.Format("未缴明细报表[查询日期-{0}][客户名称-{1}][费用类型-{2}].xls", jp.getValue("Month"), jp.getValue("CustName") == "" ? "全部" : jp.getValue("CustName"), jp.getValue("FeeTypeName"));
//pathName = "未缴明细报表[客户" + GetDate().ToString("yyyy-MM") + ".xls";
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("未缴明细表" + GetDate().ToString("yyyy-MM"));
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("序号");
headerRow.CreateCell(1).SetCellValue("订单编号");
headerRow.CreateCell(2).SetCellValue("订单类别");
headerRow.CreateCell(3).SetCellValue("订单状态");
headerRow.CreateCell(4).SetCellValue("客户名称");
headerRow.CreateCell(5).SetCellValue("所属年月");
headerRow.CreateCell(6).SetCellValue("合同编号");
headerRow.CreateCell(7).SetCellValue("资源编号");
headerRow.CreateCell(8).SetCellValue("费用项目");
headerRow.CreateCell(9).SetCellValue("应缴金额");
headerRow.CreateCell(10).SetCellValue("已缴金额");
headerRow.CreateCell(11).SetCellValue("未缴金额");
int rowIndex = 1;
foreach (DataRow it in GetUnPaidListProc(jp.getValue("BeginMonth"), jp.getValue("EndMonth"), jp.getValue("CustName"), jp.getValue("FeeTypeNo")).Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
dataRow.CreateCell(0).SetCellValue(rowIndex.ToString());
dataRow.CreateCell(1).SetCellValue(it["OrderNo"].ToString());
dataRow.CreateCell(2).SetCellValue(it["OrderTypeName"].ToString());
dataRow.CreateCell(3).SetCellValue(it["OrderStatus"].ToString());
dataRow.CreateCell(4).SetCellValue(it["CustName"].ToString());
dataRow.CreateCell(5).SetCellValue(it["OrderTime"].ToString());
dataRow.CreateCell(6).SetCellValue(it["ODContractNo"].ToString());
dataRow.CreateCell(7).SetCellValue(it["ResourceNo"].ToString());
dataRow.CreateCell(8).SetCellValue(it["SRVName"].ToString());
dataRow.CreateCell(9).SetCellValue(Convert.ToDouble(it["ODARAmount"].ToString())); // ParseDecimalForString(it["ODARAmount"].ToString()).ToString("0.##"));
dataRow.CreateCell(10).SetCellValue(Convert.ToDouble(it["PaidAmount"].ToString())); //ParseDecimalForString(it["PaidAmount"].ToString()).ToString("0.##"));
dataRow.CreateCell(11).SetCellValue(Convert.ToDouble(it["UnpaidAmount"].ToString())); //ParseDecimalForString(it["UnpaidAmount"].ToString()).ToString("0.##"));
TotalEarnPaid += Convert.ToDecimal(it["ODARAmount"].ToString());
TotalPaid += Convert.ToDecimal(it["PaidAmount"].ToString());
TotalUnpaid += Convert.ToDecimal(it["UnpaidAmount"].ToString());
dataRow = null;
rowIndex++;
}
sheet.AddMergedRegion(new NPOI.SS.Util.Region(rowIndex, 0, rowIndex, 8));
HSSFRow row = (HSSFRow)sheet.CreateRow(rowIndex);
HSSFCell cell = (HSSFCell)row.CreateCell(0);
cell.SetCellValue("合计");
row.CreateCell(9).SetCellValue(Convert.ToDouble(TotalEarnPaid));
row.CreateCell(10).SetCellValue(Convert.ToDouble(TotalPaid));
row.CreateCell(11).SetCellValue(Convert.ToDouble(TotalUnpaid));
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
headerRow = null;
sheet = null;
workbook = null;
if (!Directory.Exists(localpath))
{
Directory.CreateDirectory(localpath);
}
FileStream fs = new FileStream(localpath + pathName, FileMode.OpenOrCreate);
BinaryWriter w = new BinaryWriter(fs);
w.Write(ms.ToArray());
fs.Close();
ms.Close();
ms.Dispose();
}
catch (Exception ex)
{
flag = "2";
collection.Add(new JsonStringValue("ex", ex.ToString()));
}
collection.Add(new JsonStringValue("type", "excel"));
collection.Add(new JsonStringValue("flag", flag));
collection.Add(new JsonStringValue("path", pathName));
return(collection.ToString());
}
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 CellRangeAddress(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);
}
}
/// <summary>
/// 导出方法实现
/// </summary>
/// <param name="list"></param>
private static void Export(IList <NPOIModel> list)
{
#region 变量声明
// 初始化
_workbook = new HSSFWorkbook();
// 声明 Row 对象
IRow _row;
// 声明 Cell 对象
ICell _cell;
// 总列数
int cols = 0;
// 总行数
int rows = 0;
// 行数计数器
int rowIndex = 0;
// 单元格值
string drValue = null;
#endregion
foreach (NPOIModel model in list)
{
// 工作薄命名
if (model.sheetName != null)
{
_sheet = (HSSFSheet)_workbook.CreateSheet(model.sheetName);
}
else
{
_sheet = (HSSFSheet)_workbook.CreateSheet();
}
// 获取数据源
DataTable dt = model.dataSource;
// 初始化
rowIndex = 0;
// 获取总行数
rows = GetRowCount(model.headerName);
// 获取总列数
cols = GetColCount(model.headerName);
// 循环行数
foreach (DataRow row in dt.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
_sheet = (HSSFSheet)_workbook.CreateSheet();
}
// 构建行
for (int i = 0; i < rows + model.isTitle; i++)
{
_row = _sheet.GetRow(i);
// 创建行
if (_row == null)
{
_row = _sheet.CreateRow(i);
}
for (int j = 0; j < cols; j++)
{
_row.CreateCell(j).CellStyle = bodyStyle;
}
}
// 如果存在表标题
if (model.isTitle > 0)
{
// 获取行
_row = _sheet.GetRow(0);
// 合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, (cols - 1));
_sheet.AddMergedRegion(region);
// 填充值
_row.CreateCell(0).SetCellValue(model.tableTitle);
// 设置样式
_row.GetCell(0).CellStyle = titleStyle;
// 设置行高
_row.HeightInPoints = 20;
}
// 取得上一个实体
NPOIHeader lastRow = null;
IList <NPOIHeader> hList = GetHeaders(model.headerName, rows, model.isTitle);
// 创建表头
foreach (NPOIHeader m in hList)
{
var data = hList.Where(c => c.firstRow == m.firstRow && c.lastCol == m.firstCol - 1);
if (data.Count() > 0)
{
lastRow = data.First();
if (m.headerName == lastRow.headerName)
{
m.firstCol = lastRow.firstCol;
}
}
// 获取行
_row = _sheet.GetRow(m.firstRow);
// 合并单元格
CellRangeAddress region = new CellRangeAddress(m.firstRow, m.lastRow, m.firstCol, m.lastCol);
_sheet.AddMergedRegion(region);
// 填充值
_row.CreateCell(m.firstCol).SetCellValue(m.headerName);
}
// 填充表头样式
for (int i = 0; i < rows + model.isTitle; i++)
{
_row = _sheet.GetRow(i);
for (int j = 0; j < cols; j++)
{
_row.GetCell(j).CellStyle = bodyStyle;
//设置列宽
_sheet.SetColumnWidth(j, (model.colWidths[j] + 1) * 256);
}
}
rowIndex = (rows + model.isTitle);
}
#endregion
#region 填充内容
// 构建列
_row = _sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{
// 添加序号列
if (1 == model.isOrderby && column.Ordinal == 0)
{
_cell = _row.CreateCell(0);
_cell.SetCellValue(rowIndex - rows);
_cell.CellStyle = bodyStyle;
}
// 创建列
_cell = _row.CreateCell(column.Ordinal + model.isOrderby);
// 获取值
drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
_cell.SetCellValue(drValue);
_cell.CellStyle = bodyStyle;
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
_cell.SetCellValue(dateV);
_cell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
_cell.SetCellValue(boolV);
_cell.CellStyle = bodyStyle;
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
_cell.SetCellValue(intV);
_cell.CellStyle = bodyRightStyle;
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
_cell.SetCellValue(doubV);
_cell.CellStyle = bodyRightStyle;
break;
case "System.DBNull": //空值处理
_cell.SetCellValue("");
break;
default:
_cell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
}
}
MemoryStream Export(bool isPrivate)
{
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workBook.CreateSheet();
#region 右击文件属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "DuGu";
workBook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "DuGu";
si.ApplicationName = "DuGu_NPOI_" + AppInfo.GetName();
si.LastAuthor = "DuGu";
si.Comments = "DuGu";
si.Title = "DuGu";
si.Subject = "DemarcateResults";
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");
int[] arrColWidth = { 20, 20, 20, 20, 20, 20, 20, 20 };
#region 表头样式
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workBook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workBook.CreateFont();
font.FontHeightInPoints = 25;
font.Boldweight = 400;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, ColumnCount - 1));
#endregion
headerRow = (HSSFRow)sheet.CreateRow(1);
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
font.FontHeightInPoints = 25;
font.Boldweight = 400;
headStyle.SetFont(font);
ExportOptions exportOptions = new ExportOptions();
for (int i = 0; i < ColumnCount; ++i)
{
headerRow.CreateCell(i).SetCellValue(exportOptions.TableString[i]);
headerRow.GetCell(i).CellStyle = headStyle;
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
for (int i = 0; i < 12; ++i)
{
headerRow = (HSSFRow)sheet.CreateRow(i + 2);
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
font.FontHeightInPoints = 15;
font.Boldweight = 400;
headStyle.SetFont(font);
headerRow.CreateCell(0).SetCellValue(i + 1);
headerRow.GetCell(0).CellStyle = headStyle;
if (demaMotionArray != null)
{
for (int j = 1; j < ColumnCount; ++j)
{
headerRow.CreateCell(j).SetCellValue(demaMotionArray[i].Para[j - 1].ToString());
headerRow.GetCell(j).CellStyle = headStyle;
sheet.SetColumnWidth(j, (arrColWidth[j] + 1) * 256);
}
}
if (demaMotionList != null)
{
for (int j = 1; j < ColumnCount; ++j)
{
headerRow.CreateCell(j).SetCellValue(demaMotionList[i].Para[j - 1].ToString());
headerRow.GetCell(j).CellStyle = headStyle;
sheet.SetColumnWidth(j, (arrColWidth[j] + 1) * 256);
}
}
}
using (MemoryStream ms = new MemoryStream())
{
workBook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/*****************************************/
/// <summary>
/// 外观检查月报Excel
/// </summary>
/// <param name="ds"></param>
/// <param name="ReportHeader"></param>
/// <returns></returns>
public static MemoryStream ExprotXlsByYear(DataSet ds, string ReportHeader = "")
{
//创建Excel文件的对象
HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
HSSFSheet sheet1 = (HSSFSheet)book.CreateSheet(ReportHeader);
//设置列宽度
for (int i = 0; i < ds.Tables[0].Columns.Count + 19; i++)
{
if ((i > -1 && i < 3) || (i > 4 && i < 8))//第一、二列
{
sheet1.SetColumnWidth(i, 11 * 250);
}
else
{
sheet1.SetColumnWidth(i, 8 * 256);
}
}
//设置全局行高
sheet1.DefaultRowHeightInPoints = 14;
//字体
IFont fontkh = book.CreateFont(); //创建一个字体样式
fontkh.FontHeightInPoints = 12; //字体大小
fontkh.FontName = "宋体"; //字体名
//添加考核标题样式
//单元格样式
ICellStyle stylekh = book.CreateCellStyle();
stylekh.Alignment = HorizontalAlignment.Left; //左对齐
stylekh.WrapText = true; //自动换行
stylekh.FillForegroundColor = HSSFColor.PaleBlue.Index; //前景色
stylekh.FillPattern = FillPattern.SolidForeground; //填充样式
stylekh.VerticalAlignment = VerticalAlignment.Center; //上下居中
stylekh.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh.SetFont(fontkh);
ICellStyle stylekh1 = book.CreateCellStyle();
stylekh1.Alignment = HorizontalAlignment.Left; //左对齐
stylekh1.WrapText = true; //自动换行
stylekh1.FillForegroundColor = HSSFColor.LightCornflowerBlue.Index; //前景色
stylekh1.FillPattern = FillPattern.SolidForeground; //填充样式
stylekh1.VerticalAlignment = VerticalAlignment.Center; //上下居中
stylekh1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh1.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh1.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh1.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh1.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh1.SetFont(fontkh);
//第一行合并单元格
CellRangeAddress firstcellRange = new CellRangeAddress(0, 1, 0, ds.Tables[0].Columns.Count + 18);
sheet1.AddMergedRegion(firstcellRange);
//显示数据字体
IFont fontdata1 = book.CreateFont(); //创建一个字体样式
fontdata1.FontHeightInPoints = 11; //字体大小
fontdata1.Color = (short)ConsoleColor.Green;
fontdata1.FontName = "宋体"; //字体名
ICellStyle styledata1 = book.CreateCellStyle(); //创建显示数据格式
styledata1.Alignment = HorizontalAlignment.Center; //左对齐
styledata1.WrapText = true;
styledata1.VerticalAlignment = VerticalAlignment.Center; //上下居中
styledata1.SetFont(fontdata1);
//显示数据字体
IFont fontdata = book.CreateFont(); //创建一个字体样式
fontdata.FontHeightInPoints = 11; //字体大小
fontdata.FontName = "宋体"; //字体名
ICellStyle styledata = book.CreateCellStyle(); //创建显示数据格式
styledata.WrapText = true;
styledata.VerticalAlignment = VerticalAlignment.Center; //上下居中
styledata.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
styledata.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
styledata.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
styledata.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
styledata.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
styledata.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
styledata.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
styledata.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
styledata.SetFont(fontdata);
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.WrapText = true;
IFont font = book.CreateFont();
font.FontHeightInPoints = 20;//字体大小
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontName = "宋体";
style.SetFont(font);
HSSFRow row = (HSSFRow)sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue("外观检查不良清单");
row.GetCell(0).CellStyle = style;
//给sheet1添加第一行的头部标题
HSSFRow row1 = (HSSFRow)sheet1.CreateRow(2);
row1.HeightInPoints = 40;
string mColumnName = "";//列标题的名称
string[] strs = new string[]
{ "料伤", "夹伤", "划伤", "打伤", "磨伤", "轴承不过", "倒角不良",
"抛光变形", "扁错位", "刀痕", "孔径不良", "尾刺", "粗糙度",
"电镀不良", "生锈", "漏工程", "料头", "跳动不良", "长度不良", "加工错误", "其他" };//数组存储不良类型，用来构造列头
for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
{
//标题
mColumnName = ds.Tables[0].Columns[k].ColumnName.ToString();
if (k > ds.Tables[0].Columns.Count - 3)
{
//此处是不良类型的标题，隐去了不良类型与不良数,利用生产的数组代替
if (k == (ds.Tables[0].Columns.Count - 1))
{
for (int i = 0; i < strs.Length; i++)
{
row1.CreateCell(k - 2 + i).SetCellValue(strs[i]);
row1.GetCell(k - 2 + i).CellStyle = stylekh1;
}
}
}
else
{
if (k == 8)
{
row1.CreateCell(strs.Length + 8).SetCellValue(mColumnName);
row1.GetCell(strs.Length + 8).CellStyle = stylekh1;
}
else
{
row1.CreateCell(k).SetCellValue(mColumnName);
row1.GetCell(k).CellStyle = stylekh;
}
}
}
int SumCheck = 0;
int okCheck = 0;
int NgCheck = 0;
int asd;
Dictionary <int, int> dic = new Dictionary <int, int>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
HSSFRow rowtemp1 = (HSSFRow)sheet1.CreateRow(i + 3);
for (int j = 0; j < ds.Tables[0].Columns.Count - 1; j++)
{
if (j > ds.Tables[0].Columns.Count - 4 && ((ds.Tables[0].Rows[i][ds.Tables[0].Columns.Count - 1]) == null ? false : true))
{
if (j == 8)
{
rowtemp1.CreateCell(strs.Length + j).SetCellValue(ds.Tables[0].Rows[i][j].ToString());
}
if (j == 9)
{
string[] types = ds.Tables[0].Rows[i][ds.Tables[0].Columns.Count - 2].ToString().Split(',');
string[] cts = ds.Tables[0].Rows[i][ds.Tables[0].Columns.Count - 1].ToString().Split(',');
if (types.Length > 1 && cts.Length > 1)
{
for (int cs = 0; cs < cts.Length; cs++)
{
if (dic.Where(u => u.Key.Equals(j + int.Parse(types[cs]) - 2)).Count() > 0)
{
int a = int.Parse(cts[cs]) + dic[j + int.Parse(types[cs]) - 2];
dic.Remove(j + int.Parse(types[cs]) - 2);
dic.Add(j + int.Parse(types[cs]) - 2, a);
}
else
{
dic.Add(j + int.Parse(types[cs]) - 2, int.Parse(cts[cs]));
}
rowtemp1.CreateCell(j + int.Parse(types[cs]) - 2).SetCellValue(int.Parse(cts[cs]));
}
}
}
}
else
{
int.TryParse(ds.Tables[0].Rows[i][j].ToString(), out asd);
if (asd > 0 || j == 7)
{
if (j == 5)
{
SumCheck += int.Parse(ds.Tables[0].Rows[i][j].ToString());
}
if (j == 6)
{
okCheck += int.Parse(ds.Tables[0].Rows[i][j].ToString());
}
if (j == 7)
{
NgCheck += int.Parse(ds.Tables[0].Rows[i][j].ToString());
}
rowtemp1.CreateCell(j).SetCellValue(int.Parse(ds.Tables[0].Rows[i][j].ToString()));
}
else
{
if (j == 0)
{
rowtemp1.CreateCell(j).SetCellValue(DateTime.Parse(ds.Tables[0].Rows[i][j].ToString()).ToString("yyyy/MM/dd"));
}
else
{
rowtemp1.CreateCell(j).SetCellValue(ds.Tables[0].Rows[i][j].ToString());
}
}
}
}
}
HSSFRow rowtemp2 = (HSSFRow)sheet1.CreateRow(ds.Tables[0].Rows.Count + 3);
rowtemp2.CreateCell(4).SetCellValue("合计:");
rowtemp2.GetCell(4).CellStyle = styledata;
rowtemp2.CreateCell(5).SetCellValue(SumCheck);
rowtemp2.GetCell(5).CellStyle = styledata;
rowtemp2.CreateCell(6).SetCellValue(okCheck);
rowtemp2.GetCell(6).CellStyle = styledata;
rowtemp2.CreateCell(7).SetCellValue(NgCheck);
rowtemp2.GetCell(7).CellStyle = styledata;
foreach (var item in dic)
{
rowtemp2.CreateCell(item.Key).SetCellValue(item.Value);
rowtemp2.GetCell(item.Key).CellStyle = styledata;
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
/// <summary>
/// 外观检查日报Excel
/// </summary>
/// <param name="ds"></param>
/// <param name="ReportHeader"></param>
/// <returns></returns>
public static MemoryStream ExportXlsByList(DataSet ds, string ReportHeader = "", string userName = "")
{
//创建Excel文件的对象
HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
HSSFSheet sheet1 = (HSSFSheet)book.CreateSheet(ReportHeader);
sheet1.DefaultRowHeight = 150 * 4;//设置全局行高
//设置列宽度
for (int i = 0; i < ds.Tables[0].Columns.Count + 19; i++)
{
if (i == 1 || i == 0 || i == 28)//第一、二列
{
sheet1.SetColumnWidth(i, 19 * 250);
}
else if (i > 1 && i < 7)
{
sheet1.SetColumnWidth(i, 10 * 256);
}
else
{
sheet1.SetColumnWidth(i, 6 * 256);
}
}
//字体
IFont fontkh = book.CreateFont(); //创建一个字体样式
fontkh.FontHeightInPoints = 12; //字体大小
fontkh.FontName = "宋体"; //字体名
//显示数据字体
IFont fontdata = book.CreateFont(); //创建一个字体样式
fontdata.FontHeightInPoints = 11; //字体大小
fontdata.FontName = "宋体"; //字体名
//显示数据字体
IFont fontdata1 = book.CreateFont(); //创建一个字体样式
fontdata1.FontHeightInPoints = 11; //字体大小
fontdata1.Color = (short)ConsoleColor.Green;
fontdata1.FontName = "宋体"; //字体名
//字符样式
HSSFFont row2stylefont = (HSSFFont)book.CreateFont();
row2stylefont.FontName = "宋体";
row2stylefont.FontHeightInPoints = 25;
row2stylefont.IsBold = true;
//显示数据格式
ICellStyle styledata = book.CreateCellStyle();//创建显示数据格式
styledata.WrapText = true;
styledata.VerticalAlignment = VerticalAlignment.Center; //上下居中
styledata.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
styledata.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
styledata.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
styledata.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
styledata.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
styledata.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
styledata.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
styledata.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
styledata.SetFont(fontdata);
ICellStyle styledata1 = book.CreateCellStyle(); //创建显示数据格式
styledata1.Alignment = HorizontalAlignment.Center; //左对齐
styledata1.WrapText = true;
styledata1.VerticalAlignment = VerticalAlignment.Center; //上下居中
styledata1.SetFont(fontdata1);
//添加考核标题样式
//单元格样式
ICellStyle stylekh = book.CreateCellStyle();
stylekh.Alignment = HorizontalAlignment.Left; //左对齐
stylekh.WrapText = true; //自动换行
stylekh.FillForegroundColor = HSSFColor.PaleBlue.Index; //前景色
stylekh.FillPattern = FillPattern.SolidForeground; //填充样式
stylekh.VerticalAlignment = VerticalAlignment.Center; //上下居中
stylekh.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh.SetFont(fontkh);
ICellStyle stylekh1 = book.CreateCellStyle();
stylekh1.Alignment = HorizontalAlignment.Left; //左对齐
stylekh1.WrapText = true; //自动换行
stylekh1.FillForegroundColor = HSSFColor.LightCornflowerBlue.Index; //前景色
stylekh1.FillPattern = FillPattern.SolidForeground; //填充样式
stylekh1.VerticalAlignment = VerticalAlignment.Center; //上下居中
stylekh1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh1.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh1.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh1.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh1.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh1.SetFont(fontkh);
//第一行单元格样式 外观检测日报
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.WrapText = true;
IFont font = book.CreateFont();
font.FontHeightInPoints = 20;//字体大小
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontName = "宋体";
style.SetFont(font);
//给第一行填数据
HSSFRow row2 = (HSSFRow)sheet1.CreateRow(0);
row2.CreateCell(0).SetCellValue("外观检查日报");
row2.GetCell(0).CellStyle = style;
//显示考核信息
HSSFRow row3 = (HSSFRow)sheet1.CreateRow(2);
row3.CreateCell(0).SetCellValue("考核信息");
row3.GetCell(0).CellStyle = stylekh;
//第一行设置合并单元格
//参数分别为 自左到右 开始行索引,结束行索引,开始列索引,结束列索引
CellRangeAddress firstcellRange = new CellRangeAddress(0, 1, 0, ds.Tables[0].Columns.Count / 3);
CellRangeAddress nextcellRange = new CellRangeAddress(0, 1, ds.Tables[0].Columns.Count / 3, ds.Tables[0].Columns.Count + 10);
sheet1.AddMergedRegion(firstcellRange);
sheet1.AddMergedRegion(nextcellRange);
//空出一行
CellRangeAddress khInfo1 = new CellRangeAddress(2, 2, 1, ds.Tables[0].Columns.Count + 10);//显示"考核信息"
sheet1.AddMergedRegion(khInfo1);
CellRangeAddress kb = new CellRangeAddress(5, 5, 0, ds.Tables[0].Columns.Count + 10);//第5行空白
sheet1.AddMergedRegion(kb);
//备注合并单元格
CellRangeAddress Remark = new CellRangeAddress(3, 3, 7, 13); //合并第三行第8列到第9列
CellRangeAddress Remark1 = new CellRangeAddress(4, 4, 7, 13); //合并第四行第8列到第9列
sheet1.AddMergedRegion(Remark);
sheet1.AddMergedRegion(Remark1);
//给sheet1添加第一行的头部标题
HSSFRow row1 = (HSSFRow)sheet1.CreateRow(3);
HSSFRow tworow = (HSSFRow)sheet1.CreateRow(6); //第7行
int twonum = 0; //列标题的索引号
string mColumnName = ""; //列标题的名称
string[] strs = new string[]
{ "料伤", "夹伤", "划伤", "打伤", "磨伤", "轴承不过", "倒角不良",
"抛光变形", "扁错位", "刀痕", "孔径不良", "尾刺", "粗糙度",
"电镀不良", "生锈", "漏工程", "料头", "跳动不良", "长度不良", "加工错误", "其他" };//数组存储不良类型，用来构造列头
//列标题
for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
{
//考核信息标题
mColumnName = ds.Tables[0].Columns[k].ColumnName.ToString();
if (k < 8)
{
row1.CreateCell(k).SetCellValue(mColumnName);
row1.GetCell(k).CellStyle = stylekh;
}
else
{
if (k > ds.Tables[0].Columns.Count - 4)
{
if (k == 15)
{
tworow.CreateCell(strs.Length + 7).SetCellValue(mColumnName);
tworow.GetCell(strs.Length + 7).CellStyle = stylekh1;
}
//此处是不良类型的标题，隐去了不良类型与不良数,利用生产的数组代替
if (k == (ds.Tables[0].Columns.Count - 1))
{
for (int i = 0; i < strs.Length; i++)
{
tworow.CreateCell(twonum).SetCellValue(strs[i]);
tworow.GetCell(twonum).CellStyle = stylekh1;
twonum++;
}
}
}
else
{
tworow.CreateCell(twonum).SetCellValue(k == 11 ? mColumnName + "/H" : mColumnName); //因为数据库里部能包好标准时/H 在此重构一下
tworow.GetCell(twonum).CellStyle = k == 15 ? stylekh1 : stylekh;
twonum++;
}
}
}
////显示数据 按行填充 行数从0开始
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(4);//第5行
ICellStyle makeStyle = book.CreateCellStyle();
makeStyle.Alignment = HorizontalAlignment.Left;
makeStyle.VerticalAlignment = VerticalAlignment.Center;
IFont makeFont = book.CreateFont();
makeFont.FontName = "宋体";
makeFont.FontHeightInPoints = 16;
makeStyle.SetFont(makeFont);
row1.CreateCell(15).SetCellValue("作成: " + userName + " 时间：" + DateTime.Now.ToString());
row1.GetCell(15).CellStyle = makeStyle;
rowtemp.CreateCell(15).SetCellValue("说明:达成率=(标准工时+其他工时)/出勤工时*100% 标准时间=∑(检查数量/标准时间)");
//考核信息部分 j为行索引 这里只遍历前八个
for (int j = 0; j < 8; j++)
{
if (j == 0)
{ //第一个日期转化格式
rowtemp.CreateCell(j).SetCellValue(DateTime.Parse(ds.Tables[0].Rows[0][j].ToString()).ToString("yyyy年MM月dd日"));
}
else if (j > 1 && j < 7)
{
if (j == 6)
{
//达成率加百分比
rowtemp.CreateCell(j).SetCellValue(ds.Tables[0].Rows[0][j].ToString() + "%");
}
else
{
//工号、出勤时间等已双精度输出
rowtemp.CreateCell(j).SetCellValue(double.Parse(ds.Tables[0].Rows[0][j].ToString()));
}
}
else
{
//备注姓名等
rowtemp.CreateCell(j).SetCellValue(ds.Tables[0].Rows[0][j].ToString());
}
//样式填充
if (j != 7)
{
rowtemp.GetCell(j).CellStyle = styledata;
}
}
int SumCheck = 0;
int okCheck = 0;
int NgCheck = 0;
int asd;
//第7行开始遍历导出数据
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
HSSFRow rowtemp1 = (HSSFRow)sheet1.CreateRow(i + 7);
for (int j = 7; j < ds.Tables[0].Columns.Count - 1; j++)
{
if (j > ds.Tables[0].Columns.Count - 4 && ((ds.Tables[0].Rows[i][ds.Tables[0].Columns.Count - 1]) == null ? false : true))
{
string[] types = ds.Tables[0].Rows[i][ds.Tables[0].Columns.Count - 2].ToString().Split(',');
string[] cts = ds.Tables[0].Rows[i][ds.Tables[0].Columns.Count - 1].ToString().Split(',');
if (types.Length > 1 && cts.Length > 1)
{
for (int cs = 0; cs < cts.Length; cs++)
{
rowtemp1.CreateCell(j - 8 + int.Parse(types[cs])).SetCellValue(int.Parse(cts[cs]));
rowtemp1.GetCell(j - 8 + int.Parse(types[cs])).CellStyle = styledata1;
}
}
}
else
{
int.TryParse(ds.Tables[0].Rows[i][j].ToString(), out asd);
if (asd > 0 || j == 14)
{
if (j == 12)
{
SumCheck += int.Parse(ds.Tables[0].Rows[i][j].ToString());
}
if (j == 13)
{
okCheck += int.Parse(ds.Tables[0].Rows[i][j].ToString());
}
if (j == 14)
{
NgCheck += int.Parse(ds.Tables[0].Rows[i][j].ToString());
}
rowtemp1.CreateCell(j - 8).SetCellValue(int.Parse(ds.Tables[0].Rows[i][j].ToString()));
rowtemp1.GetCell(j - 8).CellStyle = styledata;
}
else if (j == 7)
{
rowtemp1.CreateCell(strs.Length + j).SetCellValue(ds.Tables[0].Rows[i][j].ToString());
}
else
{
rowtemp1.CreateCell(j - 8).SetCellValue(ds.Tables[0].Rows[i][j].ToString());
rowtemp1.GetCell(j - 8).CellStyle = styledata;
}
}
}
}
HSSFRow rowtemp2 = (HSSFRow)sheet1.CreateRow(ds.Tables[0].Rows.Count + 7);
rowtemp2.CreateCell(3).SetCellValue("total:");
rowtemp2.GetCell(3).CellStyle = styledata;
rowtemp2.CreateCell(4).SetCellValue(SumCheck);
rowtemp2.GetCell(4).CellStyle = styledata;
rowtemp2.CreateCell(5).SetCellValue(okCheck);
rowtemp2.GetCell(5).CellStyle = styledata;
rowtemp2.CreateCell(6).SetCellValue(NgCheck);
rowtemp2.GetCell(6).CellStyle = styledata;
//写入流
System.IO.MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
/// <summary>
/// 实现导出功能
/// </summary>
public void Export( )
{
#region 变量声明
string [] tableTitle = this._header.Split(new char [] { ';' }, StringSplitOptions.RemoveEmptyEntries);
// 表头数组
string [] newHeaders = null;
// 数据字段
string [] files = this._filed.Split(new char [] { ';' }, StringSplitOptions.RemoveEmptyEntries);
// 数据字段
string [] file = null;
// 临时数组
string [] temp = null;
// 临时表头
string tempHeader = string.Empty;
// 工作薄名称
string [] sheetNames = this._sheetName.Split(new string [] { ";" }, StringSplitOptions.RemoveEmptyEntries);
// 表头名称
string [] titles = this._title == null ? null : this._title.Split(new string [] { ";" }, StringSplitOptions.RemoveEmptyEntries);
// 获取行数
int rows = GetRowCount(this._header);
// 列数计数器
int cols = 0;
// 列头跨行数
int rowSpans = 0;
// 列头跨列数
int colSpans = GetColCount(this._header);
// HSSFSheet 对象
HSSFSheet sheet = null;
// IRow 对象
IRow row = null;
// 表头行添加
int trow = (string.IsNullOrEmpty(this._title) ? 0 : 1);
DataTable dt;
#endregion
#region 单元格样式
ICellStyle style = _workbook.CreateCellStyle( );
style.Alignment = HorizontalAlignment.Center; //居中
style.VerticalAlignment = VerticalAlignment.Center; //垂直居中
style.WrapText = true; //自动换行
// 边框
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
// 字体
IFont font = _workbook.CreateFont( );
font.FontHeightInPoints = 10;
font.FontName = "宋体";
style.SetFont(font);
ICellStyle titleType = _workbook.CreateCellStyle( );
titleType.Alignment = HorizontalAlignment.Center; //居中
titleType.VerticalAlignment = VerticalAlignment.Center; //垂直居中
titleType.WrapText = true; //自动换行
// 边框
titleType.BorderBottom = BorderStyle.Thin;
titleType.BorderLeft = BorderStyle.Thin;
titleType.BorderRight = BorderStyle.Thin;
titleType.BorderTop = BorderStyle.Thin;
IFont font2 = _workbook.CreateFont( );
font2.FontHeightInPoints = 14;
font2.FontName = "宋体";
font2.Boldweight = ( short )FontBoldWeight.Bold;
titleType.SetFont(font2);
#endregion
// 表格绘制
for (int k = 0; k < sheetNames.Length; k++)
{
#region 表头绘制
newHeaders = tableTitle [k].Split(new char [] { '#' }, StringSplitOptions.RemoveEmptyEntries);
sheet = ( HSSFSheet )_workbook.CreateSheet(sheetNames [k]);
for (int m = 0; m < rows + trow; m++) // 创建行
{
if (m == 0 && trow > 0)
{
row = sheet.CreateRow(0);
CellRangeAddress region = new CellRangeAddress(0, 0, 0, colSpans - 1);
sheet.AddMergedRegion(region);
row.CreateCell(0).SetCellValue(titles [k]);
row.GetCell(0).CellStyle = titleType;
row.Height = 20 * 20;
continue;
}
cols = 0;
for (int i = 0; i < newHeaders.Length; i++) // 创建列
{
tempHeader = newHeaders [i];
// 获取列头跨行数
rowSpans = GetRowSpan(tempHeader, rows);
// 获取列头跨列数
colSpans = GetColSpan(tempHeader);
// 如果表头还可以划分
temp = tempHeader.Split(new char [] { ' ' });
if (temp.Length == rows)
{
tempHeader = temp [m - trow];
}
else
{
tempHeader = temp [0];
}
if (1 == rowSpans)
{
// 获取行
row = sheet.GetRow(m);
if (row == null)
{
row = sheet.CreateRow(m);
}
// 未跨列
if (1 == colSpans)
{
row.CreateCell(cols).SetCellValue(tempHeader);
row.GetCell(cols).CellStyle = style;
}
else // 跨列
{
temp = tempHeader.Split(new char [] { ',' });
if (temp.Length > 1)
{
for (int j = 0; j < temp.Length; j++)
{
row.CreateCell(j + cols).SetCellValue(temp [j]);
row.GetCell(j + cols).CellStyle = style;
}
}
else
{
// 创建范围
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
CellRangeAddress region = new CellRangeAddress(m, m, cols, cols + colSpans - 1);
sheet.AddMergedRegion(region);
row.CreateCell(cols).SetCellValue(tempHeader);
row.GetCell(cols).CellStyle = style;
}
cols += colSpans - 1;
}
}
else if (rowSpans > 1 && m < 2)
{
// 获取行
row = sheet.GetRow(m);
if (row == null)
{
row = sheet.CreateRow(m);
}
// 未跨列
if (1 == colSpans)
{
// 创建范围
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
CellRangeAddress region = new CellRangeAddress(m, rowSpans - 1 + trow, cols, cols);
sheet.AddMergedRegion(region);
row.CreateCell(cols).SetCellValue(tempHeader);
row.GetCell(cols).CellStyle = style;
}
else
{
// 创建范围
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
CellRangeAddress region = new CellRangeAddress(m, rowSpans - 1 + trow, cols, cols + colSpans - 1);
sheet.AddMergedRegion(region);
row.CreateCell(cols).SetCellValue(tempHeader);
row.GetCell(cols).CellStyle = style;
cols += colSpans - 1;
}
}
// 列计数器
cols += 1;
}
}
#endregion
#region 数据源
int rowIndex = rows + trow;
foreach (DataRow dr in this._dsSource.Tables[k].Rows)
{
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in this._dsSource.Tables[k].Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
string drValue = dr [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;
}
}
rowIndex++;
}
#endregion
}
#region 数据导出
// WEB导出
//if ( 1 == this . _exportMode )
//{
// System . Web . HttpContext . Current . Response . ContentType = "application/vnd.ms-excel";
// //设置下载的Excel文件名
// System . Web . HttpContext . Current . Response . AddHeader ( "Content-Disposition" ,string . Format ( "attachment;filename={0}" ,this . _fileName ) );
// using ( MemoryStream ms = new MemoryStream ( ) )
// {
// //将工作簿的内容放到内存流中
// _workbook . Write ( ms );
// //将内存流转换成字节数组发送到客户端
// System . Web . HttpContext . Current . Response . BinaryWrite ( ms . GetBuffer ( ) );
// System . Web . HttpContext . Current . Response . End ( );
// _workbook = null;
// }
//}
//else
if (2 == this._exportMode)
{
using (FileStream fs = File.Open(this._filePath, FileMode.Append))
{
_workbook.Write(fs);
_workbook = null;
}
}
#endregion
}
/// <summary>DataTable导出到Excel的MemoryStream</summary>
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText, string[] str)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
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 == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontName = str[5]; //[5]
font.FontHeightInPoints = Convert.ToInt16(str[0]); //[0]
font.Boldweight = Convert.ToInt16(str[1]); //[1]
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) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontName = str[6]; //[6]
font.FontHeightInPoints = Convert.ToInt16(str[2]); //[2]
font.Boldweight = Convert.ToInt16(str[3]); //[3]
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) * Convert.ToInt32(str[4])); //[4]
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#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": //字符串类型
string result = drValue;
newCell.SetCellValue(result);
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);
ms.Flush();
ms.Position = 0;
//sheet;
//workbook.Dispose();
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);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <Author> 2010-5-8 22:21:41</Author>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, Dictionary <string, string> columnNames, Dictionary <string, string> dataformats)
{
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 = "kakake"; //填加xls文件作者信息
si.ApplicationName = ""; //填加xls文件创建程序信息
si.LastAuthor = ""; //填加xls文件最后保存者信息
si.Comments = "说明信息"; //填加xls文件作者信息
si.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");
HSSFCellStyle customStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat customformat = (HSSFDataFormat)workbook.CreateDataFormat();
//取得列宽
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;
int index = 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 = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
if (columnNames.Count > 0)
{
sheet.AddMergedRegion(new Region(0, 0, 0, columnNames.Count - 1));
}
else
{
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 = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
index = 0;
foreach (DataColumn column in dtSource.Columns)
{
if (columnNames.Count > 0)
{
if (columnNames.ContainsKey(column.ColumnName))
{
headerRow.CreateCell(index).SetCellValue(columnNames[column.ColumnName]);
headerRow.GetCell(index).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(index, (Encoding.GetEncoding(936).GetBytes(columnNames[column.ColumnName]).Length + 1) * 256);
index++;
}
}
else
{
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 填充内容
index = 0;
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
if (columnNames.Count > 0)
{
if (columnNames.ContainsKey(column.ColumnName))
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(index);
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);
//HSSFCellStyle celldoubleStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//int pos = Convert.ToString(doubV).Length - Convert.ToString(doubV).IndexOf('.') - 1;
//if (pos == 4)
//{
// celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0000");
//}
//else if (pos == 2)
//{
// celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
//}
//newCell.CellStyle = celldoubleStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
if (dataformats.ContainsKey(column.ColumnName))
{
customStyle.DataFormat = customformat.GetFormat(dataformats[column.ColumnName]);
newCell.CellStyle = customStyle;
}
index++;
}
}
else
{
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": //日期类型
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);
//HSSFCellStyle celldoubleStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//int pos = Convert.ToString(doubV).Length - Convert.ToString(doubV).IndexOf('.') - 1;
//if (pos == 4)
//{
// celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0000");
//}
//else if (pos == 2)
//{
// celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
//}
//newCell.CellStyle = celldoubleStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
if (dataformats.ContainsKey(column.ColumnName))
{
customStyle.DataFormat = customformat.GetFormat(dataformats[column.ColumnName]);
newCell.CellStyle = customStyle;
}
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Workbook.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放 sheet
return(ms);
}
}
