public void TestRepeatingRowsAndColumsNames()
{
// First Test that Setting RR&C for same sheet more than once only Creates a
// single Print_Titles built-in record
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("FirstSheet");
// set repeating rows and columns twice for the first sheet
for (int i = 0; i < 2; i++)
{
wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3 - 1);
sheet.CreateFreezePane(0, 3);
}
Assert.AreEqual(1, wb.NumberOfNames);
IName nr1 = wb.GetNameAt(0);
Assert.AreEqual("Print_Titles", nr1.NameName);
if (false)
{
#if !HIDE_UNREACHABLE_CODE
// TODO - full column references not rendering properly, absolute markers not present either
Assert.AreEqual("FirstSheet!$A:$A,FirstSheet!$1:$3", nr1.RefersToFormula);
#endif
}
else
{
Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);
}
// Save and re-open
HSSFWorkbook nwb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(1, nwb.NumberOfNames);
nr1 = nwb.GetNameAt(0);
Assert.AreEqual("Print_Titles", nr1.NameName);
Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);
// check that Setting RR&C on a second sheet causes a new Print_Titles built-in
// name to be Created
sheet = (HSSFSheet)nwb.CreateSheet("SecondSheet");
nwb.SetRepeatingRowsAndColumns(1, 1, 2, 0, 0);
Assert.AreEqual(2, nwb.NumberOfNames);
IName nr2 = nwb.GetNameAt(1);
Assert.AreEqual("Print_Titles", nr2.NameName);
Assert.AreEqual("SecondSheet!B:C,SecondSheet!$A$1:$IV$1", nr2.RefersToFormula);
//if (false) {
// // In case you fancy Checking in excel, to ensure it
// // won't complain about the file now
// File tempFile = File.CreateTempFile("POI-45126-", ".xls");
// FileOutputStream fout = new FileOutputStream(tempFile);
// nwb.Write(fout);
// fout.close();
// Console.WriteLine("check out " + tempFile.GetAbsolutePath());
//}
}
public static HSSFDataFormat CreateTop(HSSFWorkbook hssfworkbookDown, HSSFSheet sheet, string [] pwe, string state)
{
HSSFDataFormat format = (HSSFDataFormat)hssfworkbookDown.CreateDataFormat();
ICellStyle table0 = TopRow(hssfworkbookDown);
{ ///开始创建
HSSFRow toprow = (HSSFRow)sheet.CreateRow(0);
toprow.Height = 2 * 256; //行高
for (int i = 0; i < pwe.Length; i++)
{
toprow.CreateCell(i).SetCellValue(pwe[i]);
if (i == 2 || i == 5 || i == 6 || i == 7 || i == 10)
{
sheet.SetColumnWidth(i, 18 * 256); //第一列宽
}
else if (i == 1 || i == 4) //宽点
{
sheet.SetColumnWidth(i, 36 * 256); //第一列宽
}
else if (i == 3)
{
sheet.SetColumnWidth(i, 54 * 256);//第一列宽
}
else if (i == 8 || i == 9)
{
if (i == 8)///项目完成度
{
if (state == "0")
{
sheet.SetColumnWidth(i, 18 * 256);
}
else
{
sheet.SetColumnWidth(i, 36 * 256);//第一列宽
}
//第一列宽
}
else if (i == 9)
{
if (state == "1")
{
sheet.SetColumnWidth(i, 18 * 256);//第一列宽
}
else if (state == "2")
{
sheet.SetColumnWidth(i, 36 * 256);//第一列宽
}
}
}
toprow.GetCell(i).CellStyle = table0;
}
}
sheet.CreateFreezePane(1, 1, 1, 1);//锁定单元格
return(format);
}
//第一种情况，预先不知道多少行，但是表格格式统一，表格中没有特殊单元格，比如合并啊，居中啥的，这种情况边框放到最后加
//DataGridView是winform中类型表格的控件
public void ConvertDGVToSheet(DataGridView dv, string sheetName)
{
HSSFSheet sheet = CreateSheet(sheetName);
//冻结标题行
sheet.CreateFreezePane(0, 1, 0, 1);
//写标题
IRow colHeader = sheet.CreateRow(0);
for (int i = 0; i < dv.ColumnCount; i++)
{
if (dv.Columns[i].Visible)
{
colHeader.CreateCell(i).SetCellValue(dv.Columns[i].HeaderText);
}
}
//写内容
//int tmp = 1;
for (int j = 0; j < dv.Rows.Count; j++)
{
IRow row = sheet.CreateRow(j + 1);
for (int k = 0; k < dv.Columns.Count; k++)
{
if (dv.Columns[k].Visible)
{
row.CreateCell(k).SetCellValue(dv.Rows[j].Cells[k].FormattedValue.ToString());
}
}
}
//自动调整列间距
for (int i = 0; i < dv.ColumnCount; i++)
{
sheet.AutoSizeColumn(i);
}
SetCellBorder(sheet, NPOI.SS.UserModel.BorderStyle.Dashed, NPOI.SS.UserModel.BorderStyle.Thin);
}
public void TestRepeatingRowsAndColumsNames()
{
// First Test that Setting RR&C for same sheet more than once only Creates a
// single Print_Titles built-in record
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("FirstSheet");
// set repeating rows and columns twice for the first sheet
CellRangeAddress cra = CellRangeAddress.ValueOf("A1:A3");
for (int i = 0; i < 2; i++)
{
sheet.RepeatingColumns = (cra);
sheet.RepeatingRows = (cra);
sheet.CreateFreezePane(0, 3);
}
Assert.AreEqual(1, wb.NumberOfNames);
IName nr1 = wb.GetNameAt(0);
Assert.AreEqual("Print_Titles", nr1.NameName);
// TODO - full column references not rendering properly, absolute markers not present either
// assertEquals("FirstSheet!$A:$A,FirstSheet!$1:$3", nr1.getRefersToFormula());
Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);
// Save and re-open
HSSFWorkbook nwb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(1, nwb.NumberOfNames);
nr1 = nwb.GetNameAt(0);
Assert.AreEqual("Print_Titles", nr1.NameName);
Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);
// check that Setting RR&C on a second sheet causes a new Print_Titles built-in
// name to be Created
sheet = (HSSFSheet)nwb.CreateSheet("SecondSheet");
cra = CellRangeAddress.ValueOf("B1:C1");
sheet.RepeatingColumns = (cra);
sheet.RepeatingRows = (cra);
Assert.AreEqual(2, nwb.NumberOfNames);
IName nr2 = nwb.GetNameAt(1);
Assert.AreEqual("Print_Titles", nr2.NameName);
Assert.AreEqual("SecondSheet!B:C,SecondSheet!$A$1:$IV$1", nr2.RefersToFormula);
}
public IRow AgregaEncabezados(List <String> listValues, float tamanioFuente)
{
currentRow++;
currentColumn = -1;
HSSFRow newRow = (HSSFRow)currentSheet.CreateRow(currentRow);
foreach (var val in listValues)
{
var cell = AgregarCelda(val, tamanioFuente, true);
}
currentSheet.CreateFreezePane(0, 1);
currentSheet.SetAutoFilter(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, listValues.Count - 1));
return(newRow);
}
/// <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);
}
}
/// <summary>
/// 创建Sheet第一行(Excel表头)
/// </summary>
/// <param name="workbook">要创建表头的工作簿</param>
/// <param name="sheet">要创建表头的Sheet</param>
/// <param name="DataTable">要导出的DataTable</param>
private static void CreateHeader(HSSFWorkbook workbook, HSSFSheet sheet, DataTable dt)
{
int cellIndex = 0;
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.IsLocked = true;
headStyle.SetFont(font);
//取得列宽
int[] arrayColumnWidth = new int[ListColumnsName.Count];
int index = 0;
foreach (System.Collections.DictionaryEntry de in ListColumnsName)
{
arrayColumnWidth[index] = Encoding.GetEncoding(936).GetBytes(de.Value.ToString()).Length;
index++;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < ListColumnsName.Count; j++)
{
//列名称
string columnName = ListColumnsName.GetKey(j).ToString();
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][columnName].ToString()).Length;
if (intTemp > arrayColumnWidth[j])
{
arrayColumnWidth[j] = intTemp;
}
}
}
//循环导出列
foreach (System.Collections.DictionaryEntry de in ListColumnsName)
{
headerRow.CreateCell(cellIndex).SetCellValue(de.Value.ToString());
headerRow.GetCell(cellIndex).CellStyle = headStyle;
//int maxColumnWidth = 255*256;// 1/256为一个字符宽度单位
int maxColumnWidth = 80 * 256; //设置80个字符列宽为最大
int setColumn = (arrayColumnWidth[cellIndex] + 1) * 256; //要设置的列宽
if (setColumn >= maxColumnWidth)
{
setColumn = maxColumnWidth;
}
//设置列宽
sheet.SetColumnWidth(cellIndex, setColumn);
cellIndex++;
}
sheet.CreateFreezePane(0, 1, 0, ListColumnsName.Count - 1);
}
public void SaveAsXls(string xlsFilepath)
{
HSSFWorkbook workbook = new HSSFWorkbook();
//create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Hever Translations";
workbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "Statistics - auto generated file";
workbook.SummaryInformation = si;
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("statistics");
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("Text");
headerRow.CreateCell(1).SetCellValue("Words in Text");
headerRow.CreateCell(2).SetCellValue("Instances of Text");
//HSSFFont headerFont = workbook.CreateFont();
NPOI.SS.UserModel.IFont headerFont = workbook.CreateFont();
headerFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
headerRow.Cells[0].CellStyle = workbook.CreateCellStyle();
headerRow.Cells[0].CellStyle.SetFont(headerFont);
headerRow.Cells[1].CellStyle = workbook.CreateCellStyle();
headerRow.Cells[1].CellStyle.SetFont(headerFont);
headerRow.Cells[2].CellStyle = workbook.CreateCellStyle();
headerRow.Cells[2].CellStyle.SetFont(headerFont);
int totalWordsInText = 0;
int totalInstances = 0;
int rowNumber = 0;
foreach (KeyValuePair <string, int> kvp in m_elementsCount)
{
string text = kvp.Key;
int count = kvp.Value;
int wordsCount = m_elementsWordCount[kvp.Key];
totalWordsInText += wordsCount;
totalInstances += count;
rowNumber++;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowNumber);
row.CreateCell(0).SetCellValue(text);
row.CreateCell(1).SetCellValue(wordsCount);
row.CreateCell(2).SetCellValue(count);
}
rowNumber++;
rowNumber++;
sheet.CreateRow(rowNumber).CreateCell(0).SetCellValue("Total words: " + totalWordsInText.ToString());
rowNumber++;
sheet.CreateRow(rowNumber).CreateCell(0).SetCellValue("Total instances: " + totalInstances.ToString());
sheet.CreateFreezePane(0, 1, 0, 1);
FileStream file = new FileStream(xlsFilepath, FileMode.Create);
workbook.Write(file);
file.Close();
}
/// <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() as HSSFSheet;
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "utry.cn";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "utry"; //填加xls文件作者信息
si.ApplicationName = "ots"; //填加xls文件创建程序信息
si.LastAuthor = "utry"; //填加xls文件最后保存者信息
si.Comments = "说明信息"; //填加xls文件作者信息
si.Title = "ots-excel"; //填加xls文件标题信息
si.Subject = "ots-excel"; //填加文件主题信息
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;
#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 = 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 CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.IsLocked = true;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
int cwidth = (arrColWidth[column.Ordinal] + 1);
if (cwidth >= 255)
{
cwidth = 255;
}
int width = cwidth * 256;
sheet.SetColumnWidth(column.Ordinal, width);
}
sheet.CreateFreezePane(0, 2, 0, dtSource.Columns.Count - 1);
}
#endregion
rowIndex = 2;
}
#endregion
foreach (DataRow row in dtSource.Rows)
{
#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++;
}
//统计功能
int count = dtSource.Rows.Count;//总共条数
HSSFRow bottomRow = sheet.CreateRow(count + 2) as HSSFRow;
HSSFCell bottomCell1 = (HSSFCell)bottomRow.CreateCell(0);
HSSFCell bottomCell2 = (HSSFCell)bottomRow.CreateCell(1);
bottomCell1.SetCellValue("合计：");
bottomCell2.SetCellValue(count + "条");
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// 導出Excel文件
/// </summary>
/// <param name="dateTable"></param>
/// <param name="width">寬度以,分隔 "20,10,50"</param>
/// <returns></returns>
public static HSSFWorkbook ToExcel(DataTable dateTable, string width = "0")
{
try
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Sheel"); //创建工作表
sheet.CreateFreezePane(0, 1); //冻结列头行
HSSFRow row_Title = (HSSFRow)sheet.CreateRow(0); //创建列头行
row_Title.HeightInPoints = 19.5F; //设置列头行高
#region 设置列宽
if (width == "0")//默認列寬
{
for (int i = 0; i < dateTable.Columns.Count; i++)
{
sheet.SetColumnWidth(i, 20 * 256);
}
}
else
{
string[] Uwidth = width.Split(',');
if (Uwidth.Count() == dateTable.Columns.Count)//設置的列寬
{
for (int i = 0; i < dateTable.Columns.Count; i++)
{
sheet.SetColumnWidth(i, int.Parse(Uwidth[i]) * 256);
}
}
else//默認列寬
{
for (int i = 0; i < dateTable.Columns.Count; i++)
{
if (i < Uwidth.Count())
{
sheet.SetColumnWidth(i, int.Parse(Uwidth[i]) * 256);
}
else
{
sheet.SetColumnWidth(i, int.Parse(Uwidth[Uwidth.Count() - 1]) * 256);
}
}
}
}
#endregion
#region 设置列头单元格样式
HSSFCellStyle cs_Title = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
HSSFFont cs_Title_Font = (HSSFFont)wb.CreateFont(); //创建字体
//cs_Title_Font.IsBold = true; //字体加粗
cs_Title_Font.FontHeightInPoints = 12; //字体大小
cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式
#endregion
#region 生成列头
for (int i = 0; i < dateTable.Columns.Count; i++)
{
HSSFCell cell_Title = (HSSFCell)row_Title.CreateCell(i); //创建单元格
cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
cell_Title.SetCellValue(dateTable.Columns[i].ToString());
}
#endregion
int k = 0;
foreach (DataRow dr in dateTable.Rows)
{
#region 设置内容单元格样式
HSSFCellStyle cs_Content = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
cs_Content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
cs_Content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
#endregion
#region 生成内容单元格
HSSFRow row_Content = (HSSFRow)sheet.CreateRow(k + 1); //创建行
k++;
row_Content.HeightInPoints = 16;
for (int j = 0; j < 13; j++)
{
HSSFCell cell_Conent = (HSSFCell)row_Content.CreateCell(j); //创建单元格
cell_Conent.CellStyle = cs_Content;
cell_Conent.SetCellValue(dr[j].ToString());
}
#endregion
}
return(wb);
}
catch
{
}
return(null);
}
/// <summary>
/// 生成模板
/// </summary>
/// <param name="displayName">文件名</param>
/// <returns>生成的模版文件</returns>
public byte[] GenerateTemplate(out string displayName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
InitExcelData();
CreateDataTable(); //add by dufei
SetTemplateDataValus(); //add by dufei
if (!string.IsNullOrEmpty(FileDisplayName))
{
displayName = FileDisplayName + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls";
}
else
{
displayName = this.GetType().Name + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls";
}
//模板sheet页
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
workbook.SetSheetName(0, string.IsNullOrEmpty(FileDisplayName) ? this.GetType().Name : FileDisplayName);
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
row.HeightInPoints = 20;
HSSFSheet enumSheet = (HSSFSheet)workbook.CreateSheet();
HSSFRow enumSheetRow1 = (HSSFRow)enumSheet.CreateRow(0);
enumSheetRow1.CreateCell(0).SetCellValue(Program._localizer?["Yes"]);
enumSheetRow1.CreateCell(1).SetCellValue(Program._localizer?["No"]);
enumSheetRow1.CreateCell(2).SetCellValue(this.GetType().Name); //为模板添加标记,必要时可添加版本号
HSSFSheet dataSheet = (HSSFSheet)workbook.CreateSheet();
#region 设置excel模板列头
//默认灰色
var headerStyle = GetCellStyle(workbook);
headerStyle.IsLocked = true;
//黄色
var yellowStyle = GetCellStyle(workbook, BackgroudColorEnum.Yellow);
yellowStyle.IsLocked = true;
//红色
var redStyle = GetCellStyle(workbook, BackgroudColorEnum.Red);
redStyle.IsLocked = true;
//取得所有ExcelPropety
var propetys = this.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList();
int _currentColunmIndex = 0;
bool IsProtect = false;
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
ExcelPropety excelPropety = (ExcelPropety)propetys[porpetyIndex].GetValue(this);
ColumnDataType dateType = excelPropety.DataType;
if (excelPropety.ReadOnly)
{
IsProtect = true;
}
//给必填项加星号
string colName = excelPropety.IsNullAble ? excelPropety.ColumnName : excelPropety.ColumnName + "*";
row.CreateCell(_currentColunmIndex).SetCellValue(colName);
//修改列头样式
switch (excelPropety.BackgroudColor)
{
case BackgroudColorEnum.Yellow:
row.Cells[_currentColunmIndex].CellStyle = yellowStyle;
break;
case BackgroudColorEnum.Red:
row.Cells[_currentColunmIndex].CellStyle = redStyle;
break;
default:
row.Cells[_currentColunmIndex].CellStyle = headerStyle;
break;
}
var dataStyle = workbook.CreateCellStyle();
var dataFormat = workbook.CreateDataFormat();
if (dateType == ColumnDataType.Dynamic)
{
int dynamicColCount = excelPropety.DynamicColumns.Count();
for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++)
{
var dynamicCol = excelPropety.DynamicColumns.ToList()[dynamicColIndex];
string dynamicColName = excelPropety.IsNullAble ? dynamicCol.ColumnName : dynamicCol.ColumnName + "*";
row.CreateCell(_currentColunmIndex).SetCellValue(dynamicColName);
row.Cells[_currentColunmIndex].CellStyle = headerStyle;
if (dynamicCol.ReadOnly)
{
IsProtect = true;
}
//设定列宽
if (excelPropety.CharCount > 0)
{
sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
dataStyle.WrapText = true;
}
else
{
sheet.AutoSizeColumn(_currentColunmIndex);
}
//设置单元格样式及数据类型
dataStyle.IsLocked = excelPropety.ReadOnly;
dynamicCol.SetColumnFormat(dynamicCol.DataType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
_currentColunmIndex++;
}
}
else
{
//设定列宽
if (excelPropety.CharCount > 0)
{
sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
dataStyle.WrapText = true;
}
else
{
sheet.AutoSizeColumn(_currentColunmIndex);
}
//设置是否锁定
dataStyle.IsLocked = excelPropety.ReadOnly;
//设置单元格样式及数据类型
excelPropety.SetColumnFormat(dateType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
_currentColunmIndex++;
}
}
#endregion
#region 添加模版数据 add by dufei
if (TemplateDataTable.Rows.Count > 0)
{
for (int i = 0; i < TemplateDataTable.Rows.Count; i++)
{
DataRow tableRow = TemplateDataTable.Rows[i];
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(1 + i);
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
string colName = propetys[porpetyIndex].Name;
tableRow[colName].ToString();
dataRow.CreateCell(porpetyIndex).SetCellValue(tableRow[colName].ToString());
}
}
}
#endregion
//冻结行
sheet.CreateFreezePane(0, 1, 0, 1);
//锁定excel
if (IsProtect)
{
sheet.ProtectSheet("password");
}
workbook.SetSheetHidden(1, true);
workbook.SetSheetHidden(2, true);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms.ToArray());
}
/// <summary>
/// list转Excel
/// </summary>
/// <typeparam name="T">传入list 类型 </typeparam>
/// <param name="SheetName">sheet名称</param>
/// <param name="list">list集合</param>
/// <param name="FiedNames">名称序列</param>
/// <returns></returns>
public static HSSFWorkbook ListtoExcel <T>(string SheetName, List <T> list, Dictionary <string, string> FiedNames)
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(SheetName); //创建工作表
sheet.CreateFreezePane(0, 1); //冻结列头行
HSSFRow row_Title = (HSSFRow)sheet.CreateRow(0); //创建列头行
row_Title.HeightInPoints = 30.5F; //设置列头行高
HSSFCellStyle cs_Title = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
HSSFFont cs_Title_Font = (HSSFFont)wb.CreateFont(); //创建字体
cs_Title_Font.IsBold = true; //字体加粗
cs_Title_Font.FontHeightInPoints = 14; //字体大小
cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式
#region 生成列头
int ii = 0;
foreach (string key in FiedNames.Keys)
{
HSSFCell cell_Title = (HSSFCell)row_Title.CreateCell(ii); //创建单元格
cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
cell_Title.SetCellValue(key);
sheet.SetColumnWidth(ii, 25 * 256); //设置列宽
ii++;
}
#endregion
//获取 实体类 类型对象
Type t = typeof(T); // model.GetType();
//获取 实体类 所有的 公有属性
List <PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
//创建 实体属性 字典集合
Dictionary <string, PropertyInfo> dictPros = new Dictionary <string, PropertyInfo>();
//将 实体属性 中要修改的属性名 添加到 字典集合中 键：属性名 值：属性对象
proInfos.ForEach(p =>
{
if (FiedNames.Values.Contains(p.Name))
{
dictPros.Add(p.Name, p);
}
});
HSSFCellStyle cs_Content = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
cs_Content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
cs_Content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
for (int i = 0; i < list.Count; i++)
{
HSSFRow row_Content = (HSSFRow)sheet.CreateRow(i + 1); //创建行
row_Content.HeightInPoints = 20;
int jj = 0;
foreach (string proName in FiedNames.Values)
{
if (dictPros.ContainsKey(proName))
{
HSSFCell cell_Conent = (HSSFCell)row_Content.CreateCell(jj); //创建单元格
cell_Conent.CellStyle = cs_Content;
//如果存在，则取出要属性对象
PropertyInfo proInfo = dictPros[proName];
//获取对应属性的值
object value = proInfo.GetValue(list[i], null); //object newValue = model.uName;
string cell_value = value == null ? "" : value.ToString();
cell_Conent.SetCellValue(cell_value);
jj++;
}
}
}
return(wb);
}
private void WriteXls()
{
int g = 0;
int h = 0;
int i = 0;
int j = 0;
CheckCount[0] = 0;
CheckCount[1] = 0;
////需要使用的變數。Workbook表示Excel檔，Worksheet表示一個Excel檔裡面的sheet(一個Excel檔可以有很多sheet)，Range表示Excel裡面單元格的範圍。
//Microsoft.Office.Interop.Excel.Application xlApp = null;
//Workbook wb = null;
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet ws = (HSSFSheet)wb.CreateSheet(DateTime.Today.ToString("yyyyMMdd"));
//Worksheet ws = null;
//Range aRange = null;
//Range aRange2 = null;
/* object mObj_opt = System.Reflection.Missing.Value;
* //啟動Excel應用程式
* xlApp = new Microsoft.Office.Interop.Excel.Application();
* xlApp.Visible = false;
* if (xlApp == null)
* {
* Console.WriteLine("Error! xlApp");
* return;
* }
* //用Excel應用程式建立一個Excel物件，也就是Workbook。並取得Workbook中的第一個sheet。這就是我們要操作資料的地方。
* wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
* ws = (Worksheet)wb.Worksheets[1];
* if (ws == null)
* {
* Console.WriteLine("Error! ws");
* }*/
//利用Cells屬性，取得單一儲存格，並進行操作。
//ws.Name = DateTime.Today.ToString("yyyyMMdd");
string[] header = { "版本", "個案", "檔案名稱", "修改時間", "落差值(分)", "落差值(天)" };
ws.CreateRow(0);
for (int ki = 0; ki < header.Length; ki++)
{
ws.GetRow(0).CreateCell(ki).SetCellValue(header[ki]);
}
//ws.get_Range("A1", "F1").Value2 = header;
//ListFile.Clear();
//pb_main.Maximum = List_ver.Count;
//pb_main.Value = 0;
int colorcount = 0;
//string ra0 = "A2";
int ra0 = 1;
int ra1 = 0;
int last = 0;
for (int mi = 0; mi < List_ver.Count; mi++)
{
GetAllDirList(basicpath + List_ver[mi] + @"\FOR客製客戶");
//最後，呼叫SaveAs function儲存這個Excel物件到硬碟。
/* for (int i = 0; i < ListFile.Count;i++ )
* {
* ws.Cells[2+i, 1] = ListFile[i];
* }*/
//版本
//個案
//pb_file.Maximum = ListDir.Count;
//pb_file.Value = 0;
int index = 0;
foreach (KeyValuePair <string, List <string> > item in ListDir)
{
if (item.Value.Count > 0)
{
for (int p = 0; p < item.Value.Count; p++)
{
ws.CreateRow(1 + h);
//ws.Cells[2 + h, 2] = item.Key;
//ws.CreateRow(2 + h).CreateCell(2).SetCellValue(item.Key);
ws.GetRow(1 + h).CreateCell(1).SetCellValue(item.Key);
//ws.Cells[2 + h, 1] = List_ver[mi];
//ws.CreateRow(2 + h).CreateCell(1).SetCellValue(List_ver[mi]);
ws.GetRow(1 + h).CreateCell(0).SetCellValue(List_ver[mi]);
h++;
}
}
}
//修改時間
foreach (KeyValuePair <string, List <DateTime> > item in ListDate)
{
if (item.Value.Count > 0)
{
DateTime dt = item.Value[0];
for (int p = 0; p < item.Value.Count; p++)
{
//ws.Cells[2 + j, 4] = item.Value[p];
//ws.CreateRow(2 + j).CreateCell(4).SetCellValue(item.Value[p]);
DateTime dt1 = item.Value[p];
ws.GetRow(1 + j).CreateCell(3).SetCellValue(dt1.ToString());
TimeSpan Total = item.Value[p].Subtract(dt); //日期相減
//Cell cell = ws.GetRow(2 + j).GetCell(2 + j);
//ws.Cells[2 + j, 5] = Math.Round(Total.TotalMinutes,3).ToString();
int seconds = Math.Abs(Int32.Parse(Math.Round(Total.TotalSeconds, 0).ToString()));
int hours = Convert.ToInt32((seconds / 3600) - (seconds % 3600) / 3600);
seconds -= hours * 3600;
int minutes = Convert.ToInt32((seconds / 60) - (seconds % 60) / 60);;
seconds -= minutes * 60;
//ws.Cells[2 + j, 5] = string.Format("{0:00}", hours) + ":" + string.Format("{0:00}", minutes) + ":" + string.Format("{0:00}", seconds);
//ws.GetRow(2 + j).CreateCell(5).SetCellValue(string.Format("{0:00}", hours) + ":" + string.Format("{0:00}", minutes) + ":" + string.Format("{0:00}", seconds));
string edittime = string.Format("{0:00}", hours) + ":" + string.Format("{0:00}", minutes) + ":" + string.Format("{0:00}", seconds);
ws.GetRow(1 + j).CreateCell(4).SetCellValue(edittime);
//ws.Cells[2 + j, 6] = Convert.ToInt32((float.Parse(Total.TotalDays.ToString())));
//ws.GetRow(2 + j).CreateCell(6).SetCellValue(Convert.ToInt32((float.Parse(Total.TotalDays.ToString()))));
ws.GetRow(1 + j).CreateCell(5).SetCellValue(Convert.ToInt32((float.Parse(Total.TotalDays.ToString()))));
Cell cell = ws.GetRow(1 + j).GetCell(4);
if (float.Parse(Total.TotalMinutes.ToString()) > Int32.Parse(min))
{
//string palce = "E" + (2 + j).ToString();
//font_color(wb, ws.GetRow(2 + j).GetCell(2 + j),NPOI.HSSF.Util.HSSFColor.BLUE.index2);
cells.Add(cell, NPOI.HSSF.Util.HSSFColor.BLUE.index);
//font_color(wb, ws.GetRow(1 + j).GetCell(4), NPOI.HSSF.Util.HSSFColor.BLUE.index);
//Range ras1 = ws.get_Range(palce, palce);
//ras1.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(0, 0, 255));
CheckCount[0]++;
//System.Runtime.InteropServices.Marshal.ReleaseComObject(ras1);
//ras1 = null;
}
else if ((float.Parse(Total.TotalMinutes.ToString()) < (-1) * Int32.Parse(min)))
{
//string palce = "E" + (2 + j).ToString();
//fill_background(wb, cell, NPOI.HSSF.Util.HSSFColor.RED.index);
//font_color(wb, ws.GetRow(2 + j).GetCell(2 + j),NPOI.HSSF.Util.HSSFColor.RED.index);
cells.Add(cell, NPOI.HSSF.Util.HSSFColor.RED.index);
//font_color(wb, ws.GetRow(1 + j).GetCell(4), NPOI.HSSF.Util.HSSFColor.RED.index);
//Range ras2 = ws.get_Range(palce, palce);
//ras2.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(255, 0, 0));
CheckCount[0]++;
// System.Runtime.InteropServices.Marshal.ReleaseComObject(ras2);
// ras2 = null;
}
cell = ws.GetRow(1 + j).GetCell(5);
if (Convert.ToInt32(float.Parse(Total.TotalDays.ToString())) >= 1)
{
//string palce = "F" + (2 + j).ToString();
//Range ras3 = ws.get_Range(palce, palce);
//font_color(wb,ws.GetRow(2 + j).GetCell(2 + j), NPOI.HSSF.Util.HSSFColor.BLUE.index2);
cells.Add(cell, NPOI.HSSF.Util.HSSFColor.BLUE.index2);
//font_color(wb, ws.GetRow(1 + j).GetCell(4), NPOI.HSSF.Util.HSSFColor.BLUE.index2);
//ras3.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(0, 0, 255));
CheckCount[1]++;
//System.Runtime.InteropServices.Marshal.ReleaseComObject(ras3);
//ras3 = null;
}
else if ((Convert.ToInt32(float.Parse(Total.TotalDays.ToString())) <= -1))
{
//string palce = "F" + (2 + j).ToString();
//Range ras4 = ws.get_Range(palce, palce);
//fill_background(wb, cell, NPOI.HSSF.Util.HSSFColor.RED.index);
//font_color(wb,ws.GetRow(2 + j).GetCell(2 + j), NPOI.HSSF.Util.HSSFColor.RED.index);
cells.Add(cell, NPOI.HSSF.Util.HSSFColor.RED.index);
//font_color(wb, ws.GetRow(1 + j).GetCell(4), NPOI.HSSF.Util.HSSFColor.RED.index);
//ras4.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(255, 0, 0));
CheckCount[1]++;
//System.Runtime.InteropServices.Marshal.ReleaseComObject(ras4);
//ras4 = null;
}
j++;
}
}
}
//檔案
foreach (KeyValuePair <string, List <string> > item in ListDir)
{
if (item.Value.Count > 0)
{
for (int p = 0; p < item.Value.Count; p++)
{
//ws.Cells[2 + i, 3] = item.Value[p];
//ws.GetRow(2 + i).CreateCell(3).SetCellValue(item.Value[p]);
ws.GetRow(1 + i).CreateCell(2).SetCellValue(item.Value[p]);
i++;
//Updateinfo(List_ver[mi] + ".." + item.Key + ".." + item.Value[p]);
lb_info.Invoke(Setinfo, new object[] { List_ver[mi] + ".." + item.Key + ".." + item.Value[p] });
//UpdateCpuProgress(pb_file);
}
index++;
pb_file.Invoke(SetPro, new object[] { pb_file, ListDir.Count, index, lb_file });
Thread.CurrentThread.Join(100);
}
}
pb_main.Invoke(SetPro, new object[] { pb_main, List_ver.Count, mi + 1, lb_main });
//Thread.CurrentThread.Join(1);
if (mi < List_ver.Count)
{
ra1 = 1 + i;
/* ra1 = "F" + (1 + i).ToString();
* Range rax = ws.get_Range(ra0, ra1);*/
if (colorcount % 2 == 0)
{
//rax.Interior.Color =
//System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName("AliceBlue")); //背景顏色
for (int r1 = 0; r1 < 6; r1++)
{
for (int a2 = last; a2 < ra1; a2++)
{
fill_background(wb, ws.GetRow(a2).GetCell(r1), NPOI.HSSF.Util.HSSFColor.SKY_BLUE.index);
}
}
}
else
{
for (int r1 = 0; r1 < 6; r1++)
{
for (int a2 = last; a2 < ra1; a2++)
{
fill_background(wb, ws.GetRow(a2).GetCell(r1), NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index);
}
}
//rax.Interior.Color =
//System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName("Honeydew")); //背景顏色
//ra0 = "A" + (2 + i).ToString();
//ra0 = 1 + i;
}
last = ra1;
colorcount++;
}
}
//UpdateCpuProgress(pb_main);
//string ra_row = "A1";
int ra_row = 1;
//string ra_col = "F" + (1 + j).ToString();
int ra_col = 1 + j;
//Range ra = ws.get_Range(ra_row, ra_col);
//自動調整蘭寬
//ra.Columns.AutoFit();
for (ra_col = 0; ra_col < 6; ra_col++)
{
ws.AutoSizeColumn(ra_col);
}
//ra = ws.get_Range("A1", "F1");
//ra.Interior.Color =
//System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(192,192,255)); //背景顏色
for (int ai = 0; ai < 6; ai++)
{
fill_background(wb, ws.GetRow(0).GetCell(ai), NPOI.HSSF.Util.HSSFColor.ORANGE.index);
}
//置中
//ra.HorizontalAlignment = XlVAlign.xlVAlignCenter;
//ra.VerticalAlignment = XlVAlign.xlVAlignCenter;
/*
* Process[] runningProcs = Process.GetProcessesByName("excel");
* foreach (Process p in runningProcs)
* {
* p.Close();
* }*/
//string row = "A2";
int row = 2;
//string col = "F" + (1 + j).ToString();
int col = 1 + j;
//ra = ws.get_Range(row, col);
//ra.Select();
//xlApp.ActiveWindow.FreezePanes = true;
ws.CreateFreezePane(1, 0, 1, 0);
if (!isDirectory(Mydocument + @"\ModiCheck"))
{
Directory.CreateDirectory(Mydocument + @"\ModiCheck");
}
if (!isDirectory(Mydocument + @"\ModiCheck\" + DateTime.Now.ToString("yyyyMM")))
{
Directory.CreateDirectory(Mydocument + @"\ModiCheck\" + DateTime.Now.ToString("yyyyMM"));
}
if (!isDirectory(Mydocument + @"\ModiCheck\" + DateTime.Now.ToString("yyyyMM") + "\\" + DateTime.Now.ToString("yyyyMMdd")))
{
Directory.CreateDirectory(Mydocument + @"\ModiCheck\" + DateTime.Now.ToString("yyyyMM") + "\\" + DateTime.Now.ToString("yyyyMMdd"));
}
filename = DateTime.Now.ToString("yyyyMMdd_HHmmss");
//wb.SaveAs(Mydocument + @"\ModiCheck\" + DateTime.Now.ToString("yyyyMM") + "\\" + DateTime.Now.ToString("yyyyMMdd") + "\\" + filename + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, mObj_opt, mObj_opt, mObj_opt, mObj_opt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, mObj_opt, mObj_opt, mObj_opt, mObj_opt, mObj_opt);
savefilename = Mydocument + @"\ModiCheck\" + DateTime.Now.ToString("yyyyMM") + "\\" + DateTime.Now.ToString("yyyyMMdd") + "\\" + filename + ".xls";
WriteToFile(wb, savefilename);
/* Console.WriteLine("save");
* wb.Close(false, mObj_opt, mObj_opt);
* xlApp.Workbooks.Close();
* xlApp.Quit();
* //刪除 Windows工作管理員中的Excel.exe 進程，
* System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(ra);
* //System.Runtime.InteropServices.Marshal.ReleaseComObject(aRange);
* //System.Runtime.InteropServices.Marshal.ReleaseComObject(aRange2);
* xlApp = null;
* wb = null;
* ws = null;
* ra = null;*/
//aRange = null;
//aRange2 = null;
//呼叫垃圾回收
GC.Collect();
this.Invoke(closewin, new object[] { this });
}
public static void ExportExcel(DataGridView dgv, string fileName = "", string fontname = "微软雅黑", short fontsize = 10)
{
//检测是否有数据
if (dgv.RowCount <= 0)
{
return;
}
//创建主要对象
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Weight");
//设置字体，大小，对齐方式
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontName = fontname;
font.FontHeightInPoints = fontsize;
style.SetFont(font);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //居中对齐
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//首行填充黄色
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.FillForegroundColor = IndexedColors.Yellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.SetFont(font);
headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //居中对齐
headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//添加表头
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText);
dataRow.GetCell(i).CellStyle = headerStyle;
}
//注释的这行是设置筛选的
//sheet.SetAutoFilter(new CellRangeAddress(0, dgv.Columns.Count, 0, dgv.Columns.Count));
//添加列及内容
for (int i = 0; i < dgv.Rows.Count; i++)
{
sheet.AutoSizeColumn(i);//先来个常规自适应
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
string ValueType = dgv.Rows[i].Cells[j].Value.GetType().ToString();
string Value = dgv.Rows[i].Cells[j].Value.ToString();
switch (ValueType)
{
case "System.String": //字符串类型
dataRow.CreateCell(j).SetCellValue(Value);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(Value, out dateV);
dataRow.CreateCell(j).SetCellValue(dateV);
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(Value, out boolV);
dataRow.CreateCell(j).SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(Value, out intV);
dataRow.CreateCell(j).SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(Value, out doubV);
dataRow.CreateCell(j).SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
dataRow.CreateCell(j).SetCellValue("");
break;
default:
dataRow.CreateCell(j).SetCellValue("");
break;
}
dataRow.GetCell(j).CellStyle = style;
//设置宽度
sheet.SetColumnWidth(j, (Value.Length + 10) * 300);
//首行冻结
sheet.CreateFreezePane(dgv.Columns.Count, 1);
}
}
//保存文件
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
MemoryStream ms = new MemoryStream();
if (saveDialog.ShowDialog() == DialogResult.OK)
{
saveFileName = saveDialog.FileName;
if (!CheckFiles(saveFileName))
{
MessageBox.Show("文件占用，请关闭文件后再继续操作！ " + saveFileName);
workbook = null;
ms.Close();
ms.Dispose();
return;
}
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
MessageBox.Show("导出完成！", "", MessageBoxButtons.OK);
}
else
{
workbook = null;
ms.Close();
ms.Dispose();
}
}
private void ExportToExcel(DataTable table, string layerName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFFont font = workbook.CreateFont();
font.Color = HSSFColor.DARK_BLUE.index;
font.Boldweight = 2000;
HSSFCellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.SetFont(font);
HSSFCellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
HSSFSheet sheet = workbook.CreateSheet(layerName);
sheet.CreateFreezePane(0, 1, 0, 1);
// create the column headers
int r = 0;
HSSFRow xRow = sheet.CreateRow(r);
for (int c = 0; c <= table.Columns.Count - 1; ++c)
{
HSSFCell xCell = xRow.CreateCell(c);
xCell.SetCellValue(table.Columns[c].ColumnName);
xCell.CellStyle = headerStyle;
}
// convert feature attributes to cell values
foreach (DataRow row in table.Rows)
{
r += 1;
xRow = sheet.CreateRow(r);
for (int c = 0; c <= table.Columns.Count - 1; ++c)
{
HSSFCell xCell = xRow.CreateCell(c);
if (!row.IsNull(c))
{
switch (table.Columns[c].DataType.Name)
{
case "Byte":
case "Int16":
case "Int32":
case "Int64":
case "Single":
case "Double":
case "Decimal":
xCell.SetCellValue(Convert.ToDouble(row[c]));
break;
case "DateTime":
xCell.SetCellValue(Convert.ToDateTime(row[c]));
xCell.CellStyle = dateStyle;
break;
default:
xCell.SetCellValue(row[c].ToString());
break;
}
}
}
}
// return the Excel workbook
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-disposition", "attachment; filename=Export.xls");
workbook.Write(Response.OutputStream);
}
/// <summary>
/// 由DataSet导出Excel(带有格式)
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="sheetName">工作表名称</param>
/// <returns>Excel工作表</returns>
private static Stream ExportDataSetToFormatExcel(DataSet sourceDs, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
string[] sheetNames = sheetName.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); //分割符
for (int i = 0; i < sheetNames.Length; i++)
{
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i]);
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[sourceDs.Tables[i].Columns.Count];
foreach (DataColumn item in sourceDs.Tables[i].Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int k = 0; k < sourceDs.Tables[i].Rows.Count; k++)
{
for (int j = 0; j < sourceDs.Tables[i].Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(sourceDs.Tables[i].Rows[k][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
int sheetnum = 1;
foreach (DataRow row in sourceDs.Tables[i].Rows)
{
#region 创建表头
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheetnum++;
sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i] + "-" + sheetnum.ToString());
}
var headerRow = sheet.CreateRow(0);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//设置边框
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BottomBorderColor = HSSFColor.Black.Index;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.LeftBorderColor = HSSFColor.Green.Index;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.RightBorderColor = HSSFColor.Blue.Index;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.TopBorderColor = HSSFColor.Orange.Index;
//设置背景色
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index;
headStyle.FillPattern = NPOI.SS.UserModel.FillPattern.BigSpots;
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
rowIndex = 1;
}
#endregion
#region 创建内容
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
case "MySql.Data.Types.MySqlDateTime": //MySql类型
if (drValue == "0000/0/0 0:00:00" || String.IsNullOrEmpty(drValue))
{
//当时间为空，防止生成的execl 中是一串“#######”号，所有赋值为空字符串
newCell.SetCellValue("");
}
else
{
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++;
}
//设置首行首列冻结
//第一个参数表示要冻结的列数
//第二个参数表示要冻结的行数
//第三个参数表示右边区域可见的首列序号，从1开始计算
//第四个参数表示下边区域可见的首行序号，也是从1开始计算
sheet.CreateFreezePane(1, 1, 0, 10);
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
return(ms);
}
//冻结窗口
public static void freezePane(HSSFSheet sheet, int cloIndex, int rowIndex)
{
sheet.CreateFreezePane(cloIndex, rowIndex);
}
/// <summary>
/// 利用office组件读取Excel表格中配件信息
/// </summary>
/// <returns></returns>
//public static DataTable ImportExcelFile(string FilesPathName)
//{
// try
// {
// string ExcelFilePath = string.Empty;//存储Excel文件路径
// string ExcelConnStr = string.Empty;//读取Excel表格字符串
// DataTable ExcelTable = null;//获取Excel表格中的数据
// string SheetName = string.Empty;//Excel中表名
// string ExcelSql = string.Empty;//查询Excel表格
// ExcelFilePath = FilesPathName;//获取选定文件的路径名
// if (Path.GetExtension(ExcelFilePath) != ".xlsx" && Path.GetExtension(ExcelFilePath) != ".xls")
// {
// MessageBoxEx.Show("请您输入Excel格式的文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
// }
// else if (ExcelFilePath == string.Empty)
// {
// MessageBoxEx.Show("请您选择要导入的配件信息文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
// }
// else
// {
// //HDR=YES代表Excel第一行是标题不作为数据使用，IMEX三种模式：0代表写入模式，1代表读取模式，2代表链接模式（支持同时读取和写入）
// ExcelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
// OleDbConnection DbCon = new OleDbConnection(ExcelConnStr);//打开数据源连接
// DbCon.Open();
// ExcelTable = DbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });//获取Excel表格中的数据
// SheetName = ExcelTable.Rows[0]["table_name"].ToString();//获取Excel表名称
// ExcelSql = "select * from [" + SheetName + "]";
// OleDbDataAdapter DbAdapter = new OleDbDataAdapter(ExcelSql, DbCon);//执行数据查询
// DataSet ds = new DataSet();
// DbAdapter.Fill(ds, SheetName);//填充数据集
// DbCon.Close();//关闭数据源连接
// ExcelTable = ds.Tables[0];
// }
// return ExcelTable;
// }
// catch (Exception ex)
// {
// MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
// return null;
// }
//}
/// <summary>
/// 利用NPOI创建Excel文件
/// </summary>
/// <param name="ExcelTable">excel表格名称</param>
/// <param name="SheetName">sheet表名</param>
public static void NPOIExportExcelFile(DataTable NPOIExcelTable, string SheetName)
{
try
{
string SaveExcelName = string.Empty;//保存的Excel文件名称
SaveFileDialog SFDialog = new SaveFileDialog();
SFDialog.DefaultExt = "xls";
SFDialog.Filter = "Excel文件(*.xls)|*.xls";
SFDialog.ShowDialog();
SFDialog.CheckPathExists = true;
SFDialog.CheckFileExists = true;
SaveExcelName = SFDialog.FileName;//获取保存的Excel文件名称
if (File.Exists(SaveExcelName))
{
if (IsExcelOpen(SaveExcelName))
{
File.Delete(SaveExcelName); //替换同名文件
}
else
{
return;
}
}
if (SaveExcelName.IndexOf(":") < 0)
{
return; //点击取消按钮返回
}
HSSFWorkbook WkBk = new HSSFWorkbook(); //创建工作表流
MemoryStream ms = new MemoryStream(); //创建支持内存的存储区流
HSSFSheet sht = (HSSFSheet)WkBk.CreateSheet(SheetName); //创建Excel表格
sht.CreateFreezePane(0, 1, 0, 1); //设置冻结首行
HSSFRow HeaderRow = (HSSFRow)sht.CreateRow(0); //创建标题行
int TotalCount = NPOIExcelTable.Rows.Count;
int rowRead = 0; //读取行数
ProgressBarFrm ProgBarFrm = new ProgressBarFrm();
ProgBarFrm.MaxNum = TotalCount; //获取最大记录行
ProgBarFrm.ShowDialog(); //显示进度条
for (int i = 0; i < NPOIExcelTable.Columns.Count; i++)
{
HeaderRow.CreateCell(i).SetCellValue(NPOIExcelTable.Columns[i].ColumnName.ToString()); //创建标题列
SetXlsHeaderStyle(HeaderRow, WkBk, i); //设置标题样式
sht.SetColumnWidth(i, 1000 * 5); //设置列宽
}
//创建Excel数据行项
for (int j = 0; j < NPOIExcelTable.Rows.Count; j++)
{
HSSFRow DatasRow = (HSSFRow)sht.CreateRow(j + 1); //创建数据行
for (int k = 0; k < NPOIExcelTable.Columns.Count; k++)
{ //填充Excel表数据
DatasRow.CreateCell(k).SetCellValue(NPOIExcelTable.Rows[j][k].ToString());
}
rowRead++; //自动增长行号
ProgressBarFrm.CurrentValue = rowRead; //传递当前值
Application.DoEvents(); //处理当前在消息队列中所有windows消息
}
//写入内存流数据
WkBk.Write(ms);
ms.Flush();//清空缓存
ms.Position = 0;
sht = null;
HeaderRow = null;
WkBk = null;
FileStream fs = new FileStream(SaveExcelName, FileMode.CreateNew, FileAccess.Write); //创建文件流
byte[] dataXls = ms.ToArray(); //把文件流转换为字节数组
fs.Write(dataXls, 0, dataXls.Length);
fs.Flush(); //清除缓存
fs.Close(); //关闭文件流
dataXls = null;
ms = null;
fs = null;
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
/// <summary>
/// 设置冻结窗格
/// </summary>
/// <param name="colsplit">冻结列</param>
/// <param name="rowsplit">冻结行</param>
/// <param name="leftmostcolum">右侧首列序号</param>
/// <param name="toprow">下侧首列行号</param>
public void SetFreezePane(int colsplit, int rowsplit, int leftmostcolum, int toprow)
{
sheet.CreateFreezePane(colsplit, rowsplit, leftmostcolum, toprow);
}