/// <summary>
/// Gets the data writer engine.
/// </summary>
/// <param name="dataFormat">The data format.</param>
/// <param name="outStream">The out stream.</param>
/// <returns>The <see cref="IDataWriterEngine"/>.</returns>
/// <exception cref="System.ArgumentException">Both outStream and dataFormat,XmlWriter set. Please set only one of them.</exception>
public IDataWriterEngine GetDataWriterEngine(IDataFormat dataFormat, Stream outStream)
{
LazyDataFormat lazyDataFormat = dataFormat as LazyDataFormat;
if (lazyDataFormat != null)
{
IDataWriterEngine dataWriterEngine;
if (outStream != null && lazyDataFormat.XMLWriter != null)
{
throw new ArgumentException("Both outStream and dataFormat,XmlWriter set. Please set only one of them.");
}
switch (lazyDataFormat.SdmxDataFormat.BaseDataFormat.EnumType)
{
case BaseDataFormatEnumType.Generic:
dataWriterEngine = lazyDataFormat.XMLWriter != null
? new GenericDataWriterEngine(lazyDataFormat.XMLWriter, lazyDataFormat.SdmxDataFormat.SchemaVersion)
: new GenericDataWriterEngine(outStream, lazyDataFormat.SdmxDataFormat.SchemaVersion, lazyDataFormat.Encoding);
break;
case BaseDataFormatEnumType.Compact:
dataWriterEngine = lazyDataFormat.XMLWriter != null
? new CompactDataWriterEngine(lazyDataFormat.XMLWriter, lazyDataFormat.SdmxDataFormat.SchemaVersion)
: new CompactDataWriterEngine(outStream, lazyDataFormat.SdmxDataFormat.SchemaVersion, lazyDataFormat.Encoding);
break;
default:
return null;
}
return new DelayedDataWriterEngine(dataWriterEngine, lazyDataFormat.Actions, lazyDataFormat.DelayBehavior);
}
return null;
}
public byte[] GetTextAs(IDataFormat format)
{
try
{
return format.ParseString(Text);
}
catch(DataFormatException e)
{
return new byte[0];
}
}
/// <summary>
/// The get data writer engine.
/// </summary>
/// <param name="dataFormat">
/// The data format.
/// </param>
/// <param name="outPutStream">
/// The output stream.
/// </param>
/// <returns>
/// The <see cref="IDataWriterEngine"/>.
/// </returns>
public virtual IDataWriterEngine GetDataWriterEngine(IDataFormat dataFormat, Stream outPutStream)
{
foreach (IDataWriterFactory dwf in this._factory)
{
IDataWriterEngine dwe = dwf.GetDataWriterEngine(dataFormat, outPutStream);
if (dwe != null)
{
return dwe;
}
}
throw new SdmxNotImplementedException("Could not write data out in type: " + dataFormat);
}
/// <summary>
/// Gets the data writer engine.
/// </summary>
/// <param name="dataFormat">
/// The data format.
/// </param>
/// <param name="outStream">
/// The output stream.
/// </param>
/// <returns>
/// The <see cref="IDataWriterEngine"/>.
/// </returns>
public IDataWriterEngine GetDataWriterEngine(IDataFormat dataFormat, Stream outStream)
{
if (dataFormat.SdmxDataFormat != null)
{
switch (dataFormat.SdmxDataFormat.BaseDataFormat.EnumType)
{
case BaseDataFormatEnumType.Generic:
{
return new GenericDataWriterEngine(outStream, dataFormat.SdmxDataFormat.SchemaVersion);
}
case BaseDataFormatEnumType.Compact:
{
return new CompactDataWriterEngine(outStream, dataFormat.SdmxDataFormat.SchemaVersion);
}
case BaseDataFormatEnumType.Edi:
return new GesmesTimeSeriesWriter(outStream, true);
}
}
return null;
}
/// <summary>
/// 从datatable 中导出到excel
/// </summary>
/// <param name="dtSource">datatable数据源</param>
/// <param name="strHeaderText">表名</param>
/// <param name="fs">文件流</param>
/// <param name="readfs">内存流</param>
/// <param name="sheetnum">sheet索引</param>
static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs, MemoryStream readfs, Dictionary <string, string> dir, int sheetnum)
{
IWorkbook workbook = new XSSFWorkbook();
if (readfs.Length > 0 && sheetnum > 0)
{
workbook = WorkbookFactory.Create(readfs);
}
ISheet sheet = null;
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 表头及样式
{
string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
if (workbook.GetSheetIndex(sheetName) >= 0)
{
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
}
sheet = workbook.CreateSheet(sheetName);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
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(drValue.ToString());
break;
}
}
#endregion
rowIndex++;
}
workbook.Write(fs);
fs.Close();
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param Name="dtSource">DataTable数据源</param>
/// <param Name="strHeaderText">Excel表头文本（例如：信息工程学院2014－2015学年第一学期教学检查听课安排）</param>
private MemoryStream Export(List <ExportExcelModel> dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "破晓技术团队";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "管理员名册"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
int j = 0;
foreach (ExportExcelModel row in dtSource)
{
// 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
// 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 800;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dcs.Count - 1)); // ------------------
}
//
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 800;
font.FontName = "宋体";
headStyle.SetFont(font);
int index = 0;
foreach (string column in dcs)
{
headerRow.CreateCell(index).SetCellValue(column);
headerRow.GetCell(index).CellStyle = headStyle;
sheet.AutoSizeColumn(index);
//设置列宽
//sheet.SetColumnWidth(index, (arrColWidth[index] + 1) * 256);
//sheet.SetColumnWi
index++;
}
}
rowIndex = 2;
}
//填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int columnIndex = 0;
//！！！！！！！！！！！！！！！！！！！！！！！如果没有职称可能会异常
DistinctSupervisor(dtSource[j].supervisors, ListSupervisor);//去督导员的职称
for (int i = 0; i < dcs.Count; i++)
{
ICell newCell = dataRow.CreateCell(columnIndex);
columnIndex++;
if (i != dcs.Count - 1)
{
//注意这个在导出的时候加了“\t” 的目的就是避免导出的数据显示为科学计数法。可以放在每行的首尾。
switch (i)
{
case 0:
//序号
newCell.SetCellValue((j + 1).ToString() + "\t");
break;
case 1:
//课程
newCell.SetCellValue(dtSource[j].classname.ToString() + "\t");
break;
case 2:
//授课内容
newCell.SetCellValue(dtSource[j].classcontent.ToString() + "\t");
break;
case 3:
//授课方式
newCell.SetCellValue(dtSource[j].classtype.ToString() + "\t");
break;
case 4:
//专业
newCell.SetCellValue(dtSource[j].major.ToString() + "\t");
break;
case 5:
//教室
newCell.SetCellValue(dtSource[j].classroom.ToString() + "\t");
break;
case 6:
//教师
newCell.SetCellValue(dtSource[j].teachername.ToString() + "\t");
break;
case 7:
//周次
newCell.SetCellValue(dtSource[j].week.ToString() + "\t");
break;
case 8:
//听课时间
newCell.SetCellValue(dtSource[j].time + "\t");
break;
case 9:
//听课人员安排
newCell.SetCellValue(FormatSupervisor(ListSupervisor) + "\t");
break;
case 10:
//分数
newCell.SetCellValue(" " + "\t");
break;
}
}
else
{
//申报
newCell.SetCellValue(" ");
}
}
j++;
rowIndex++;
}
adjustcolum(sheet); //调整列宽
AddBorder(sheet, workbook); //加边框
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// 取得欄位格式
/// </summary>
/// <param name="workbook"></param>
/// <param name="format"></param>
/// <returns></returns>
private static short GetCellFormat(IWorkbook workbook, string format)
{
IDataFormat dataFormat = workbook.CreateDataFormat();
return(dataFormat.GetFormat(format));
}
private void generate_my_data()
{
ISheet sheet0 = hssfworkbook.CreateSheet("Ruch spraw");
DataView view = (DataView)dane_do_tabeli_1.Select(DataSourceSelectArguments.Empty);
DataTable table = view.ToTable();
DataTable dT = (DataTable)Session["header_01"];
table.TableName = "Załatwienia";
table.Columns.Remove("id_");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
var crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 0);
IRow row0 = sheet0.CreateRow(0);
#region tabela1
foreach (DataRow dR in getData(dT, "Column1=3"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row0 = sheet0.CreateRow(1);
foreach (DataRow dR in getData(dT, "Column1=2"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row0 = sheet0.CreateRow(2);
foreach (DataRow dR in getData(dT, "Column1=1"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
int rol = 3;
foreach (DataRow rowik in table.Rows)
{
row0 = sheet0.CreateRow(rol);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row0.CreateCell(i).SetCellValue(ji);
row0.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row0.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
rol++;
}// end foreach
#endregion tabela1
#region drugi arkusz
// druga tabela
view = (DataView)dane_do_tabeli_2.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.TableName = "Załatwienia";
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
//
//robienie
int ro = 2;
//-----------------
IDataFormat format = hssfworkbook.CreateDataFormat();
ISheet sheet1 = hssfworkbook.CreateSheet("Załatwienia");
IRow row2 = sheet1.CreateRow(0);
dT.Clear();
dT = (DataTable)Session["header_02"];
//===========
foreach (DataRow dR in getData(dT, "Column1=3"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row2 = sheet0.CreateRow(1);
foreach (DataRow dR in getData(dT, "Column1=2"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row2 = sheet0.CreateRow(2);
foreach (DataRow dR in getData(dT, "Column1=1"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
rol = 3;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet0.CreateRow(rol);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
rol++;
}// end foreach
foreach (DataRow rowik in table.Rows)
{
row2 = sheet1.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
#endregion drugi arkusz
// trzeci sheet
view = (DataView)tabela_3.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_10");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
sheet1.AutoSizeColumn(0, true);
sheet1.AutoSizeColumn(1, true);
ISheet sheet2 = hssfworkbook.CreateSheet("Wyznaczenia");
row2 = sheet2.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet2.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Wyznaczenia");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet2.AddMergedRegion(crs);
row2 = sheet2.CreateRow(1);
row2.CreateCell(5).SetCellValue("GU bez ''of''");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("GU ''of''");
row2.CreateCell(8).SetCellValue("GU Razem");
row2.CreateCell(9).SetCellValue("GUp bez '''of'");
row2.CreateCell(10).SetCellValue("GUp ''of''");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
row2.CreateCell(13).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet2.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
// czwarty sheet
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
//table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_09");
table.Columns.Remove("d_10");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
ISheet sheet3 = hssfworkbook.CreateSheet("Stan referatów sędziów");
row2 = sheet3.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet3.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Pozostało w referatach spraw kategorii");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet3.AddMergedRegion(crs);
row2 = sheet3.CreateRow(1);
row2.CreateCell(5).SetCellValue("GU bez ''of''");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("GU ''of''");
row2.CreateCell(8).SetCellValue("GU Razem");
row2.CreateCell(9).SetCellValue("GUp bez '''of'");
row2.CreateCell(10).SetCellValue("GUp ''of''");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
// row2.CreateCell(12).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet3.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
}
public void TestUnicodeInAll()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICreationHelper factory = wb.GetCreationHelper(/*getter*/);
//Create a unicode dataformat (Contains euro symbol)
IDataFormat df = wb.CreateDataFormat();
String formatStr = "_([$\u20ac-2]\\\\\\ * #,##0.00_);_([$\u20ac-2]\\\\\\ * \\\\\\(#,##0.00\\\\\\);_([$\u20ac-2]\\\\\\ *\\\"\\-\\\\\"??_);_(@_)";
short fmt = df.GetFormat(formatStr);
//Create a unicode sheet name (euro symbol)
ISheet s = wb.CreateSheet("\u20ac");
//Set a unicode header (you guessed it the euro symbol)
IHeader h = s.Header;
h.Center = (/*setter*/ "\u20ac");
h.Left = (/*setter*/ "\u20ac");
h.Right = (/*setter*/ "\u20ac");
//Set a unicode footer
IFooter f = s.Footer;
f.Center = (/*setter*/ "\u20ac");
f.Left = (/*setter*/ "\u20ac");
f.Right = (/*setter*/ "\u20ac");
IRow r = s.CreateRow(0);
ICell c = r.CreateCell(1);
c.SetCellValue(12.34);
c.CellStyle.DataFormat = (/*setter*/ fmt);
ICell c2 = r.CreateCell(2); // TODO - c2 unused but changing next line ('c'->'c2') causes Test to fail
c.SetCellValue(factory.CreateRichTextString("\u20ac"));
ICell c3 = r.CreateCell(3);
String formulaString = "TEXT(12.34,\"\u20ac###,##\")";
c3.CellFormula = (/*setter*/ formulaString);
wb = _testDataProvider.WriteOutAndReadBack(wb);
//Test the sheetname
s = wb.GetSheet("\u20ac");
Assert.IsNotNull(s);
//Test the header
h = s.Header;
Assert.AreEqual(h.Center, "\u20ac");
Assert.AreEqual(h.Left, "\u20ac");
Assert.AreEqual(h.Right, "\u20ac");
//Test the footer
f = s.Footer;
Assert.AreEqual(f.Center, "\u20ac");
Assert.AreEqual(f.Left, "\u20ac");
Assert.AreEqual(f.Right, "\u20ac");
//Test the dataformat
r = s.GetRow(0);
c = r.GetCell(1);
df = wb.CreateDataFormat();
Assert.AreEqual(formatStr, df.GetFormat(c.CellStyle.DataFormat));
//Test the cell string value
c2 = r.GetCell(2);
Assert.AreEqual(c.RichStringCellValue.String, "\u20ac");
//Test the cell formula
c3 = r.GetCell(3);
Assert.AreEqual(c3.CellFormula, formulaString);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strSheetName">工作表名称</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string strSheetName,
string[] oldColumnNames, string[] newColumnNames)
{
if (oldColumnNames.Length != newColumnNames.Length)
{
return(new MemoryStream());
}
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(strSheetName);
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://www.rongzi.com/";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
if (HttpContext.Current.Session["realname"] != null)
{
si.Author = HttpContext.Current.Session["realname"].ToString();
}
else
{
if (HttpContext.Current.Session["username"] != null)
{
si.Author = HttpContext.Current.Session["username"].ToString();
}
} //填加xls文件作者信息
si.ApplicationName = "东方融资网"; //填加xls文件创建程序信息
si.LastAuthor = "融管系统"; //填加xls文件最后保存者信息
si.Comments = "融管系统自建文件"; //填加xls文件作者信息
if (!string.IsNullOrEmpty(strHeaderText))
{
si.Title = strHeaderText; //填加xls文件标题信息
si.Subject = strHeaderText; //填加文件主题信息
}
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 取得列宽
int[] arrColWidth = new int[oldColumnNames.Length];
for (int i = 0; i < oldColumnNames.Length; i++)
{
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < oldColumnNames.Length; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
}
#region 表头及样式
if (!string.IsNullOrEmpty(strHeaderText))
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = !string.IsNullOrEmpty(strHeaderText) ? sheet.CreateRow(1) : sheet.CreateRow(0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < oldColumnNames.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(newColumnNames[i].Replace("<br/>", Environment.NewLine));
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
}
#endregion
rowIndex = !string.IsNullOrEmpty(strHeaderText) ? 2 : 1;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < oldColumnNames.Length; i++)
{
ICell newCell = dataRow.CreateCell(i);
string drValue = row[oldColumnNames[i]].ToString();
switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue.Replace("<br/>", Environment.NewLine));
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
sheet = null;
workbook = null;
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public void ExportExcel(DataTable dt)
{
try
{
//创建一个工作簿
IWorkbook workbook = new HSSFWorkbook();
//创建一个 sheet 表
ISheet sheet = workbook.CreateSheet(dt.TableName);
//创建一行
IRow rowH = sheet.CreateRow(0);
//创建一个单元格
ICell cell = null;
//创建单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//创建格式
IDataFormat dataFormat = workbook.CreateDataFormat();
//设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
cellStyle.DataFormat = dataFormat.GetFormat("@");
//设置列名
foreach (DataColumn col in dt.Columns)
{
//创建单元格并设置单元格内容
rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption);
//设置单元格格式
rowH.Cells[col.Ordinal].CellStyle = cellStyle;
}
//写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
//跳过第一行，第一行为列名
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
cell.CellStyle = cellStyle;
}
}
//设置导出文件路径
string path = HttpContext.Current.Server.MapPath("/ImportExcel/");
//设置新建文件路径及名称
string savePath = path + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//创建文件
FileStream file = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write);
//创建一个 IO 流
MemoryStream ms = new MemoryStream();
//写入到流
workbook.Write(ms);
//转换为字节数组
byte[] bytes = ms.ToArray();
file.Write(bytes, 0, bytes.Length);
file.Flush();
//还可以调用下面的方法，把流输出到浏览器下载
OutputClient(bytes);
//释放资源
bytes = null;
ms.Close();
ms.Dispose();
file.Close();
file.Dispose();
workbook.Close();
sheet = null;
workbook = null;
}
catch (Exception ex)
{
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param Name="dtSource">DataTable数据源</param>
/// <param Name="strHeaderText">Excel表头文本（例如：车辆列表）</param>
public MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
int j = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 800;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dcs.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 800;
font.FontName = "宋体";
headStyle.SetFont(font);
int index = 0;
foreach (string column in dcs)
{
headerRow.CreateCell(index).SetCellValue(column);
headerRow.GetCell(index).CellStyle = headStyle;
sheet.AutoSizeColumn(index);
//设置列宽
//sheet.SetColumnWidth(index, (arrColWidth[index] + 1) * 256);
//sheet.SetColumnWi
index++;
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int columnIndex = 0;
DistinctSupervisor(dtSource.Rows[j][6].ToString(), ListSupervisor); //去职称
for (int i = 0; i < dcs.Count; i++)
{
ICell newCell = dataRow.CreateCell(columnIndex);
columnIndex++;
if (i != dcs.Count - 1)
{
//注意这个在导出的时候加了“\t” 的目的就是避免导出的数据显示为科学计数法。可以放在每行的首尾。
switch (i)
{
case 0:
newCell.SetCellValue((j + 1).ToString() + "\t");
break;
case 1:
newCell.SetCellValue(dtSource.Rows[j][8].ToString() + "\t");
break;
case 2:
newCell.SetCellValue(dtSource.Rows[j][9].ToString() + "\t");
break;
case 3:
newCell.SetCellValue(dtSource.Rows[j][10].ToString() + "\t");
break;
case 4:
newCell.SetCellValue(dtSource.Rows[j][11].ToString() + "\t");
break;
case 5:
newCell.SetCellValue(dtSource.Rows[j][7].ToString() + "\t");
break;
case 6:
newCell.SetCellValue(dtSource.Rows[j][2].ToString() + "\t");
break;
case 7:
newCell.SetCellValue(dtSource.Rows[j][3].ToString() + "\t");
break;
case 8:
//听课时间
newCell.SetCellValue(
CalendarTools.getdata(Common.Year, Convert.ToInt32(dtSource.Rows[j][3]), Convert.ToInt32(dtSource.Rows[j][4]) - CalendarTools.weekdays(CalendarTools.CaculateWeekDay(Common.Year, Common.Month, Common.Day)), Common.Month, Common.Day).ToLongDateString()
+ " " + addseparator(Convert.ToInt32(dtSource.Rows[j][5])) + "节" + "\t");
break;
case 9:
newCell.SetCellValue(FormatSupervisor(ListSupervisor) + "\t");
break;
case 10:
newCell.SetCellValue(dtSource.Rows[j][12].ToString() + "\t");
break;
}
}
else
{
newCell.SetCellValue(" ");
}
}
#endregion
j++;
rowIndex++;
}
adjustcolum(sheet); //调整列宽
AddBorder(sheet, workbook); //加边框
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
public ActionResult ExportData(string state)
{
try {
var curuser = OperatorProvider.Provider.Current();
string sql = @"select case when b.NATURE = '班组' then b.PARENTNAME else b.DEPTNAME END as DEPTNAME,ID,a.userid,b.REALNAME,b.gender,b.dutyname,a.devicename,TO_CHAR(a.CREATEDATE,'yyyy-mm-dd hh24:mi:ss') as datetime,case when (length(b.deptcode)>20) then (select d.SORTCODE from base_department d where d.deptcode = substr(b.deptcode,1,20)) else b.DEPTSORT end as DEPTSORTss from
bis_hikinoutlog a left join V_USERINFO b on a.userid = b.userid left join(select* from HJB_PERSONSET where MODULETYPE = 0) t on a.userid = t.userid
where REALNAME is not NULL and a.inout = 0 and not exists(select 1 from bis_hikinoutlog d where d.userid = a.userid and d.CREATEDATE + 0 > a.CREATEDATE + 0)
";
//判断当前登陆用户是什么级别
if (!curuser.RoleName.IsEmpty())
{
string RoleName = curuser.RoleName.ToString();
if (ViewBag.IsAppointAccount != 1)
{
if (RoleName.Contains("承包商级用户"))
{
//承包商级用户只可查看本单位门禁数据
sql += string.Format(" and (t.ISREFER is NULL or t.userid = '{0}')", curuser.UserId);
sql += string.Format(@" and b.ROLENAME like '%{0}%' and b.DEPTNAME = (select case when a.nature = '班组' then a.parentname else a.DEPTNAME end as bmname
from v_userinfo a where a.USERID = '{1}')", RoleName, curuser.UserId);
}
}
else if (RoleName.Contains("厂级部门用户") || RoleName.Contains("安全管理员") || RoleName.Contains("公司领导") || RoleName.Contains("公司管理员") || RoleName.Contains("公司级用户") || RoleName.Contains("超级管理员") || curuser.UserId.Contains("1521c21a-62c9-4aa1-9093-a8bda503ea89"))
{
//此级别的用户可查看所有数据
}
else
{
sql += string.Format(" and (t.ISREFER is NULL or t.userid = '{0}')", curuser.UserId);
if (state == "0")
{
sql += string.Format(@" and (b.DEPTNAME = (select case when a.nature = '班组' then a.parentname else a.DEPTNAME end as bmname
from v_userinfo a where a.USERID = '{0}') or b.PARENTNAME = (select case when a.nature = '班组' then a.parentname else a.DEPTNAME end as bmname
from v_userinfo a where a.USERID = '{0}'))", curuser.UserId);
}
}
}
if (state == "0")
{
sql += " and DEPTTYPE is NULL ORDER BY DEPTSORTss, b.deptsort,b.DEPTCODE,b.userid desc";
}
else
{
sql += " and DEPTTYPE is not NULL ORDER BY DEPTTYPE,DEPTSORTss,b.deptsort,b.DEPTCODE,b.userid desc";
}
DataTable data = operticketmanagerbll.GetDataTable(sql);
//导出excel
string title = "在厂人员统计信息";
HSSFWorkbook workbook = new HSSFWorkbook();//创建Workbook对象
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
sheet.DefaultRowHeight = 24 * 20;
int column = data.Columns.Count;
int indexRow = 0;
//标题
if (!string.IsNullOrEmpty(title))
{
IRow headerRow = sheet.CreateRow(indexRow);
headerRow.HeightInPoints = 30;
headerRow.CreateCell(0).SetCellValue(title);
//合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
sheet.AddMergedRegion(region);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 25;
font.FontName = "微软雅黑";
font.Boldweight = (short)FontBoldWeight.Bold;
cellstyle.SetFont(font);
var cell = sheet.GetRow(0).GetCell(0);
cell.CellStyle = cellstyle;
HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, sheet, workbook); //下边框
HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, sheet, workbook); //左边框
HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, sheet, workbook); //右边框
HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, sheet, workbook); //上边框
indexRow++;
}
//列头样式
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
IFont headerFont = workbook.CreateFont();
//headerFont.FontHeightInPoints = 4;
headerFont.FontName = "宋体";
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerStyle.SetFont(headerFont);
IRow row1 = sheet.CreateRow(indexRow);
row1.CreateCell(0).SetCellValue("部门名称");
row1.GetCell(0).CellStyle = headerStyle;
row1.CreateCell(1).SetCellValue("姓名");
row1.GetCell(1).CellStyle = headerStyle;
row1.CreateCell(2).SetCellValue("性别");
row1.GetCell(2).CellStyle = headerStyle;
row1.CreateCell(3).SetCellValue("岗位名称");
row1.GetCell(3).CellStyle = headerStyle;
row1.CreateCell(4).SetCellValue("门禁通道名称");
row1.GetCell(4).CellStyle = headerStyle;
row1.CreateCell(5).SetCellValue("进厂时间");
row1.GetCell(5).CellStyle = headerStyle;
//普通单元格样式
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.Alignment = HorizontalAlignment.Center;
bodyStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.Color = HSSFColor.Black.Index;
//font1.Boldweight = 25;
//font1.FontHeightInPoints = 12;
bodyStyle.FillForegroundColor = HSSFColor.White.Index;
bodyStyle.SetFont(font1);
//设置格式
IDataFormat format = workbook.CreateDataFormat();
//填充数据
for (int i = 0; i < data.Rows.Count; i++)
{
indexRow++;
IRow rowTemp = sheet.CreateRow(indexRow);
//rowTemp.Height = 62 * 20;
rowTemp.CreateCell(0).SetCellValue(data.Rows[i]["deptname"].ToString());
rowTemp.CreateCell(1).SetCellValue(data.Rows[i]["realname"].ToString());
rowTemp.CreateCell(2).SetCellValue(data.Rows[i]["gender"].ToString());
rowTemp.CreateCell(3).SetCellValue(data.Rows[i]["dutyname"].ToString());
rowTemp.CreateCell(4).SetCellValue(data.Rows[i]["devicename"].ToString());
rowTemp.CreateCell(5).SetCellValue(data.Rows[i]["datetime"].ToString());
rowTemp.GetCell(0).CellStyle = bodyStyle;
rowTemp.GetCell(1).CellStyle = bodyStyle;
rowTemp.GetCell(2).CellStyle = bodyStyle;
rowTemp.GetCell(3).CellStyle = bodyStyle;
rowTemp.GetCell(4).CellStyle = bodyStyle;
rowTemp.GetCell(5).CellStyle = bodyStyle;
}
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
sheet.AutoSizeColumn(3);
sheet.AutoSizeColumn(4);
sheet.AutoSizeColumn(5);
//合并单元格
MergeCells(sheet, data, 0, 0, 0);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", title + ".xls"));
}
catch (Exception ex) {
}
return(Success("导出成功。"));
//设置导出格式
//ExcelConfig excelconfig = new ExcelConfig();
//excelconfig.Title = (state == "0" ? "内部" : "外部") + "人员统计信息";
//excelconfig.TitleFont = "微软雅黑";
//excelconfig.TitlePoint = 25;
//excelconfig.FileName = "实时在厂人员统计导出.xls";
//excelconfig.IsAllSizeColumn = true;
////每一列的设置,没有设置的列信息，系统将按datatable中的列名导出
//List<ColumnEntity> listColumnEntity = new List<ColumnEntity>();
//excelconfig.ColumnEntity = listColumnEntity;
//ColumnEntity columnentity = new ColumnEntity();
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "deptname".ToLower(), ExcelColumn = "部门名称" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "realname".ToLower(), ExcelColumn = "姓名" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "gender".ToLower(), ExcelColumn = "性别" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dutyname".ToLower(), ExcelColumn = "岗位名称" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "devicename".ToLower(), ExcelColumn = "门禁通道名称" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "datetime".ToLower(), ExcelColumn = "进厂时间" });
////调用导出方法
//ExcelHelper.ExcelDownload(data, excelconfig);
}
public void ExportExcelDouble(DataTable dt)
{
try
{
//创建一个工作簿
IWorkbook workbook = new HSSFWorkbook();
//创建一个 sheet 表
ISheet sheet = workbook.CreateSheet(dt.TableName);
//创建第一行
IRow rowFirst = sheet.CreateRow(0);
//创建第二行
IRow rowSecond = sheet.CreateRow(1);
//创建一个单元格
ICell cell = null;
//创建单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.Justify; //垂直对齐(默认应该为center，如果center无效则用justify)
cellStyle.Alignment = HorizontalAlignment.Center; //水平对齐
//创建格式
IDataFormat dataFormat = workbook.CreateDataFormat();
//设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
cellStyle.DataFormat = dataFormat.GetFormat("@");
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 2));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 3, 5));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 6, 8));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 10, 12));
rowFirst.CreateCell(0).SetCellValue("考试内容");
rowFirst.Cells[0].CellStyle = cellStyle;
rowFirst.CreateCell(3).SetCellValue("考试要求");
rowFirst.Cells[1].CellStyle = cellStyle;
rowFirst.CreateCell(6).SetCellValue("难度值");
rowFirst.Cells[2].CellStyle = cellStyle;
rowFirst.CreateCell(9).SetCellValue("题型");
rowFirst.Cells[3].CellStyle = cellStyle;
rowFirst.CreateCell(10).SetCellValue("题目来源");
rowFirst.Cells[4].CellStyle = cellStyle;
//设置列名
foreach (DataColumn col in dt.Columns)
{
//创建单元格并设置单元格内容
rowSecond.CreateCell(col.Ordinal).SetCellValue(col.Caption);
//设置单元格格式
rowSecond.Cells[col.Ordinal].CellStyle = cellStyle;
}
//写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
//跳过第一行，第一行为列名
IRow row = sheet.CreateRow(i + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
cell.CellStyle = cellStyle;
}
}
//设置导出文件路径
string path = HttpContext.Current.Server.MapPath("/ImportExcel/");
//设置新建文件路径及名称
string savePath = path + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//创建文件
FileStream file = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write);
//创建一个 IO 流
MemoryStream ms = new MemoryStream();
//写入到流
workbook.Write(ms);
//转换为字节数组
byte[] bytes = ms.ToArray();
file.Write(bytes, 0, bytes.Length);
file.Flush();
//还可以调用下面的方法，把流输出到浏览器下载
OutputClient(bytes);
//释放资源
bytes = null;
ms.Close();
ms.Dispose();
file.Close();
file.Dispose();
workbook.Close();
sheet = null;
workbook = null;
}
catch (Exception ex)
{
}
}
public static ICellStyle Getcellstyle(IWorkbook wb, Stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
//标题
IFont fontTitle = wb.CreateFont();
fontTitle.FontHeightInPoints = 16;
fontTitle.FontName = "宋体";
fontTitle.IsBold = true;
//列头
IFont fontColumnHeader = wb.CreateFont();
fontColumnHeader.FontHeightInPoints = 12;
fontColumnHeader.FontName = "宋体";
fontColumnHeader.IsBold = true;
//内容
IFont fontContent = wb.CreateFont();
fontContent.FontHeightInPoints = 9;
fontContent.FontName = "宋体";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "宋体";
////边框
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.RightBorderColor = HSSFColor.OliveGreen.Black.Index;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case Stylexls.标题:
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.SetFont(fontTitle);
break;
case Stylexls.头:
cellStyle.SetFont(fontColumnHeader);
break;
case Stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy-MM-dd");
cellStyle.SetFont(fontContent);
break;
case Stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.Alignment = HorizontalAlignment.Right;
cellStyle.SetFont(fontContent);
break;
case Stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0.00");
cellStyle.SetFont(fontContent);
break;
case Stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(fontContent);
break;
case Stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(fontContent);
break;
case Stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(fontContent);
break;
case Stylexls.默认:
cellStyle.SetFont(fontContent);
break;
}
return(cellStyle);
}
/// <summary>
/// 导出文件
/// </summary>
/// <param name="list">数据集合List</param>
/// <param name="head">列集合</param>
/// <param name="title">导出文件名称</param>
/// <param name="rootpath">导出路径</param>
/// <returns></returns>
public static string ExportListToExcelNew(IQueryable list, List <ExportObj> head, string title, string rootpath, string filename)
{
try
{
string FileName = filename.ToString() + "_" + title + ".xlsx";
string strPath = Path.Combine(rootpath, FileName);
if (Directory.Exists(rootpath) == false)
{
Directory.CreateDirectory(rootpath);
}
//文件流对象
using (FileStream filestream = new FileStream(strPath, FileMode.Create, FileAccess.Write))
{
Type type = list.ElementType;
PropertyInfo[] properties = type.GetProperties();
Int32 i = 0;
Int32 j = 0;
//打开Excel对象
XSSFWorkbook workbook = new XSSFWorkbook();
IDataFormat format = workbook.CreateDataFormat();
//set int format
ICellStyle cellStyleInt = workbook.CreateCellStyle();
cellStyleInt.DataFormat = format.GetFormat("#,##0");
cellStyleInt.WrapText = true;
//set decimal format
ICellStyle cellStyleDecimal = workbook.CreateCellStyle();
cellStyleDecimal.DataFormat = format.GetFormat("#,##0.0000");
cellStyleDecimal.WrapText = true;
//set float format
ICellStyle cellStylefloat = workbook.CreateCellStyle();
cellStylefloat.DataFormat = HSSFDataFormat.GetBuiltinFormat("0%");
cellStylefloat.WrapText = true;
//set double format
ICellStyle cellStyledouble = workbook.CreateCellStyle();
cellStyledouble.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyledouble.WrapText = true;
ICellStyle cellStyleWrapText = workbook.CreateCellStyle();
cellStyleWrapText.WrapText = true;
//创建一个字体样式对象
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle cellStyleWrapTextTitle = workbook.CreateCellStyle();
cellStyleWrapTextTitle.WrapText = true;
cellStyleWrapTextTitle.VerticalAlignment = VerticalAlignment.Center;
cellStyleWrapTextTitle.SetFont(font);
//Excel的Sheet对象
ISheet sheet = workbook.CreateSheet(title);
sheet.SetColumnWidth(0, 30 * 256);
sheet.SetColumnWidth(1, 15 * 256);
sheet.SetColumnWidth(2, 15 * 256);
sheet.SetColumnWidth(3, 22 * 256);
//生成sheet第一行列名
IRow headerRow = sheet.CreateRow(0);
foreach (var item in head)
{
if (type.GetProperty(item.key) != null)
{
ICell cell = headerRow.CreateCell(j);
cell.CellStyle = cellStyleWrapTextTitle;
cell.SetCellValue(item.Name);
j++;
}
}
//生成sheet数据部分
j = 1;
foreach (var obj in list)
{
//Writelog(string.Format("【创建行开始】"));
IRow dataRow = sheet.CreateRow(j);
//Writelog(string.Format("【创建行结束】"));
i = 0;
foreach (var item in head)
{
//Writelog(string.Format("【列开始】行数{0},列名{1}", j, item.key));
PropertyInfo column = type.GetProperty(item.key);
if (column != null)
{
ICell cell = dataRow.CreateCell(i);
Type cellType = item.DataType;
if (column.GetValue(obj, null) != null)
{
//整数123,456
if (cellType == typeof(int))
{
cell.SetCellValue((int)column.GetValue(obj, null));
cell.CellStyle = cellStyleInt;
}
//金额123,456
else if (cellType == typeof(decimal))
{
cell.SetCellValue(Convert.ToDouble(column.GetValue(obj, null)));
cell.CellStyle = cellStyleDecimal;
}
else if (cellType == typeof(float))
{
cell.SetCellValue(Convert.ToDouble(column.GetValue(obj, null)));
cell.CellStyle = cellStylefloat;
}
else if (cellType == typeof(double))
{
cell.SetCellValue(Convert.ToDouble(column.GetValue(obj, null)));
cell.CellStyle = cellStyledouble;
}
else
{
cell.SetCellValue(column.GetValue(obj, null).ToString());
cell.CellStyle.WrapText = true;
}
}
i++;
}
}
j++;
}
//保存excel文档
sheet.ForceFormulaRecalculation = true;
workbook.Write(filestream);
workbook.Clear();
}
return(rootpath + FileName);
}
catch (Exception ex)
{
return("");
}
}
private short ParseDataFormat(string formatStr) {
IDataFormat dataFormat = workbook.CreateDataFormat();
return dataFormat.GetFormat(formatStr);
}
private void generate_my_data()
{
ISheet sheet0 = hssfworkbook.CreateSheet("Ruch spraw");
DataView view = (DataView)dane_do_tabeli_1.Select(DataSourceSelectArguments.Empty);
DataTable table = view.ToTable();
IRow row0 = sheet0.CreateRow(0);
table.TableName = "Załatwienia";
table.Columns.Remove("id_");
row0.CreateCell(0).SetCellValue("Opis");
row0.CreateCell(1).SetCellValue("Sprawy według repetoriów i wykazów");
var crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet0.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 8);
sheet0.AddMergedRegion(crs);
row0 = sheet0.CreateRow(1);
row0.CreateCell(1).SetCellValue("C");
row0.CreateCell(2).SetCellValue("CG-G");
row0.CreateCell(3).SetCellValue("Ns");
row0.CreateCell(4).SetCellValue("Nc");
row0.CreateCell(5).SetCellValue("Co");
row0.CreateCell(6).SetCellValue("Cps");
row0.CreateCell(7).SetCellValue("WSC");
row0.CreateCell(8).SetCellValue("Łącznie");
int rol = 2;
foreach (DataRow rowik in table.Rows)
{
row0 = sheet0.CreateRow(rol);
for (int i = 0; i < 9; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row0.CreateCell(i).SetCellValue(ji);
row0.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row0.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
rol++;
}// end foreach
// druga tabela
view = (DataView)statystyki.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table = view.ToTable();
table.TableName = "Załatwienia";
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
//table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
//
//robienie
int ro = 2;
//-----------------
IDataFormat format = hssfworkbook.CreateDataFormat();
//-----------------
ISheet sheet1 = hssfworkbook.CreateSheet("Załatwienia");
IRow row2 = sheet1.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet1.AddMergedRegion(crs);
row2.CreateCell(1).SetCellValue("Nazwisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet1.AddMergedRegion(crs);
row2.CreateCell(2).SetCellValue("Imię");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet1.AddMergedRegion(crs);
row2.CreateCell(3).SetCellValue("Funkcja");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet1.AddMergedRegion(crs);
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet1.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Liczba sesji");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 6);
sheet1.AddMergedRegion(crs);
row2.CreateCell(7).SetCellValue("Załatwienia");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 7, 14);
sheet1.AddMergedRegion(crs);
row2.CreateCell(15).SetCellValue("Il. sporządzonych uzasadnień");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 15, 15);
sheet1.AddMergedRegion(crs);
row2.CreateCell(16).SetCellValue("Nieobecności");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 16, 17);
sheet1.AddMergedRegion(crs);
row2 = sheet1.CreateRow(1);
row2.CreateCell(5).SetCellValue("rozprawy");
row2.CreateCell(6).SetCellValue("posiedzenia");
row2.CreateCell(7).SetCellValue("C");
row2.CreateCell(8).SetCellValue("C-GC");
row2.CreateCell(9).SetCellValue("Ns");
row2.CreateCell(10).SetCellValue("Nc");
row2.CreateCell(11).SetCellValue("Co");
row2.CreateCell(12).SetCellValue("Cps");
row2.CreateCell(13).SetCellValue("WSC");
row2.CreateCell(14).SetCellValue("Razem");
row2.CreateCell(16).SetCellValue("Urlopy");
row2.CreateCell(17).SetCellValue("Zwolnienia");
foreach (DataRow rowik in table.Rows)
{
row2 = sheet1.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
// trzeci sheet
view = (DataView)tabela_3.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_06");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
sheet1.AutoSizeColumn(0, true);
sheet1.AutoSizeColumn(1, true);
ISheet sheet2 = hssfworkbook.CreateSheet("Wyznaczenia");
row2 = sheet2.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet2.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Wyznaczenia");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet2.AddMergedRegion(crs);
row2 = sheet2.CreateRow(1);
row2.CreateCell(5).SetCellValue("C");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("Ns");
row2.CreateCell(8).SetCellValue("Nc");
row2.CreateCell(9).SetCellValue("Co");
row2.CreateCell(10).SetCellValue("Cps");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
row2.CreateCell(13).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet2.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
// czwarty sheet
view = (DataView)tabela_4.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
// table.Columns.Remove("id_tabeli");
// table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_09");
table.Columns.Remove("d_06");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
ISheet sheet3 = hssfworkbook.CreateSheet("Stan referatów sędziów");
row2 = sheet3.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet3.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Pozostało w referatach spraw kategorii");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet3.AddMergedRegion(crs);
row2 = sheet3.CreateRow(1);
row2.CreateCell(5).SetCellValue("C");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("Ns");
row2.CreateCell(8).SetCellValue("Nc");
row2.CreateCell(9).SetCellValue("Co");
row2.CreateCell(10).SetCellValue("Cps");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
// row2.CreateCell(12).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet3.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="columnNames">列名</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string[] columnNames)
{
if (columnNames != null && columnNames.Length != dtSource.Columns.Count)
{
throw new ArgumentException("参数不正确:columnNames,数组元素的个数需要和数据源列的数量相同!");
}
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "上海驰亚防伪科技有限公司";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "技术部";
si.Title = strHeaderText;
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
if (columnNames == null)
{
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
if (((arrColWidth[column.Ordinal] + 1) * 256) >= 40000)
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 40000);
}
else
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
else
{
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(columnNames[column.Ordinal]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
if (((arrColWidth[column.Ordinal] + 1) * 256) >= 40000)
{
sheet.SetColumnWidth(column.Ordinal, 40000);
}
else
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(List <T> lists, ExcelConfig excelConfig)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
Type type = typeof(T);
PropertyInfo[] properties = type.GetProperties();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "刘晓雷"; //填加xls文件作者信息
si.ApplicationName = "力软信息"; //填加xls文件创建程序信息
si.LastAuthor = "刘晓雷"; //填加xls文件最后保存者信息
si.Comments = "刘晓雷"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[properties.Length];
string[] arrColName = new string[properties.Length]; //列名
ICellStyle[] arryColumStyle = new ICellStyle[properties.Length]; //样式表
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
if (excelConfig.Background != new Color())
{
if (excelConfig.Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
}
}
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig.TitlePoint;
if (excelConfig.ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig.ForeColor);
}
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
#endregion
#region 设置内容单元格样式
int i = 0;
foreach (PropertyInfo column in properties)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(column.Name).Length;
arrColName[i] = column.Name;
if (excelConfig.ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == column.Name);
if (columnentity != null)
{
arrColName[i] = columnentity.ExcelColumn;
if (columnentity.Width != 0)
{
arrColWidth[i] = columnentity.Width;
}
if (columnentity.Background != new Color())
{
if (columnentity.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
}
}
if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
{
IFont columnFont = workbook.CreateFont();
columnFont.FontHeightInPoints = 10;
if (columnentity.Font != null)
{
columnFont.FontName = columnentity.Font;
}
if (columnentity.Point != 0)
{
columnFont.FontHeightInPoints = columnentity.Point;
}
if (columnentity.ForeColor != new Color())
{
columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
}
columnStyle.SetFont(font);
}
}
}
arryColumStyle[i] = columnStyle;
i++;
}
#endregion
#region 填充数据
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
foreach (T item in lists)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(0);
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, lists.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
int headIndex = 0;
foreach (PropertyInfo column in properties)
{
headerRow.CreateCell(headIndex).SetCellValue(arrColName[headIndex]);
headerRow.GetCell(headIndex).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256);
headIndex++;
}
#endregion
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int ordinal = 0;
foreach (PropertyInfo column in properties)
{
ICell newCell = dataRow.CreateCell(ordinal);
newCell.CellStyle = arryColumStyle[ordinal];
string drValue = column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString();
SetCell(newCell, dateStyle, column.PropertyType, drValue);
ordinal++;
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
public void ExportToExcel <T>(IEnumerable <T> data, string fullPath, ExportOption exportOption = null)
{
data.ThrowIfNull(nameof(data));
fullPath.ThrowIfNull(nameof(fullPath));
exportOption = exportOption ?? new ExportOption();
List <T> list = data.ToList();
#region create workbook
IWorkbook workbook;
if (fullPath.EndsWith("xlsx", StringComparison.OrdinalIgnoreCase))
{
if (File.Exists(fullPath))
{
workbook = new XSSFWorkbook(new FileStream(fullPath, FileMode.OpenOrCreate));
}
else
{
workbook = new XSSFWorkbook();
}
}
else
{
if (File.Exists(fullPath))
{
workbook = new HSSFWorkbook(new FileStream(fullPath, FileMode.OpenOrCreate));
}
else
{
workbook = new HSSFWorkbook();
}
}
#endregion
#region cell style
Dictionary <Type, ICellStyle> cellStyles = new Dictionary <Type, ICellStyle>();
IDataFormat dataFormat = workbook.CreateDataFormat();
ICellStyle dateTimeCellStyle = workbook.CreateCellStyle();
dateTimeCellStyle.DataFormat = dataFormat.GetFormat(exportOption.DateFormat ?? "yyyy-MM-dd HH:mm:ss");
cellStyles[typeof(DateTime)] = dateTimeCellStyle;
if (!exportOption.NumberFormat.IsEmpty())
{
ICellStyle doubleCellStyle = workbook.CreateCellStyle();
doubleCellStyle.DataFormat = dataFormat.GetFormat(exportOption.NumberFormat);
cellStyles[typeof(double)] = doubleCellStyle;
}
#endregion
#region create sheet
ISheet exportSheet;
string sheetName = exportOption.SheetName.IsEmpty() ? "sheet1" : exportOption.SheetName;
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
if (sheet.SheetName == sheetName)
{
workbook.RemoveSheetAt(i);
break;
}
}
exportSheet = workbook.CreateSheet(sheetName);
#endregion
#region create header
IRow row = exportSheet.CreateRow(0);
PropertyInfo[] propertyInfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
for (int i = 0; i < propertyInfos.Length; i++)
{
ICell cell = row.CreateCell(i);
var prop = propertyInfos[i];
var headerAttr = prop.GetCustomAttribute <ExcelHeaderAttribute>();
cell.SetCellValue(headerAttr?.Name ?? prop.Name);
}
#endregion
#region export data
for (int i = 0; i < list.Count; i++)
{
T t = list[i];
IRow sheetRow = exportSheet.CreateRow(i + 1);
for (int j = 0; j < propertyInfos.Length; j++)
{
object cellValue = propertyInfos[j].GetValue(t);
ICell cell = sheetRow.CreateCell(j);
if (cellValue == null)
{
cell.SetCellValue("");
}
else
{
SetCellValue(cell, cellValue, cellStyles);
}
}
}
#endregion
for (int i = 0; i < propertyInfos.Length; i++)
{
exportSheet.AutoSizeColumn(i);
}
using (FileStream fileStream = new FileStream(fullPath, FileMode.OpenOrCreate))
{
workbook.Write(fileStream);
}
workbook?.Close();
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(DataTable dtSource, ExcelConfig excelConfig)
{
for (int i = 0; i < dtSource.Columns.Count;)
{
bool IsExists = false;
DataColumn column = dtSource.Columns[i];
for (int j = 0; j < excelConfig.ColumnEntity.Count; j++)
{
if (excelConfig.ColumnEntity[j].Column == column.ColumnName)
{
IsExists = true;
break;
}
}
if (!IsExists)
{
dtSource.Columns.Remove(column);
}
else
{
i++;
}
}
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "刘晓雷"; //填加xls文件作者信息
si.ApplicationName = "力软信息"; //填加xls文件创建程序信息
si.LastAuthor = "刘晓雷"; //填加xls文件最后保存者信息
si.Comments = "刘晓雷"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[dtSource.Columns.Count];
string[] arrColName = new string[dtSource.Columns.Count]; //列名
ICellStyle[] arryColumStyle = new ICellStyle[dtSource.Columns.Count]; //样式表
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
if (excelConfig.Background != new Color())
{
if (excelConfig.Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
}
}
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig.TitlePoint;
if (excelConfig.ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig.ForeColor);
}
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
#endregion
#region 设置内容单元格样式
foreach (DataColumn item in dtSource.Columns)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
arrColName[item.Ordinal] = item.ColumnName.ToString();
if (excelConfig.ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == item.ColumnName);
if (columnentity != null)
{
arrColName[item.Ordinal] = columnentity.ExcelColumn;
if (columnentity.Width != 0)
{
arrColWidth[item.Ordinal] = columnentity.Width;
}
if (columnentity.Background != new Color())
{
if (columnentity.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
}
}
if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
{
IFont columnFont = workbook.CreateFont();
columnFont.FontHeightInPoints = 10;
if (columnentity.Font != null)
{
columnFont.FontName = columnentity.Font;
}
if (columnentity.Point != 0)
{
columnFont.FontHeightInPoints = columnentity.Point;
}
if (columnentity.ForeColor != new Color())
{
columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
}
columnStyle.SetFont(font);
}
columnStyle.Alignment = getAlignment(columnentity.Alignment);
}
}
arryColumStyle[item.Ordinal] = columnStyle;
}
if (excelConfig.IsAllSizeColumn)
{
#region 根据列中最长列的长度取得列宽
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
if (arrColWidth[j] != 0)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
}
#endregion
}
#endregion
#region 填充数据
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(0);
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
#endregion
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = arryColumStyle[column.Ordinal];
string drValue = row[column].ToString();
SetCell(newCell, dateStyle, column.DataType, drValue);
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/**
* create a library of cell styles
*/
private static Dictionary <String, ICellStyle> createStyles(IWorkbook wb)
{
Dictionary <String, ICellStyle> styles = new Dictionary <String, ICellStyle>();
IDataFormat df = wb.CreateDataFormat();
ICellStyle style;
IFont headerFont = wb.CreateFont();
headerFont.IsBold = true;
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(headerFont);
styles.Add("header", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(headerFont);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("header_date", style);
IFont font1 = wb.CreateFont();
font1.IsBold = true;
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font1);
styles.Add("cell_b", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font1);
styles.Add("cell_b_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font1);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_b_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_g", style);
IFont font2 = wb.CreateFont();
font2.Color = (IndexedColors.Blue.Index);
font2.IsBold = true;
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font2);
styles.Add("cell_bb", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_bg", style);
IFont font3 = wb.CreateFont();
font3.FontHeightInPoints = ((short)14);
font3.Color = (IndexedColors.DarkBlue.Index);
font3.IsBold = true;
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font3);
style.WrapText = (true);
styles.Add("cell_h", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.WrapText = (true);
styles.Add("cell_normal", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.WrapText = (true);
styles.Add("cell_normal_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.WrapText = (true);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_normal_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.Indention = ((short)1);
style.WrapText = (true);
styles.Add("cell_indented", style);
style = CreateBorderedStyle(wb);
style.FillForegroundColor = (IndexedColors.Blue.Index);
style.FillPattern = FillPattern.SolidForeground;
styles.Add("cell_blue", style);
return(styles);
}
protected virtual void WriteData(IProgressMonitor progressMonitor, TextWriter writer, BindingListView bindingListView, IDataFormat dataFormat)
{
var tempBindingListView = new BindingListView(new ViewInfo(ParentColumn, bindingListView.GetViewSpec()), bindingListView.ToArray());
var tempDataGridView = new BoundDataGridView
{
DataSource = new BindingSource(tempBindingListView, ""),
SelectionMode = DataGridViewSelectionMode.FullRowSelect,
ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithAutoHeaderText,
RowHeadersVisible = false,
Visible = false,
BindingContext = new BindingContext(),
AllowUserToAddRows = false,
};
using (tempDataGridView)
{
var status = new ProgressStatus("Writing " + tempDataGridView.Rows.Count + " rows");
dataFormat.WriteRow(writer, tempDataGridView.Columns.Cast <DataGridViewColumn>().Select(column => column.HeaderCell.EditedFormattedValue));
var rowCount = tempDataGridView.Rows.Count;
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
if (progressMonitor.IsCanceled)
{
return;
}
status = status.ChangeMessage("Writing row " + (rowIndex + 1) + "/" + rowCount)
.ChangePercentComplete(rowIndex * 100 / rowCount);
progressMonitor.UpdateProgress(status);
var row = tempDataGridView.Rows.SharedRow(rowIndex);
dataFormat.WriteRow(writer,
row.Cells.Cast <DataGridViewCell>()
.Select(cell => cell.GetEditedFormattedValue(rowIndex, DataGridViewDataErrorContexts.Formatting | DataGridViewDataErrorContexts.ClipboardContent)));
}
}
}
/// <summary>
/// DataTable导出到Excel文件（无表头）另外的是有表头的
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">保存位置</param>
/// <param name="strSheetName">工作表名称</param>
/// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
public static void MyExport(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
{
if (strSheetName == "")
{
strSheetName = "Sheet";
}
MemoryStream getms = new MemoryStream();
#region 为getms赋值
if (oldColumnNames.Length != newColumnNames.Length)
{
getms = new MemoryStream();
}
HSSFWorkbook workbook = new HSSFWorkbook();
//HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
ISheet sheet = workbook.CreateSheet(strSheetName);
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://....../";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
if (HttpContext.Current.Session["realname"] != null)
{
si.Author = HttpContext.Current.Session["realname"].ToString();
}
else
{
if (HttpContext.Current.Session["username"] != null)
{
si.Author = HttpContext.Current.Session["username"].ToString();
}
} //填加xls文件作者信息
si.ApplicationName = "NPOI"; //填加xls文件创建程序信息
si.LastAuthor = "OA系统"; //填加xls文件最后保存者信息
si.Comments = "OA系统自动创建文件"; //填加xls文件作者信息
si.Title = strHeaderText; //填加xls文件标题信息
si.Subject = strHeaderText; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 取得列宽
int[] arrColWidth = new int[oldColumnNames.Length];
for (int i = 0; i < oldColumnNames.Length; i++)
{
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
}
/*
* foreach (DataColumn item in dtSource.Columns)
* {
* arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
* }
* */
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < oldColumnNames.Length; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
/*
* for (int j = 0; j < dtSource.Columns.Count; j++)
* {
* int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
* if (intTemp > arrColWidth[j])
* {
* arrColWidth[j] = intTemp;
* }
* }
* */
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
}
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1);
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < oldColumnNames.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
/*
* foreach (DataColumn column in dtSource.Columns)
* {
* headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
* headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
*
* //设置列宽
* sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
* }
* */
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
//foreach (DataColumn column in dtSource.Columns)
for (int i = 0; i < oldColumnNames.Length; i++)
{
ICell newCell = dataRow.CreateCell(i);
string drValue = row[oldColumnNames[i]].ToString();
switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
sheet = null;
workbook = null;
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
getms = ms;
}
#endregion
using (MemoryStream ms = getms)
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
protected void InitFontsStyles()
{
bold16 = wb.CreateFont();
bold16.Color = IndexedColors.BlueGrey.Index;
bold16.FontHeightInPoints = 16;
bold16.Boldweight = (short)FontBoldWeight.Bold;
bold11 = wb.CreateFont();
bold11.FontHeightInPoints = 11;
bold11.Boldweight = (short)FontBoldWeight.Bold;
bold12 = wb.CreateFont();
bold12.FontHeightInPoints = 12;
bold12.Boldweight = (short)FontBoldWeight.Bold;
bold14 = wb.CreateFont();
bold14.FontHeightInPoints = 14;
bold14.Boldweight = (short)FontBoldWeight.Bold;
bold11Italic = wb.CreateFont();
bold11Italic.FontHeightInPoints = 11;
bold11Italic.Boldweight = (short)FontBoldWeight.Bold;
bold11Italic.IsItalic = true;
boldUnderlined11 = wb.CreateFont();
boldUnderlined11.FontHeightInPoints = 11;
boldUnderlined11.Underline = FontUnderlineType.Single;
boldUnderlined11.Boldweight = (short)FontBoldWeight.Bold;
normal11 = wb.CreateFont();
normal11.FontHeightInPoints = 11;
normal11.Color = IndexedColors.Black.Index;
normal11.FontName = "Times New Roman";
format = wb.CreateDataFormat();
titleStyle = wb.CreateCellStyle();
titleStyle.SetFont(bold16);
titleStyle.Alignment = HorizontalAlignment.Center;
wrapTextStyle = wb.CreateCellStyle();
wrapTextStyle.WrapText = true;
tableTitleStyle = wb.CreateCellStyle();
tableTitleStyle.SetFont(bold14);
tableDateTitleStyle = wb.CreateCellStyle();
tableDateTitleStyle.SetFont(bold12);
tableDateTitleStyle.DataFormat = format.GetFormat("dddd, MMM. dd, yyyy");
tableDateTitleStyle.Alignment = HorizontalAlignment.Left;
columnHeaderStyleUnderlined = wb.CreateCellStyle();
columnHeaderStyleUnderlined.SetFont(bold11);
columnHeaderStyleUnderlined.Alignment = HorizontalAlignment.Center;
columnHeaderStyleUnderlined.FillForegroundColor = IndexedColors.Grey40Percent.Index;
columnHeaderStyleUnderlined.FillPattern = FillPattern.SolidForeground;
columnHeaderStyleUnderlined.BorderBottom = BorderStyle.Medium;
columnHeaderStyleUnderlined.WrapText = true;
columnHeaderStyle = wb.CreateCellStyle();
columnHeaderStyle.SetFont(bold11);
columnHeaderStyle.Alignment = HorizontalAlignment.Center;
columnHeaderStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
columnHeaderStyle.FillPattern = FillPattern.SolidForeground;
columnHeaderStyle.WrapText = true;
bold11RightStyle = wb.CreateCellStyle();
bold11RightStyle.SetFont(bold11);
bold11RightStyle.Alignment = HorizontalAlignment.Right;
bold11LeftStyle = wb.CreateCellStyle();
bold11LeftStyle.SetFont(bold11);
bold11LeftStyle.Alignment = HorizontalAlignment.Left;
textStyle = wb.CreateCellStyle();
textStyle.SetFont(normal11);
textStyle.VerticalAlignment = VerticalAlignment.Center;
currencyStyle = wb.CreateCellStyle();
currencyStyle.SetFont(normal11);
currencyStyle.DataFormat = format.GetFormat("$ #,##0.00;$ -#,##0.00");
currencyStyle.Alignment = HorizontalAlignment.Right;
currencyRightLineStyle = wb.CreateCellStyle();
currencyRightLineStyle.SetFont(normal11);
currencyRightLineStyle.DataFormat = format.GetFormat("$ #,##0.00;$ -#,##0.00");
currencyRightLineStyle.Alignment = HorizontalAlignment.Right;
currencyRightLineStyle.BorderRight = BorderStyle.Medium;
alternateTextStyle = wb.CreateCellStyle();
alternateTextStyle.SetFont(normal11);
setBackgroundCustomColor(alternateTextStyle, customColors[ALTERNATE_BACK_COLOR_INDEX]);
alternateTextStyle.FillPattern = FillPattern.SolidForeground;
alternateCurrencyStyle = wb.CreateCellStyle();
alternateCurrencyStyle.SetFont(normal11);
setBackgroundCustomColor(alternateCurrencyStyle, customColors[ALTERNATE_BACK_COLOR_INDEX]);
alternateCurrencyStyle.FillPattern = FillPattern.SolidForeground;
alternateCurrencyStyle.DataFormat = format.GetFormat("$ #,##0.00;$ -#,##0.00");
alternateCurrencyStyle.Alignment = HorizontalAlignment.Right;
alternateCurrencyRightLineStyle = wb.CreateCellStyle();
alternateCurrencyRightLineStyle.SetFont(normal11);
setBackgroundCustomColor(alternateCurrencyRightLineStyle, customColors[ALTERNATE_BACK_COLOR_INDEX]);
alternateCurrencyRightLineStyle.FillPattern = FillPattern.SolidForeground;
alternateCurrencyRightLineStyle.DataFormat = format.GetFormat("$ #,##0.00;$ -#,##0.00");
alternateCurrencyRightLineStyle.Alignment = HorizontalAlignment.Right;
alternateCurrencyRightLineStyle.BorderRight = BorderStyle.Medium;
intStyle = wb.CreateCellStyle();
intStyle.SetFont(normal11);
intStyle.Alignment = HorizontalAlignment.Right;
intStyle.DataFormat = format.GetFormat("#,##0");
alternateIntStyle = wb.CreateCellStyle();
alternateIntStyle.SetFont(normal11);
setBackgroundCustomColor(alternateIntStyle, customColors[ALTERNATE_BACK_COLOR_INDEX]);
alternateIntStyle.FillPattern = FillPattern.SolidForeground;
alternateIntStyle.Alignment = HorizontalAlignment.Right;
alternateIntStyle.DataFormat = format.GetFormat("#,##0");
intStyleWithoutThousandSeparator = wb.CreateCellStyle();
intStyleWithoutThousandSeparator.SetFont(normal11);
intStyleWithoutThousandSeparator.Alignment = HorizontalAlignment.Right;
intStyleWithoutThousandSeparator.DataFormat = format.GetFormat("#0");
alternateIntStyleWithoutThousandSeparator = wb.CreateCellStyle();
alternateIntStyleWithoutThousandSeparator.SetFont(normal11);
setBackgroundCustomColor(alternateIntStyleWithoutThousandSeparator, customColors[ALTERNATE_BACK_COLOR_INDEX]);
alternateIntStyleWithoutThousandSeparator.FillPattern = FillPattern.SolidForeground;
alternateIntStyleWithoutThousandSeparator.Alignment = HorizontalAlignment.Right;
alternateIntStyleWithoutThousandSeparator.DataFormat = format.GetFormat("#0");
percentageStyle = wb.CreateCellStyle();
percentageStyle.SetFont(normal11);
percentageStyle.DataFormat = format.GetFormat("#,##0.00%");
percentageStyle.Alignment = HorizontalAlignment.Right;
alternatePercentageStyle = wb.CreateCellStyle();
alternatePercentageStyle.SetFont(normal11);
setBackgroundCustomColor(alternatePercentageStyle, customColors[ALTERNATE_BACK_COLOR_INDEX]);
alternatePercentageStyle.FillPattern = FillPattern.SolidForeground;
alternatePercentageStyle.DataFormat = format.GetFormat("#,##0.00%");
alternatePercentageStyle.Alignment = HorizontalAlignment.Right;
dateStyle = wb.CreateCellStyle();
dateStyle.SetFont(normal11);
dateStyle.Alignment = HorizontalAlignment.Center;
dateStyle.DataFormat = format.GetFormat("dd/MM/yyyy");
alternateDateStyle = wb.CreateCellStyle();
alternateDateStyle.SetFont(normal11);
setBackgroundCustomColor(alternateDateStyle, customColors[ALTERNATE_BACK_COLOR_INDEX]);
alternateDateStyle.FillPattern = FillPattern.SolidForeground;
alternateDateStyle.Alignment = HorizontalAlignment.Center;
alternateDateStyle.DataFormat = format.GetFormat("dd/MM/yyyy");
dateTimeStyle = wb.CreateCellStyle();
dateTimeStyle.SetFont(normal11);
dateTimeStyle.Alignment = HorizontalAlignment.Center;
dateTimeStyle.DataFormat = format.GetFormat("dd/MM/yyyy HH:mm");
alternateDateTimeStyle = wb.CreateCellStyle();
alternateDateTimeStyle.SetFont(normal11);
setBackgroundCustomColor(alternateDateTimeStyle, customColors[ALTERNATE_BACK_COLOR_INDEX]);
alternateDateTimeStyle.FillPattern = FillPattern.SolidForeground;
alternateDateTimeStyle.Alignment = HorizontalAlignment.Center;
alternateDateTimeStyle.DataFormat = format.GetFormat("dd/MM/yyyy HH:mm");
dateTimeAmPmStyle = wb.CreateCellStyle();
dateTimeAmPmStyle.SetFont(normal11);
dateTimeAmPmStyle.Alignment = HorizontalAlignment.Center;
dateTimeAmPmStyle.DataFormat = format.GetFormat("dd/MM/yyyy hh:mm AM/PM");
alternateDateTimeAmPmStyle = wb.CreateCellStyle();
alternateDateTimeAmPmStyle.SetFont(normal11);
setBackgroundCustomColor(alternateDateTimeAmPmStyle, customColors[ALTERNATE_BACK_COLOR_INDEX]);
alternateDateTimeAmPmStyle.FillPattern = FillPattern.SolidForeground;
alternateDateTimeAmPmStyle.Alignment = HorizontalAlignment.Center;
alternateDateTimeAmPmStyle.DataFormat = format.GetFormat("dd/MM/yyyy HH:mm AM/PM");
fullDateStyle = wb.CreateCellStyle();
fullDateStyle.SetFont(normal11);
fullDateStyle.Alignment = HorizontalAlignment.Center;
fullDateStyle.DataFormat = format.GetFormat("dd-MMM-yyyy h:mm AM/PM");
alternateFullDateStyle = wb.CreateCellStyle();
alternateFullDateStyle.SetFont(normal11);
setBackgroundCustomColor(alternateFullDateStyle, customColors[ALTERNATE_BACK_COLOR_INDEX]);
alternateFullDateStyle.FillPattern = FillPattern.SolidForeground;
alternateFullDateStyle.Alignment = HorizontalAlignment.Center;
alternateFullDateStyle.DataFormat = format.GetFormat("dd-MMM-yyyy h:mm AM/PM");
subTotalTextStyle = wb.CreateCellStyle();
subTotalTextStyle.SetFont(bold11);
subTotalTextStyle.Alignment = HorizontalAlignment.Center;
subTotalTextStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
subTotalTextStyle.FillPattern = FillPattern.SolidForeground;
subTotalTextStyle.BorderBottom = BorderStyle.Medium;
subTotalTextStyle.WrapText = true;
setBackgroundCustomColor(subTotalTextStyle, customColors[SUB_TOTAL_BACK_COLOR_INDEX]);
subTotalTextStyle.BorderTop = BorderStyle.Medium;
subTotalTextStyle.Alignment = HorizontalAlignment.Right;
subTotalIntStyle = wb.CreateCellStyle();
subTotalIntStyle.SetFont(bold11);
subTotalIntStyle.Alignment = HorizontalAlignment.Center;
subTotalIntStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
subTotalIntStyle.FillPattern = FillPattern.SolidForeground;
subTotalIntStyle.BorderBottom = BorderStyle.Medium;
subTotalIntStyle.WrapText = true;
setBackgroundCustomColor(subTotalIntStyle, customColors[SUB_TOTAL_BACK_COLOR_INDEX]);
subTotalIntStyle.BorderTop = BorderStyle.Medium;
subTotalIntStyle.Alignment = HorizontalAlignment.Right;
subTotalIntStyle.DataFormat = format.GetFormat("#,##0");
subTotalCurrencyStyle = wb.CreateCellStyle();
subTotalCurrencyStyle.SetFont(bold11);
subTotalCurrencyStyle.Alignment = HorizontalAlignment.Center;
subTotalCurrencyStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
subTotalCurrencyStyle.FillPattern = FillPattern.SolidForeground;
subTotalCurrencyStyle.BorderBottom = BorderStyle.Medium;
subTotalCurrencyStyle.WrapText = true;
setBackgroundCustomColor(subTotalCurrencyStyle, customColors[SUB_TOTAL_BACK_COLOR_INDEX]);
subTotalCurrencyStyle.BorderTop = BorderStyle.Medium;
subTotalCurrencyStyle.Alignment = HorizontalAlignment.Right;
subTotalCurrencyStyle.DataFormat = format.GetFormat("$ #,##0.00;$ -#,##0.00");
grandTotalTextStyle = wb.CreateCellStyle();
grandTotalTextStyle.SetFont(bold11);
grandTotalTextStyle.Alignment = HorizontalAlignment.Center;
grandTotalTextStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
grandTotalTextStyle.FillPattern = FillPattern.SolidForeground;
grandTotalTextStyle.BorderBottom = BorderStyle.Medium;
grandTotalTextStyle.WrapText = true;
grandTotalTextStyle.BorderTop = BorderStyle.Medium;
grandTotalTextStyle.Alignment = HorizontalAlignment.Right;
grandTotalIntStyle = wb.CreateCellStyle();
grandTotalIntStyle.SetFont(bold11);
grandTotalIntStyle.Alignment = HorizontalAlignment.Center;
grandTotalIntStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
grandTotalIntStyle.FillPattern = FillPattern.SolidForeground;
grandTotalIntStyle.BorderBottom = BorderStyle.Medium;
grandTotalIntStyle.WrapText = true;
grandTotalIntStyle.BorderTop = BorderStyle.Medium;
grandTotalIntStyle.Alignment = HorizontalAlignment.Right;
grandTotalIntStyle.DataFormat = format.GetFormat("#,##0");
grandTotalCurrencyStyle = wb.CreateCellStyle();
grandTotalCurrencyStyle.SetFont(bold11);
grandTotalCurrencyStyle.Alignment = HorizontalAlignment.Center;
grandTotalCurrencyStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
grandTotalCurrencyStyle.FillPattern = FillPattern.SolidForeground;
grandTotalCurrencyStyle.BorderBottom = BorderStyle.Medium;
grandTotalCurrencyStyle.WrapText = true;
grandTotalCurrencyStyle.BorderTop = BorderStyle.Medium;
grandTotalCurrencyStyle.Alignment = HorizontalAlignment.Right;
grandTotalCurrencyStyle.DataFormat = format.GetFormat("$ #,##0.00;$ -#,##0.00");
grandTotalCurrencyRightLineStyle = wb.CreateCellStyle();
grandTotalCurrencyRightLineStyle.SetFont(bold11);
grandTotalCurrencyRightLineStyle.Alignment = HorizontalAlignment.Center;
grandTotalCurrencyRightLineStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
grandTotalCurrencyRightLineStyle.FillPattern = FillPattern.SolidForeground;
grandTotalCurrencyRightLineStyle.BorderBottom = BorderStyle.Medium;
grandTotalCurrencyRightLineStyle.WrapText = true;
grandTotalCurrencyRightLineStyle.BorderTop = BorderStyle.Medium;
grandTotalCurrencyRightLineStyle.Alignment = HorizontalAlignment.Right;
grandTotalCurrencyRightLineStyle.DataFormat = format.GetFormat("$ #,##0.00;$ -#,##0.00");
grandTotalCurrencyRightLineStyle.BorderRight = BorderStyle.Medium;
grandTotalPercentageStyle = wb.CreateCellStyle();
grandTotalPercentageStyle.SetFont(bold11);
grandTotalPercentageStyle.Alignment = HorizontalAlignment.Center;
grandTotalPercentageStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
grandTotalPercentageStyle.FillPattern = FillPattern.SolidForeground;
grandTotalPercentageStyle.BorderBottom = BorderStyle.Medium;
grandTotalPercentageStyle.WrapText = true;
grandTotalPercentageStyle.BorderTop = BorderStyle.Medium;
grandTotalPercentageStyle.Alignment = HorizontalAlignment.Right;
grandTotalPercentageStyle.DataFormat = format.GetFormat("#,##0.00%");
rowSeparatorStyle = wb.CreateCellStyle();
rowSeparatorStyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
rowSeparatorStyle.FillPattern = FillPattern.ThinForwardDiagonals;;
}
/// <summary>
/// List导出到Excel的MemoryStream
/// </summary>
/// <param name="list">数据源</param>
/// <param name="sHeaderText">表头文本</param>
/// <param name="columns">需要导出的属性</param>
private MemoryStream CreateExportMemoryStream(List <T> list, string sHeaderText, string[] columns)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
Type type = typeof(T);
PropertyInfo[] properties = GetProperties(type, columns);
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#region 取得每列的列宽（最大宽度）
int[] arrColWidth = new int[properties.Length];
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
//GBK对应的code page是CP936
arrColWidth[columnIndex] = properties[columnIndex].Name.Length;
}
#endregion
for (int rowIndex = 0; rowIndex < list.Count; rowIndex++)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(sHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
// 类属性如果有Description就用Description当做列名
DescriptionAttribute customAttribute = (DescriptionAttribute)Attribute.GetCustomAttribute(properties[columnIndex], typeof(DescriptionAttribute));
string description = properties[columnIndex].Name;
if (customAttribute != null)
{
description = customAttribute.Description;
}
headerRow.CreateCell(columnIndex).SetCellValue(description);
headerRow.GetCell(columnIndex).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256);
}
}
#endregion
}
#endregion
#region 填充内容
ICellStyle contentStyle = workbook.CreateCellStyle();
contentStyle.Alignment = HorizontalAlignment.Left;
IRow dataRow = sheet.CreateRow(rowIndex + 2); // 前面2行已被占用
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
ICell newCell = dataRow.CreateCell(columnIndex);
newCell.CellStyle = contentStyle;
string drValue = properties[columnIndex].GetValue(list[rowIndex], null).ParseToString();
switch (properties[columnIndex].PropertyType.ToString())
{
case "System.String":
newCell.SetCellValue(drValue);
break;
case "System.DateTime":
case "System.Nullable`1[System.DateTime]":
newCell.SetCellValue(drValue.ParseToDateTime());
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean":
case "System.Nullable`1[System.Boolean]":
newCell.SetCellValue(drValue.ParseToBool());
break;
case "System.Byte":
case "System.Nullable`1[System.Byte]":
case "System.Int16":
case "System.Nullable`1[System.Int16]":
case "System.Int32":
case "System.Nullable`1[System.Int32]":
newCell.SetCellValue(drValue.ParseToInt());
break;
case "System.Int64":
case "System.Nullable`1[System.Int64]":
newCell.SetCellValue(drValue.ParseToString());
break;
case "System.Double":
case "System.Nullable`1[System.Double]":
newCell.SetCellValue(drValue.ParseToDouble());
break;
case "System.Decimal":
case "System.Nullable`1[System.Decimal]":
newCell.SetCellValue(drValue.ParseToDouble());
break;
case "System.DBNull":
newCell.SetCellValue(string.Empty);
break;
default:
newCell.SetCellValue(string.Empty);
break;
}
}
#endregion
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
workbook.Close();
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(DataTable[] dtSource, ExcelConfig[] excelConfig)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
for (int dtIndex = 0, length = dtSource.Length; dtIndex < length; dtIndex++)
{
// int colint = 0;
DataTable data = dtSource[dtIndex].Copy();
var tatalCount = dtSource[dtIndex].Columns.Count;
// var index = 0;
/* for (int i = 0; i < dtSource[dtIndex].Columns.Count;)
* {
* index++;
* DataColumn column = dtSource[dtIndex].Columns[i];
* if (excelConfig[dtIndex].ColumnEntity[colint].Column != column.ColumnName)
* {
* dtSource[dtIndex].Columns.Remove(column.ColumnName);
* }
* else
* {
* i++;
* colint++;
* if (colint == excelConfig[dtIndex].ColumnEntity.Count)
* {
* for (var j = index; j < tatalCount; j++)
* {
* DataColumn column1 = data.Columns[j];
* dtSource[dtIndex].Columns.Remove(column1.ColumnName);
* }
* break;
* }
* }
* }*/
ISheet sheet = workbook.CreateSheet((dtIndex + 1).ToString());
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "administrator"; //填加xls文件作者信息
si.ApplicationName = "ZoonTop"; //填加xls文件创建程序信息
si.LastAuthor = "administrator"; //填加xls文件最后保存者信息
si.Comments = "ZoonTop自动生成excel"; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[dtSource[dtIndex].Columns.Count];
string[] arrColName = new string[dtSource[dtIndex].Columns.Count]; //列名
ColumnEntity[] columnModel = new ColumnEntity[dtSource[dtIndex].Columns.Count]; //航头属性
ICellStyle[] arryColumStyle = new ICellStyle[dtSource[dtIndex].Columns.Count]; //样式表
if (excelConfig[dtIndex].Background != new Color())
{
if (excelConfig[dtIndex].Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig[dtIndex].Background);
}
}
//title文字
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig[dtIndex].TitlePoint;
if (excelConfig[dtIndex].ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig[dtIndex].ForeColor);
}
font.Boldweight = 700;
font.FontHeightInPoints = 20;
headStyle.SetFont(font);
headStyle.ShrinkToFit = true;
//垂直居中,水平居中
headStyle.VerticalAlignment = VerticalAlignment.Center;
headStyle.Alignment = HorizontalAlignment.Center;
//边框样式
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
headStyle.BorderBottom = BorderStyle.Thin;
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = 15; // excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
//边框样式
cHeadStyle.BorderLeft = BorderStyle.Thin;
cHeadStyle.BorderRight = BorderStyle.Thin;
cHeadStyle.BorderTop = BorderStyle.Thin;
cHeadStyle.BorderBottom = BorderStyle.Thin;
//垂直居中
cHeadStyle.VerticalAlignment = VerticalAlignment.Center;
#endregion
#region 设置内容单元格样式
foreach (DataColumn item in dtSource[dtIndex].Columns)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
arrColName[item.Ordinal] = item.ColumnName.ToString();
if (excelConfig[dtIndex].ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig[dtIndex].ColumnEntity.Find(t => t.Column == item.ColumnName);
if (columnentity != null)
{
arrColName[item.Ordinal] = columnentity.ExcelColumn;
columnModel[item.Ordinal] = columnentity;
if (columnentity.Width != 0)
{
arrColWidth[item.Ordinal] = columnentity.Width;
}
if (columnentity.Background != new Color())
{
if (columnentity.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
}
}
if (columnentity.Font != null || columnentity.Point != 0 /*|| columnentity.ForeColor != new Color()*/)
{
IFont columnFont = workbook.CreateFont();
columnFont.FontHeightInPoints = 10;
if (columnentity.Font != null)
{
columnFont.FontName = columnentity.Font;
}
if (columnentity.Point != 0)
{
columnFont.FontHeightInPoints = columnentity.Point;
}
if (columnentity.ForeColor != new Color())
{
columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
}
columnStyle.SetFont(font);
}
columnStyle.Alignment = getAlignment(columnentity.Alignment);
}
}
//边框样式
columnStyle.BorderLeft = BorderStyle.Thin;
columnStyle.BorderRight = BorderStyle.Thin;
columnStyle.BorderTop = BorderStyle.Thin;
columnStyle.BorderBottom = BorderStyle.Thin;
//单元格文字
IFont columnsFont = workbook.CreateFont();
columnsFont.FontHeightInPoints = 12;
columnStyle.SetFont(columnsFont);
//columnStyle.ShrinkToFit = true;
columnStyle.WrapText = true;//自动换行
//垂直居中
columnStyle.VerticalAlignment = VerticalAlignment.Center;
arryColumStyle[item.Ordinal] = columnStyle;
}
if (excelConfig[dtIndex].IsAllSizeColumn)
{
#region 根据列中最长列的长度取得列宽
for (int i = 0; i < dtSource[dtIndex].Rows.Count; i++)
{
for (int j = 0; j < dtSource[dtIndex].Columns.Count; j++)
{
if (arrColWidth[j] != 0)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource[dtIndex].Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
}
#endregion
}
#endregion
#region 填充数据
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
string xh = dtSource[dtIndex].Columns[0].ToString(); //第一个参数，用来合并单元格
int rowIndex = 0;
int createIndex = 0; //已经创建的行数
int dataIndex = 1;
int[] mergeRows = new int[dtSource[dtIndex].Columns.Count]; //需要合并的行数
IRow[] headerRows;
IRow headerRow;
bool IsCellRangeAddress;
int titleRow = 0;
double[] totalCount = new double[dtSource[dtIndex].Columns.Count];
if (dtSource[dtIndex].Rows.Count == 0)//当列表头大于65536行时会报错，但是....
{
#region 新建表，填充表头，填充列头，样式
#region 表头及样式
if (excelConfig[dtIndex].Title != null)
{
headerRow = sheet.CreateRow(0);
if (excelConfig[dtIndex].TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig[dtIndex].TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.Height = 45 * 20;
headerRow.CreateCell(0).SetCellValue(excelConfig[dtIndex].Title);
headerRow.GetCell(0).CellStyle = headStyle;
//标题的宽高
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[dtIndex].Columns.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
IsCellRangeAddress = false;
rowIndex = 1;
foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
{
if (columnEntity.IsCellRangeAddress)
{
rowIndex = rowIndex > columnEntity.Bottom ? rowIndex : columnEntity.Bottom;
IsCellRangeAddress = true;
}
}
headerRows = new IRow[rowIndex];
for (int hi = 0; hi < rowIndex; hi++)
{
headerRow = sheet.CreateRow(hi + 1);
headerRow.Height = 30 * 20;
headerRows[hi] = headerRow;
}
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
if (!IsCellRangeAddress)//简单表头
{
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
headerRows[0].CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRows[0].GetCell(column.Ordinal).CellStyle = cHeadStyle;
sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
}
}
else//复杂表头
{
int[] indexs = new int[rowIndex];
int max = 0;
foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
{
int headerIndex = columnEntity.Top - 1;
int index = indexs[headerIndex];
for (int i = 0; i < columnEntity.Left - index; i++)//填充合并项的空缺
{
headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue("");
headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
indexs[headerIndex]++;
}
headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue(columnEntity.ExcelColumn);
headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
sheet.SetColumnWidth(indexs[headerIndex], columnEntity.Width * 32);
if (columnEntity.IsCellRangeAddress)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(new CellRangeAddress(
columnEntity.Top,
columnEntity.Bottom,
columnEntity.Left,
columnEntity.Right
));
}
indexs[headerIndex]++;
max = max > indexs[headerIndex] ? max : indexs[headerIndex];
}
//填充表头的空缺
for (int headerDefectCell = 0; headerDefectCell < indexs.Length; headerDefectCell++)
{
for (int headerDefectCellNum = indexs[headerDefectCell]; headerDefectCellNum < max; headerDefectCellNum++)
{
headerRows[headerDefectCell].CreateCell(headerDefectCellNum).SetCellValue("");
headerRows[headerDefectCell].GetCell(headerDefectCellNum).CellStyle = cHeadStyle;
}
}
#endregion
}
rowIndex += 1;
createIndex = rowIndex;
#endregion
#endregion
#region 合计
IRow totalRow = sheet.CreateRow(rowIndex);
totalRow.Height = 30 * 20;
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
if (column.Ordinal == 0)
{
totalRow.CreateCell(column.Ordinal).SetCellValue("合计");
}
else
{
if (column.DataType.FullName.Equals("System.Double"))
{
totalRow.CreateCell(column.Ordinal).SetCellValue(0);
}
else
{
totalRow.CreateCell(column.Ordinal).SetCellValue("");
}
}
totalRow.GetCell(column.Ordinal).CellStyle = arryColumStyle[column.Ordinal];
sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
}
#endregion
}
foreach (DataRow row in dtSource[dtIndex].Rows)
{
#region 新建表，填充表头，填充列头，样式 如果没有数据表头为空
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)//数据过多创建新的sheet页
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
if (excelConfig[dtIndex].Title != null)
{
headerRow = sheet.CreateRow(0);
if (excelConfig[dtIndex].TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig[dtIndex].TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.Height = 45 * 20;
headerRow.CreateCell(0).SetCellValue(excelConfig[dtIndex].Title);
headerRow.GetCell(0).CellStyle = headStyle;
//标题的宽高
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[dtIndex].Columns.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
IsCellRangeAddress = false;
rowIndex = 1;
foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
{
if (columnEntity.IsCellRangeAddress)
{
rowIndex = rowIndex > columnEntity.Bottom ? rowIndex : columnEntity.Bottom;
IsCellRangeAddress = true;
}
}
headerRows = new IRow[rowIndex];
for (int hi = 0; hi < rowIndex; hi++)
{
headerRow = sheet.CreateRow(hi + 1);
headerRow.Height = 30 * 20;
headerRows[hi] = headerRow;
}
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
if (!IsCellRangeAddress)//简单表头
{
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
headerRows[0].CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRows[0].GetCell(column.Ordinal).CellStyle = cHeadStyle;
//设置列宽 // 第二个参数的单位是1/256个字符宽度，但与前端不一致，故改为48
//sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
}
}
else//复杂表头
{
int[] indexs = new int[rowIndex];
int max = 0;
foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
{
int headerIndex = columnEntity.Top - 1;
int index = indexs[headerIndex];
for (int i = 0; i < columnEntity.Left - index; i++)//填充合并项的空缺
{
headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue("");
headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
indexs[headerIndex]++;
}
headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue(columnEntity.ExcelColumn);
headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
sheet.SetColumnWidth(indexs[headerIndex], columnEntity.Width * 32);
if (columnEntity.IsCellRangeAddress)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(new CellRangeAddress(
columnEntity.Top,
columnEntity.Bottom,
columnEntity.Left,
columnEntity.Right
));
}
indexs[headerIndex]++;
max = max > indexs[headerIndex] ? max : indexs[headerIndex];
}
//填充表头的空缺
for (int headerDefectCell = 0; headerDefectCell < indexs.Length; headerDefectCell++)
{
for (int headerDefectCellNum = indexs[headerDefectCell]; headerDefectCellNum < max; headerDefectCellNum++)
{
headerRows[headerDefectCell].CreateCell(headerDefectCellNum).SetCellValue("");
headerRows[headerDefectCell].GetCell(headerDefectCellNum).CellStyle = cHeadStyle;
}
}
#endregion
}
rowIndex += 1;
createIndex = rowIndex;
titleRow = rowIndex;
#endregion
}
#endregion
#region 填充内容
if (createIndex <= rowIndex)
{
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.Height = 30 * 20;
createIndex++;
}
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
if (columnModel[column.Ordinal].Merge == "row")//合并行，行和列不可能同时出现在一个单元格
{
ICell newCell = sheet.GetRow(rowIndex).CreateCell(column.Ordinal);
newCell.CellStyle = arryColumStyle[column.Ordinal];
string drValue = row[column].ToString();
if (mergeRows[column.Ordinal] == 0)//计算要合并几行
{
//不计算父子关系，同级关系，只要和下一个相同，就合并 to edit
//改成只有序号一样的合并 to edit
//增加父级序号也要一致(父级序号是子级的子集：父级：B，子级BB)
for (int subDataIndex = dataIndex, total = dtSource[dtIndex].Rows.Count; subDataIndex < total; subDataIndex++)
{
/*if (drValue.Equals(dtSource[dtIndex].Rows[subDataIndex][column].ToString()))
* {
* mergeRows[column.Ordinal]++;
* }*/
string[] arr = column.ColumnName.ToString().Split('_');
string mark = "";
if (arr.Length > 1)
{
int markCount = 0;
int markLength = arr[0].Length;
bool isAllEqually = true;
foreach (DataColumn parentColumn in dtSource[dtIndex].Columns)
{
if (markCount <= markLength)
{
mark = markCount > 0 ? arr[0].Substring(0, markCount) + "_XH" : "XH";
if (parentColumn.ColumnName.Equals(mark))
{
if (!row[parentColumn.ColumnName].ToString().Equals(dtSource[dtIndex].Rows[subDataIndex][parentColumn.ColumnName].ToString()))
{
isAllEqually = false;
}
markCount++;
}
}
else
{
break;
}
}
if (isAllEqually)
{
mergeRows[column.Ordinal]++;
}
}
else
{
mark = "XH";
//当前序号一致 最外层
if (row[mark].ToString().Equals(dtSource[dtIndex].Rows[subDataIndex][mark].ToString()))
{
mergeRows[column.Ordinal]++;
}
}
}
for (int i = 1; i <= mergeRows[column.Ordinal]; i++)//预创建行
{
if (sheet.GetRow(rowIndex + i) != null)
{
IRow dataRow = sheet.CreateRow(rowIndex + i);
dataRow.Height = 30 * 20;
createIndex++;
}
}
sheet.AddMergedRegion(new CellRangeAddress(
rowIndex,
rowIndex + mergeRows[column.Ordinal],
column.Ordinal,
column.Ordinal
));
}
else
{
drValue = "";
mergeRows[column.Ordinal]--;
}
SetCell(newCell, dateStyle, column.DataType, drValue);
}
else if (columnModel[column.Ordinal].Merge == "col")//合并列
{
}
else//无合并单元格
{
ICell newCell = sheet.GetRow(rowIndex).CreateCell(column.Ordinal);
newCell.CellStyle = arryColumStyle[column.Ordinal];
string drValue = row[column].ToString();
SetCell(newCell, dateStyle, column.DataType, drValue);
}
if (column.DataType.FullName.Equals("System.Double"))
{
double value;
try
{
value = double.Parse(row[column].ToString());
totalCount[column.Ordinal] += value;
}
catch
{
totalCount[column.Ordinal] += 0;
}
}
}
#endregion
#region 合计
if (rowIndex == 65534 || dtSource[dtIndex].Rows.Count + titleRow == rowIndex + 1)//最后一行 或者所有数据填充完
{
IRow totalRow = sheet.CreateRow(rowIndex + 1);
totalRow.Height = 30 * 20;
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
if (column.Ordinal == 0)
{
totalRow.CreateCell(column.Ordinal).SetCellValue("合计");
}
else
{
if (column.DataType.FullName.Equals("System.Double"))
{
if (column.ColumnName.IndexOf("_") > 0)//子表 包含小计
{
totalRow.CreateCell(column.Ordinal).SetCellValue(totalCount[column.Ordinal] / 2);
}
else
{
totalRow.CreateCell(column.Ordinal).SetCellValue(totalCount[column.Ordinal]);
}
}
else
{
totalRow.CreateCell(column.Ordinal).SetCellValue("");
}
}
totalRow.GetCell(column.Ordinal).CellStyle = arryColumStyle[column.Ordinal];
sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
}
rowIndex++;
}
#endregion
rowIndex++;
dataIndex++;
}
#endregion
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
public async Task <IActionResult> GenerateBlankTemplateAsync()
{
var TheStream = new NPOIMemoryStream {
AllowClose = false
};
var IntegerFields = new List <string> {
"*** Surveyor Number", "DeedVolume", "DeedPage", "AutomatedFileNumber"
};
var FileName = $"PLSO_Upload_Template {DateTime.Now.ToString("yy-MMdd")}-{DateTime.Now.Ticks}.xlsx";
try {
var Columns = await excelTemplateRepo.GetTemplateColumnsAsync();
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("PLSO Record Import");
IsBold = CreateBoldStyle(workbook);
IsBlue = CreateBlueStyle(workbook);
IsValidation = CreateValidationStyle(workbook);
IsInteger = CreateIntegerStyle(workbook);
int RowIndex = 0;
IRow row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
sheet1.SetColumnWidth(col.ColumnIndex - 1, (col.ColumnWidth * 256));
if (col.IsRequired)
{
Cell.CellStyle = IsBold;
}
Cell.SetCellType(CellType.String);
Cell.SetCellValue(col.DisplayName);
} // foreach of the columns on the Display Name row
row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
Cell.SetCellType(CellType.String);
Cell.SetCellValue(col.ExampleData);
Cell.CellStyle = IsBlue;
} // foreach of the columns on the Example Data row
row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
Cell.SetCellType(CellType.String);
Cell.SetCellValue((col.IsRequired ? "REQUIRED: " : "") + col.Validation);
Cell.CellStyle = IsValidation;
Cell.CellStyle.WrapText = true;
} // foreach of the columns on the Validation row
for (var index = 1; index < 6; index++)
{
row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
if (col.IsRequired)
{
Cell.CellStyle = IsBold;
}
if (col.FieldName == "SurveyDate")
{
IDataFormat dataFormat = workbook.CreateDataFormat();
Cell.CellStyle.DataFormat = dataFormat.GetFormat("M/d/yyyy");
}
else if (IntegerFields.Contains(col.FieldName))
{
Cell.SetCellType(CellType.Numeric);
Cell.CellStyle = IsInteger;
}
else
{
Cell.SetCellType(CellType.String);
}
} // foreach of the columns on the Display Name row
} // for 5 rows add formatting and make the cell bold if required
XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
workbook.Write(TheStream);
} catch (Exception e) {
TheStream.Dispose();
logger.LogError(1, e, "Unable to export Blank Template to Excel");
}
TheStream.Seek(0, SeekOrigin.Begin);
TheStream.AllowClose = true;
return(File(TheStream, "application/vnd.ms-excel", FileName));
}
/// <summary>
/// 导出Excel数据
/// </summary>
/// <param name="data">数据</param>
/// <param name="sheetname"></param>
/// <param name="titles"></param>
/// <param name="columns"></param>
/// <param name="templatefile"></param>
/// <param name="outputFilePath"></param>
public virtual IWorkbook FillDataToExcel(DataTable data, int maxrow, string sheetName, int startRowIndex, int startColIndex,
string[] titles, string[] columns, Dictionary <string, string> keyValues,
string templatefile, string outputFilePath)
{
int sheetCount = 1;
int rowcount = data == null ? 0 : data.Rows.Count;
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "工作表";
}
IWorkbook workbook;
if (!string.IsNullOrEmpty(templatefile) && File.Exists(templatefile))
{
using (FileStream sourcefs = new FileStream(templatefile, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(sourcefs);
}
}
else
{
using (FileStream sourcefs = new FileStream(outputFilePath, FileMode.Create, FileAccess.Write))
{
workbook = new HSSFWorkbook();
}
}
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
ISheet sheet;
if (!string.IsNullOrEmpty(templatefile) && File.Exists(templatefile))
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(sheetName) ?? workbook.CreateSheet(sheetName);
}
//获取填充设置标记范围
List <FillSetting> settings = getTemplateSetting(sheet);
//标记渲染
renderDictFlag(data, maxrow, keyValues, settings, sheet, workbook);
if (data != null)
{
if (settings.Where(x => x.Flag == FlagType.DataSource).Count() > 0)
{
//模板方式渲染
renderByTemplate(data, maxrow, keyValues, settings, sheet, workbook);
}
else
{
//没有任何填充标记,按照默认情况填充数据
renderByDefault(data, maxrow, startRowIndex, startColIndex, titles, columns, workbook, sheet);
}
}
sheet.ForceFormulaRecalculation = true;
return(workbook);
}
/// <summary>
/// 导出数据到数据流
/// </summary>
/// <param name="dataSet">要导出的数据</param>
/// <param name="stream">要导出到的数据流</param>
/// <exception cref="ArgumentException">参数异常</exception>
public static void ExportToStream(IEnumerable<IEnumerable<object>> dataSet, Stream stream)
{
if (dataSet == null) throw new ArgumentException("无效的导入数据", "dataSet");
if (stream == null) throw new ArgumentException("目标数据流无效", "stream");
//创建工作簿
IWorkbook workbook = new HSSFWorkbook();
//创建日期的显示样式
_dateStyle = workbook.CreateCellStyle();
_dataFormat = workbook.CreateDataFormat();
_dateStyle.DataFormat = _dataFormat.GetFormat(_dataType);
//遍历每张表
foreach (IEnumerable<object> dataTable in dataSet) //每张表
{
if (dataTable == null || !dataTable.Any()) continue;
//获取表中第一个不为空的元素
var obj = dataTable.FirstOrDefault(o => o != null);
//如果没有不为空的元素,跳过这张表
if (obj == null) continue;
//获取该表中的对象的属性
var columns = obj.GetType().GetProperties();
//创建表
ISheet sheet = workbook.CreateSheet();
SetTable(sheet, dataTable);
}
workbook.Write(stream);
}
/// <summary>
/// Execute the rest query, write the response out to the output stream based on the data format
/// </summary>
/// <param name="dataQuery">
/// The data query
/// </param>
/// <param name="dataFormat">
/// The data format
/// </param>
/// <param name="outPutStream">
/// The output stream
/// </param>
public void ExecuteQuery(IRestDataQuery dataQuery, IDataFormat dataFormat, Stream outPutStream)
{
IDataQuery query = new DataQueryImpl(dataQuery, this._objectRetrievalManager);
using (IDataWriterEngine dataWriterEngine = this._dataWriterManager.GetDataWriterEngine(dataFormat, outPutStream))
{
this._dataRetrievalWithWriter.GetData(query, dataWriterEngine);
}
}
public void CreateOrReplace(string name, IDataFormat dataFormat)
{
this.dataFormat = dataFormat;
CreateOrReplaceDatastore(name);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="strHeaderText">Excel表头文本（例如：车辆列表）</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream NpoiExport(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#region 取得每列的列宽（最大宽度）
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
//GBK对应的code page是CP936
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;
}
}
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
ICellStyle contentStyle = workbook.CreateCellStyle();
contentStyle.Alignment = HorizontalAlignment.Left;
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = contentStyle;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public void ExportItemSourceToExcel(List <ProductiveTaskListModel> lists, string filePath)
{
if (lists.Count == 0)
{
return;
}
string part1 = lists[0].FProductionDate.ToLongDateString().ToString();
string fName = Path.Combine(filePath, $"{part1}生产任务清单.xls");
//如果存在此文件则添加1
if (File.Exists(fName))
{
fName = fName.Replace(".xls", DateTime.Now.ToString("HHmmss") + ".xls");
}
IWorkbook wb = new HSSFWorkbook();
//定义单元格格式
#region 缩小填充
ICellStyle fitStyle = wb.CreateCellStyle();
fitStyle.ShrinkToFit = true;
fitStyle.BorderBottom = BorderStyle.Thin; fitStyle.BorderLeft = BorderStyle.Thin; fitStyle.BorderTop = BorderStyle.Thin; fitStyle.BorderRight = BorderStyle.Thin;
IFont fitFont = wb.CreateFont();
fitFont.FontHeight = 14 * 20;
fitFont.FontName = "宋体";
fitStyle.SetFont(fitFont);
#endregion
#region 数字左对齐
ICellStyle numberStyle = wb.CreateCellStyle();
numberStyle.ShrinkToFit = true;
numberStyle.BorderBottom = BorderStyle.Thin; numberStyle.BorderLeft = BorderStyle.Thin; numberStyle.BorderTop = BorderStyle.Thin; numberStyle.BorderRight = BorderStyle.Thin;
numberStyle.Alignment = HorizontalAlignment.Right;
//numberStyle.DataFormat= HSSFDataFormat.GetBuiltinFormat("0");
IFont numberFont = wb.CreateFont();
numberFont.FontHeight = 14 * 20;
numberFont.FontName = "宋体";
numberStyle.SetFont(numberFont);
#endregion
#region 红色数字
ICellStyle warningStyle = wb.CreateCellStyle();
warningStyle.ShrinkToFit = true;
warningStyle.BorderBottom = BorderStyle.Thin; numberStyle.BorderLeft = BorderStyle.Thin; numberStyle.BorderTop = BorderStyle.Thin; numberStyle.BorderRight = BorderStyle.Thin;
IFont warningFont = wb.CreateFont();
warningFont.FontHeight = 14 * 20;
warningFont.FontName = "宋体";
warningFont.Color = HSSFColor.Red.Index;
warningStyle.SetFont(warningFont);
#endregion
#region 生产类型为返工时候，值为"灌"，设置样式
ICellStyle guanStyle = wb.CreateCellStyle();
guanStyle.Alignment = HorizontalAlignment.Center;
guanStyle.ShrinkToFit = true;
IFont guanFont = wb.CreateFont();
guanFont.Color = (short)FontColor.Red;
guanFont.FontName = "宋体";
guanFont.FontHeight = 15 * 20;
guanStyle.SetFont(guanFont);
#endregion
#region 标题样式
IFont titleFont = wb.CreateFont();
titleFont.FontName = "宋体";
titleFont.Boldweight = (short)FontBoldWeight.Bold;
titleFont.FontHeight = 24 * 20; //和行高一样？ 也是20倍
ICellStyle titleStyle = wb.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.Center;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
titleStyle.SetFont(titleFont);
#endregion
IDataFormat format = wb.CreateDataFormat();
//cellsylenumber.DataFormat = format.GetFormat("0.00");
//获取不同的类型
var countGroup = lists.Where(m => m.FType != null).GroupBy(m => m.FType).Select(t => new { FType = t.Key, Count = t.Count() });
foreach (var item in countGroup)
{
var typedlists = lists.Where(n => n.FType == item.FType).ToList();
string previousBatch = string.Empty;
double batchQuantity = 0;
//string prevoousProductionModel = string.Empty;
ISheet sheet = wb.CreateSheet(item.FType);
sheet.ForceFormulaRecalculation = true;
sheet.SetColumnWidth(0, 20);
//6.设置列宽 SetColumnWidth(列的索引号从0开始, N * 256) 第二个参数的单位是1/256个字符宽度。例：将第四列宽度设置为了30个字符
//7.设置行高 Height的单位是1/20个点。例：设置高度为50个点 row0.Height = 50 * 20;
sheet.SetColumnWidth(0, (int)19 * 256);
sheet.SetColumnWidth(1, (int)14 * 256);
sheet.SetColumnWidth(2, (int)11.5 * 256);
sheet.SetColumnWidth(3, (int)4 * 256);
sheet.SetColumnWidth(4, (int)13 * 256);
sheet.SetColumnWidth(5, (int)25.35 * 256);
sheet.SetColumnWidth(6, (int)6 * 256);
sheet.SetColumnWidth(7, (int)19.49 * 256);
sheet.SetColumnWidth(8, (int)18.2 * 256);
sheet.SetColumnWidth(9, (int)5.35 * 256);
sheet.SetColumnWidth(10, (int)9.92 * 256);
sheet.SetColumnWidth(11, (int)6 * 256);
sheet.SetColumnWidth(12, (int)7 * 256);
sheet.SetColumnWidth(13, (int)18 * 256); //灌和做货
sheet.SetColumnWidth(14, 8 * 256); //差额
sheet.SetColumnWidth(15, 0 * 256); //第几次做货
sheet.SetColumnWidth(16, 15 * 256); //订单号
sheet.SetColumnWidth(17, 40 * 256); //唯一值
sheet.SetColumnWidth(18, 14 * 256); //安全编号
sheet.SetColumnWidth(19, 10 * 256); //样油重量
#region Logo
ExportImgToExcel(sheet, (HSSFWorkbook)wb);
#endregion
#region 标题
IRow row0 = sheet.CreateRow(0);
row0.Height = 30 * 20;
//CellRangeAddress region = new CellRangeAddress(0, 0, 0, 13);//CellRangeAddress rg = new CellRangeAddress(j + 2, j + 2, 8, 11);
//sheet.AddMergedRegion(region);
ICell cell = row0.CreateCell(5);
cell.SetCellValue(typedlists[0].FProductionDate.ToString("yyyy年MM月dd日") + typedlists[0].FType + "生产任务清单");
/*第一个参数：从第几行开始合并 第二个参数：到第几行结束合并 第三个参数：从第几列开始合并 第四个参数：到第几列结束合并 */
cell.CellStyle = titleStyle;
#endregion
#region 表头
IRow row1 = sheet.CreateRow(1);
row1.Height = (short)20.5 * 20;
var A = row1.CreateCell(0); A.SetCellValue("产品型号"); A.CellStyle = fitStyle;
var B = row1.CreateCell(1); B.SetCellValue("产品批号"); B.CellStyle = fitStyle;
var C = row1.CreateCell(2); C.SetCellValue("生产数量"); C.CellStyle = fitStyle;
var D = row1.CreateCell(3); D.SetCellValue("小料"); D.CellStyle = fitStyle;
var E = row1.CreateCell(4); E.SetCellValue("包装桶数"); E.CellStyle = fitStyle;
var F = row1.CreateCell(5); F.SetCellValue("包装桶"); F.CellStyle = fitStyle;
var G = row1.CreateCell(6); G.SetCellValue("客户代码"); G.CellStyle = fitStyle;
var H = row1.CreateCell(7); H.SetCellValue("标签型号"); H.CellStyle = fitStyle;
var I = row1.CreateCell(8); I.SetCellValue("备注"); I.CellStyle = fitStyle;
var J = row1.CreateCell(9); J.SetCellValue("留样"); J.CellStyle = fitStyle;
var K = row1.CreateCell(10); K.SetCellValue("合格证"); K.CellStyle = fitStyle;
var L = row1.CreateCell(11); L.SetCellValue("接收人"); L.CellStyle = fitStyle;
var M = row1.CreateCell(12); M.SetCellValue("残液"); M.CellStyle = fitStyle;
var N = row1.CreateCell(13); N.SetCellValue(""); N.CellStyle = guanStyle;//灌不灌
var O = row1.CreateCell(14); O.SetCellValue("差额"); O.CellStyle = fitStyle;
var P = row1.CreateCell(15); P.SetCellValue("生产次数"); P.CellStyle = numberStyle;
var Q = row1.CreateCell(16); Q.SetCellValue("订单号"); Q.CellStyle = fitStyle;
var R = row1.CreateCell(17); R.SetCellValue("唯一值"); R.CellStyle = fitStyle;
var S = row1.CreateCell(18); S.SetCellValue("安全编号"); S.CellStyle = fitStyle;
var T = row1.CreateCell(19); T.SetCellValue("样油重量(g)"); T.CellStyle = fitStyle;
#endregion
for (int j = 0; j < typedlists.Count; j++)
{
IRow row = sheet.CreateRow(j + 2);
row.Height = (short)20.5 * 20;
string currentBatch = typedlists[j].FBatchNo;
var A0 = row.CreateCell(0); var B0 = row.CreateCell(1); var C0 = row.CreateCell(2); var D0 = row.CreateCell(3); var M0 = row.CreateCell(12); var O0 = row.CreateCell(14);
if (currentBatch != previousBatch)
{
batchQuantity = Convert.ToDouble(typedlists[j].FQuantity) - Convert.ToDouble(typedlists[j].RowQuantity) + Convert.ToDouble(typedlists[j].FResidue);
A0.SetCellValue(typedlists[j].FitemName);
B0.SetCellValue(currentBatch);
C0.SetCellValue(Convert.ToDouble(typedlists[j].FQuantity));
D0.SetCellValue(typedlists[j].FHasSmallMaterial);
M0.SetCellValue(Convert.ToDouble(typedlists[j].FResidue.ToString("0.0")));
O0.CellStyle = fitStyle;
}
else
{
batchQuantity -= Convert.ToDouble(typedlists[j].RowQuantity);
O0.CellStyle = warningStyle;
}
O0.SetCellValue(batchQuantity);
A0.CellStyle = fitStyle; B0.CellStyle = fitStyle; C0.CellStyle = numberStyle; M0.CellStyle = numberStyle; D0.CellStyle = fitStyle;
var E0 = row.CreateCell(4); E0.SetCellValue(typedlists[j].FPackage); E0.CellStyle = fitStyle;
var F0 = row.CreateCell(5); F0.SetCellValue(typedlists[j].FBucketName); F0.CellStyle = fitStyle;
var G0 = row.CreateCell(6); G0.SetCellValue(typedlists[j].FOrgID); G0.CellStyle = fitStyle;
var H0 = row.CreateCell(7); H0.SetCellValue(typedlists[j].FLabel); H0.CellStyle = fitStyle;
#region 备注字体设置
var I0 = row.CreateCell(8);
string value = typedlists[j].FNote;
I0.SetCellValue(value);
ICellStyle noteStyle = wb.CreateCellStyle();
noteStyle.BorderBottom = BorderStyle.Thin; noteStyle.BorderLeft = BorderStyle.Thin; noteStyle.BorderTop = BorderStyle.Thin;
IFont noteFont = wb.CreateFont();
noteFont.FontName = "宋体";
int dataLength = UniversalFunction.GetLength(value);
if (dataLength >= 50)
{
noteFont.FontHeight = 7 * 20;
}
else if (dataLength >= 40)
{
noteFont.FontHeight = 8.5 * 20;
}
else if (dataLength >= 30)
{
noteFont.FontHeight = 10 * 20;
}
else if (dataLength >= 25)
{
noteFont.FontHeight = 12 * 20;
}
else
{
noteFont.FontHeight = 14 * 20;
}
noteStyle.SetFont(noteFont);
I0.CellStyle = noteStyle;
#endregion
#region 留样开始3列不赋值(赋值空都会有个空格)
ICellStyle blankStyle = wb.CreateCellStyle();
blankStyle.BorderLeft = BorderStyle.Thin; blankStyle.BorderTop = BorderStyle.Thin; blankStyle.BorderBottom = BorderStyle.Thin;
var J0 = row.CreateCell(9); J0.CellStyle = blankStyle;
var K0 = row.CreateCell(10); K0.CellStyle = blankStyle;
var L0 = row.CreateCell(11); L0.CellStyle = blankStyle;
#endregion
string productionNumber = typedlists[j].ProductionNumber > 0 && typedlists[j].ProductionNumber <= 3 ? $"第{typedlists[j].ProductionNumber}次生产" : "";
string guan = typedlists[j].ProductionType == "返工" ? "灌" : "";
var N0 = row.CreateCell(13); N0.SetCellValue(guan + " " + productionNumber); N0.CellStyle = guanStyle;//灌不灌
//var P0 = row.CreateCell(15); P0.SetCellValue(); P0.CellStyle = fitStyle; // 第多少次生产和 灌写入同一个单元格，本单元格已设置宽度为0，
var Q0 = row.CreateCell(16); Q0.SetCellValue(typedlists[j].FBillNo); Q0.CellStyle = fitStyle;
var R0 = row.CreateCell(17); R0.SetCellValue(UniversalFunction.ToHexString(typedlists[j].RowHashValue)); R0.CellStyle = fitStyle;
var S0 = row.CreateCell(18); S0.SetCellValue(typedlists[j].SafeCode); S0.CellStyle = fitStyle;
var T0 = row.CreateCell(19); T0.SetCellValue(Convert.ToInt32(typedlists[j].PaintSampleTotal)); T0.CellStyle = numberStyle;
previousBatch = currentBatch.Length != 0 ? currentBatch : previousBatch;
}
}
FileStream fs = new FileStream(fName, FileMode.Create); //新建才不会报错
wb.Write(fs); //会自动关闭流文件 //fs.Flush();
fs.Close();
}
/// <summary>
/// 导出数据到数据流
/// </summary>
/// <param name="dataSet">要导出的数据</param>
/// <param name="stream">要导出到的数据流</param>
/// <exception cref="ArgumentException">参数异常</exception>
public static void ExportToStream(DataSet dataSet, Stream stream)
{
if (dataSet == null) throw new ArgumentException("无效的导入数据", "dataSet");
if (stream == null) throw new ArgumentException("目标数据流无效", "stream");
//创建工作簿
IWorkbook workbook = new HSSFWorkbook();
//创建日期的显示样式
_dateStyle = workbook.CreateCellStyle();
_dataFormat = workbook.CreateDataFormat();
_dateStyle.DataFormat = _dataFormat.GetFormat(_dataType);
DataTableCollection tables = dataSet.Tables;
for (int i = 0; i < tables.Count; i++) //每张表
{
//当前表的总行数
DataRowCollection rows = tables[i].Rows;
//如果表里面没有数据,忽略这个表
if (rows.Count < 1) continue;
//当前表的列集合
var columns = tables[i].Columns;
//创建表
string tableName = string.IsNullOrEmpty(tables[i].TableName) ? "Sheet" + i : tables[i].TableName;
var sheet = workbook.CreateSheet(tableName);
//创建表头
var titleRow = sheet.CreateRow(0);
for (int j = 0; j < columns.Count; j++)
{
string columnName = string.IsNullOrEmpty(columns[j].ColumnName) ? "Cell" + j : columns[j].ColumnName;
var cell = titleRow.CreateCell(j);
cell.SetCellValue(columnName);
}
//定义行开始索引
int rowIndex = 1;
foreach (DataRow dataRow in tables[i].Rows) //每一行
{
var row = sheet.CreateRow(rowIndex);
rowIndex++;
for (int j = 0; j < columns.Count; j++) //每一个格子
{
//var cellValue = dataRow[j] == DBNull.Value ? string.Empty : dataRow[j].ToString();
var cell = row.CreateCell(j);
//如果该属性为null,设置单元格为空格
if (dataRow[j] == null)
{
cell.SetCellType(CellType.Blank);
return;
}
//判断单元格类型
switch (columns[j].DataType.Name.ToLower())
{
case "char":
case "string":
cell.SetCellValue(Convert.ToString(dataRow[j]));
break;
case "double":
case "single":
case "int32":
cell.SetCellValue(Convert.ToDouble(dataRow[j]));
break;
case "boolean":
cell.SetCellValue(Convert.ToBoolean(dataRow[j]));
break;
case "datetime":
cell.SetCellValue(Convert.ToDateTime(dataRow[j]));
cell.CellStyle = _dateStyle;
break;
default:
cell.SetCellValue(dataRow[j].ToString());
break;
}
}
}
}
workbook.Write(stream);
}
