using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.IO;
|
using System.Linq;
|
using System.Text;
|
using System.Web;
|
using Microsoft.Office.Interop.Word;
|
using NPOI;
|
using NPOI.HPSF;
|
using NPOI.HSSF;
|
using NPOI.HSSF.UserModel;
|
using NPOI.HSSF.Util;
|
using NPOI.POIFS;
|
using NPOI.Util;
|
using NPOI.SS.UserModel;
|
using NPOI.SS.Util;
|
using NPOI.XSSF.UserModel;
|
using DataTable = System.Data.DataTable;
|
using IFont = NPOI.SS.UserModel.IFont;
|
|
namespace CommonHelper
|
{
|
/// <summary>
|
/// NPOI操作帮助类
|
/// </summary>
|
public class NPOIHelper
|
{
|
public static readonly string SheetLockPassword = Configs.GetValue("ExcelTemplatePsw");
|
|
/// <summary>
|
/// DataTable导出到Excel文件
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="file"></param>
|
public static void ExportExcel(DataTable dt, string file)
|
{
|
IWorkbook workbook;
|
string fileExt = Path.GetExtension(file).ToLower();
|
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
|
if (workbook == null) { return; }
|
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
|
|
//表头
|
IRow row = sheet.CreateRow(0);
|
for (int i = 0; i < dt.Columns.Count; i++)
|
{
|
ICell cell = row.CreateCell(i);
|
cell.SetCellValue(dt.Columns[i].ColumnName);
|
}
|
|
//数据
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
IRow row1 = sheet.CreateRow(i + 1);
|
for (int j = 0; j < dt.Columns.Count; j++)
|
{
|
ICell cell = row1.CreateCell(j);
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
}
|
|
//转为字节数组
|
MemoryStream stream = new MemoryStream();
|
workbook.Write(stream);
|
var buf = stream.ToArray();
|
|
//保存为Excel文件
|
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
|
{
|
fs.Write(buf, 0, buf.Length);
|
fs.Flush();
|
}
|
}
|
/// <summary>
|
/// 公司导出到Excel文件
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="file"></param>
|
public static void ExportExcelCompany(DataSet dt, string file)
|
{
|
IWorkbook workbook;
|
string fileExt = Path.GetExtension(file).ToLower();
|
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
|
if (workbook == null) { return; }
|
for (int z = 0; z < dt.Tables.Count; z++)
|
{
|
ISheet sheet = string.IsNullOrEmpty(dt.Tables[z].TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.Tables[z].TableName);
|
|
//表头
|
IRow row = sheet.CreateRow(0);
|
for (int i = 0; i < dt.Tables[z].Columns.Count; i++)
|
{
|
ICell cell = row.CreateCell(i);
|
cell.SetCellValue(dt.Tables[z].Columns[i].ColumnName);
|
}
|
|
//数据
|
for (int i = 0; i < dt.Tables[z].Rows.Count; i++)
|
{
|
IRow row1 = sheet.CreateRow(i + 1);
|
for (int j = 0; j < dt.Tables[z].Columns.Count; j++)
|
{
|
ICell cell = row1.CreateCell(j);
|
cell.SetCellValue(dt.Tables[z].Rows[i][j].ToString());
|
}
|
}
|
|
}
|
|
//转为字节数组
|
MemoryStream stream = new MemoryStream();
|
workbook.Write(stream);
|
var buf = stream.ToArray();
|
|
//保存为Excel文件
|
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
|
{
|
fs.Write(buf, 0, buf.Length);
|
fs.Flush();
|
}
|
}
|
/// <summary>
|
/// DataTable导出到Excel文件
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="file"></param>
|
public static void FRMExportExcel(DataTable dt, string file, string title, bool IsShow)
|
{
|
if (IsShow)
|
{
|
IWorkbook workbook;
|
string fileExt = Path.GetExtension(file).ToLower();
|
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
|
if (workbook == null) { return; }
|
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
|
|
//表头
|
IRow row = sheet.CreateRow(0);
|
row.Height = 25 * 20; //行高为30
|
// sheet.SetColumnWidth(0, 13 * 256);
|
CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1);
|
sheet.SetColumnWidth(0, 30 * 256);
|
sheet.AddMergedRegion(region);
|
#region 标题样式
|
ICellStyle style = workbook.CreateCellStyle();//创建样式对象
|
IFont font = workbook.CreateFont(); //创建一个字体样式对象
|
font.FontName = "微软雅黑"; //和excel里面的字体对应
|
// font.Color = new HSSFColor.Pink().GetIndex();//颜色参考NPOI的颜色对照表(替换掉PINK())
|
font.IsItalic = false; //斜体
|
font.FontHeightInPoints = 14;//字体大小
|
font.Boldweight = short.MaxValue;//字体加粗
|
style.SetFont(font); //将字体样式赋给样式对象
|
style.BorderTop = BorderStyle.Thin;
|
style.BorderBottom = BorderStyle.Thin;
|
style.BorderLeft = BorderStyle.Thin;
|
style.BorderRight = BorderStyle.Thin;
|
style.WrapText = true;//设置自动换行
|
style.Alignment = HorizontalAlignment.Center;
|
#endregion
|
#region 内容样式 1 右对齐 千位符保留两位小数
|
ICellStyle style1 = workbook.CreateCellStyle();
|
IFont font1 = workbook.CreateFont();
|
var format1 = workbook.CreateDataFormat();
|
font1.FontName = "微软雅黑";
|
font1.IsItalic = false;
|
font1.FontHeightInPoints = 12;
|
font1.Boldweight = short.MaxValue;
|
style1.SetFont(font1);
|
style1.DataFormat = format1.GetFormat("#,##0.00");
|
style1.BorderTop = BorderStyle.Thin;
|
style1.BorderBottom = BorderStyle.Thin;
|
style1.BorderLeft = BorderStyle.Thin;
|
style1.BorderRight = BorderStyle.Thin;
|
style1.WrapText = true;//设置自动换行
|
style1.Alignment = HorizontalAlignment.Right;
|
#endregion
|
|
#region 内容样式2 左对齐
|
ICellStyle style2 = workbook.CreateCellStyle();
|
IFont font2 = workbook.CreateFont();
|
font2.FontName = "微软雅黑";
|
font2.IsItalic = false;
|
font2.FontHeightInPoints = 12;
|
font2.Boldweight = short.MaxValue;
|
style2.SetFont(font2);
|
style2.BorderTop = BorderStyle.Thin;
|
style2.BorderBottom = BorderStyle.Thin;
|
style2.BorderLeft = BorderStyle.Thin;
|
style2.BorderRight = BorderStyle.Thin;
|
style2.WrapText = true;
|
style2.Alignment = HorizontalAlignment.Left;
|
#endregion
|
#region 内容样式3 左对齐 加粗
|
ICellStyle style3 = workbook.CreateCellStyle();
|
IFont font3 = workbook.CreateFont();
|
font3.FontName = "微软雅黑";
|
font3.IsItalic = false;
|
font3.FontHeightInPoints = 12;
|
font3.Boldweight = (short)FontBoldWeight.Bold;
|
style3.SetFont(font3);
|
style3.BorderTop = BorderStyle.Thin;
|
style3.BorderBottom = BorderStyle.Thin;
|
style3.BorderLeft = BorderStyle.Thin;
|
style3.BorderRight = BorderStyle.Thin;
|
style3.WrapText = true;
|
style3.Alignment = HorizontalAlignment.Left;
|
#endregion
|
#region 内容样式4 右对齐 加粗 千位符 保留两位小数
|
ICellStyle style4 = workbook.CreateCellStyle();
|
IFont font4 = workbook.CreateFont();
|
var format4 = workbook.CreateDataFormat();
|
font4.FontName = "微软雅黑";
|
font4.IsItalic = false;
|
font4.FontHeightInPoints = 12;
|
font4.Boldweight = (short)FontBoldWeight.Bold;
|
style4.SetFont(font4);
|
style4.DataFormat = format4.GetFormat("#,##0.00");
|
style4.BorderTop = BorderStyle.Thin;
|
style4.BorderBottom = BorderStyle.Thin;
|
style4.BorderLeft = BorderStyle.Thin;
|
style4.BorderRight = BorderStyle.Thin;
|
style4.WrapText = true;
|
style4.Alignment = HorizontalAlignment.Right;
|
#endregion
|
#region 内容样式 5 右对齐
|
ICellStyle style5 = workbook.CreateCellStyle();
|
IFont font5 = workbook.CreateFont();
|
font5.FontName = "微软雅黑";
|
font5.IsItalic = false;
|
font5.FontHeightInPoints = 12;
|
font5.Boldweight = short.MaxValue;
|
style5.SetFont(font5);
|
style5.BorderTop = BorderStyle.Thin;
|
style5.BorderBottom = BorderStyle.Thin;
|
style5.BorderLeft = BorderStyle.Thin;
|
style5.BorderRight = BorderStyle.Thin;
|
style5.WrapText = true;//设置自动换行
|
style5.Alignment = HorizontalAlignment.Right;
|
#endregion
|
ICell cellt = row.CreateCell(0);
|
cellt.CellStyle = style; //把样式赋给单元格
|
cellt.SetCellValue(title);
|
|
//数据
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
IRow row1 = sheet.CreateRow(i + 1);
|
if (row1 == null)
|
{
|
continue;
|
}
|
|
int OldHg = row1.Height;
|
for (int z = 0; z < dt.Columns.Count; z++)
|
{
|
if (row1.GetCell(z) == null)
|
{
|
continue;
|
}
|
ICell CurCell = row1.GetCell(z);
|
if (CurCell.CellType == NPOI.SS.UserModel.CellType.String && CurCell.StringCellValue != string.Empty)
|
{
|
double CurHeight = row1.Sheet.GetColumnWidth(CurCell.ColumnIndex) / 277;
|
double length = Encoding.Default.GetBytes(CurCell.ToString()).Length;
|
short height = Convert.ToInt16(Math.Ceiling(length / CurHeight));
|
if (height * 256 > OldHg)
|
{
|
OldHg = height * 256;
|
}
|
}
|
}
|
if (row1.Height < OldHg)
|
{
|
row1.Height = Convert.ToInt16(OldHg);
|
}
|
sheet.SetColumnWidth(i + 1, 25 * 256);
|
bool FontFlag = false;
|
//row1.Height = 36 * 20;
|
for (int j = 0; j < dt.Columns.Count; j++)
|
{
|
|
dt.Rows[i][j] = dt.Rows[i][j].ToString().Replace("wqeewqsdad", "").Replace("--------", "*").Replace("木部", "本部").Replace("贷币", "货币");
|
if (i == 0)
|
{
|
if (dt.Rows[i][j].ToString() == "" || dt.Rows[i][j].ToString().Length > 5)
|
{
|
if (j == 0)
|
{
|
dt.Rows[i][j] = "项目";
|
}
|
}
|
}
|
if (dt.Columns.Count == 4 && i == 0)
|
{
|
if (dt.Rows[i][j].ToString() == "")
|
{
|
if (j == 0)
|
{
|
dt.Rows[i][j] = "项目";
|
}
|
if (j == 2)
|
{
|
dt.Rows[i][j] = "本年金额";
|
}
|
if (j == 3)
|
{
|
dt.Rows[i][j] = "上年金额";
|
}
|
}
|
}
|
|
ICell cell = row1.CreateCell(j);
|
if (j == 0)
|
{
|
if ((dt.Rows[i][j].ToString().Contains("流动资产") && dt.Rows[i][j].ToString().Length < 6) || (dt.Rows[i][j].ToString().Contains("非流动资产")) && dt.Rows[i][j].ToString().Length < 7 || dt.Rows[i][j].ToString().Contains("合计") || dt.Rows[i][j].ToString().Contains("总计"))
|
{
|
FontFlag = true;
|
cell.CellStyle = style3;
|
}
|
else
|
{
|
cell.CellStyle = style2;
|
}
|
|
}
|
else
|
{
|
if (FontFlag)
|
{
|
cell.CellStyle = style4;
|
}
|
else
|
{
|
cell.CellStyle = style1;
|
}
|
|
}
|
double Money = -1;
|
if (double.TryParse(dt.Rows[i][j].ToString(), out Money))
|
{
|
cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
|
}
|
else
|
{
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
|
}
|
}
|
|
//转为字节数组
|
MemoryStream stream = new MemoryStream();
|
workbook.Write(stream);
|
var buf = stream.ToArray();
|
|
//保存为Excel文件
|
//if (System.IO.Directory.Exists(file) == false)
|
//{
|
// System.IO.Directory.CreateDirectory(file);
|
//}
|
|
using (FileStream fs = new FileStream(file, FileMode.OpenOrCreate, FileAccess.Write))
|
{
|
fs.Write(buf, 0, buf.Length);
|
fs.Flush();
|
}
|
}
|
else
|
{
|
IWorkbook workbook;
|
string fileExt = Path.GetExtension(file).ToLower();
|
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
|
if (workbook == null) { return; }
|
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
|
|
//表头
|
IRow row = sheet.CreateRow(0);
|
row.Height = 25 * 20; //行高为30
|
// sheet.SetColumnWidth(0, 13 * 256);
|
CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1);
|
sheet.SetColumnWidth(0, 30 * 256);
|
sheet.AddMergedRegion(region);
|
#region 标题样式
|
ICellStyle style = workbook.CreateCellStyle();//创建样式对象
|
IFont font = workbook.CreateFont(); //创建一个字体样式对象
|
font.FontName = "微软雅黑"; //和excel里面的字体对应
|
// font.Color = new HSSFColor.Pink().GetIndex();//颜色参考NPOI的颜色对照表(替换掉PINK())
|
font.IsItalic = false; //斜体
|
font.FontHeightInPoints = 14;//字体大小
|
font.Boldweight = short.MaxValue;//字体加粗
|
style.SetFont(font); //将字体样式赋给样式对象
|
style.BorderTop = BorderStyle.Thin;
|
style.BorderBottom = BorderStyle.Thin;
|
style.BorderLeft = BorderStyle.Thin;
|
style.BorderRight = BorderStyle.Thin;
|
style.WrapText = true;//设置自动换行
|
style.Alignment = HorizontalAlignment.Center;
|
#endregion
|
#region 内容样式 1 右对齐 千位符保留两位小数
|
ICellStyle style1 = workbook.CreateCellStyle();
|
IFont font1 = workbook.CreateFont();
|
var format1 = workbook.CreateDataFormat();
|
font1.FontName = "微软雅黑";
|
font1.IsItalic = false;
|
font1.FontHeightInPoints = 12;
|
font1.Boldweight = short.MaxValue;
|
style1.SetFont(font1);
|
//style1.DataFormat = format1.GetFormat("#,##0.00");
|
style1.BorderTop = BorderStyle.Thin;
|
style1.BorderBottom = BorderStyle.Thin;
|
style1.BorderLeft = BorderStyle.Thin;
|
style1.BorderRight = BorderStyle.Thin;
|
style1.WrapText = true;//设置自动换行
|
style1.Alignment = HorizontalAlignment.Right;
|
#endregion
|
|
#region 内容样式2 左对齐
|
ICellStyle style2 = workbook.CreateCellStyle();
|
IFont font2 = workbook.CreateFont();
|
font2.FontName = "微软雅黑";
|
font2.IsItalic = false;
|
font2.FontHeightInPoints = 12;
|
font2.Boldweight = short.MaxValue;
|
style2.SetFont(font2);
|
style2.BorderTop = BorderStyle.Thin;
|
style2.BorderBottom = BorderStyle.Thin;
|
style2.BorderLeft = BorderStyle.Thin;
|
style2.BorderRight = BorderStyle.Thin;
|
style2.WrapText = true;
|
style2.Alignment = HorizontalAlignment.Left;
|
#endregion
|
#region 内容样式3 左对齐 加粗
|
ICellStyle style3 = workbook.CreateCellStyle();
|
IFont font3 = workbook.CreateFont();
|
font3.FontName = "微软雅黑";
|
font3.IsItalic = false;
|
font3.FontHeightInPoints = 12;
|
font3.Boldweight = (short)FontBoldWeight.Bold;
|
style3.SetFont(font3);
|
style3.BorderTop = BorderStyle.Thin;
|
style3.BorderBottom = BorderStyle.Thin;
|
style3.BorderLeft = BorderStyle.Thin;
|
style3.BorderRight = BorderStyle.Thin;
|
style3.WrapText = true;
|
style3.Alignment = HorizontalAlignment.Left;
|
#endregion
|
#region 内容样式4 右对齐 加粗 千位符 保留两位小数
|
ICellStyle style4 = workbook.CreateCellStyle();
|
IFont font4 = workbook.CreateFont();
|
var format4 = workbook.CreateDataFormat();
|
font4.FontName = "微软雅黑";
|
font4.IsItalic = false;
|
font4.FontHeightInPoints = 12;
|
font4.Boldweight = (short)FontBoldWeight.Bold;
|
style4.SetFont(font4);
|
// style4.DataFormat = format4.GetFormat("#,##0.00");
|
style4.BorderTop = BorderStyle.Thin;
|
style4.BorderBottom = BorderStyle.Thin;
|
style4.BorderLeft = BorderStyle.Thin;
|
style4.BorderRight = BorderStyle.Thin;
|
style4.WrapText = true;
|
style4.Alignment = HorizontalAlignment.Right;
|
#endregion
|
#region 内容样式 5 右对齐
|
ICellStyle style5 = workbook.CreateCellStyle();
|
IFont font5 = workbook.CreateFont();
|
font5.FontName = "微软雅黑";
|
font5.IsItalic = false;
|
font5.FontHeightInPoints = 12;
|
font5.Boldweight = short.MaxValue;
|
style5.SetFont(font5);
|
style5.BorderTop = BorderStyle.Thin;
|
style5.BorderBottom = BorderStyle.Thin;
|
style5.BorderLeft = BorderStyle.Thin;
|
style5.BorderRight = BorderStyle.Thin;
|
style5.WrapText = true;//设置自动换行
|
style5.Alignment = HorizontalAlignment.Right;
|
#endregion
|
ICell cellt = row.CreateCell(0);
|
cellt.CellStyle = style; //把样式赋给单元格
|
cellt.SetCellValue(title);
|
|
//数据
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
IRow row1 = sheet.CreateRow(i + 1);
|
if (row1 == null)
|
{
|
continue;
|
}
|
|
int OldHg = row1.Height;
|
for (int z = 0; z < dt.Columns.Count; z++)
|
{
|
if (row1.GetCell(z) == null)
|
{
|
continue;
|
}
|
ICell CurCell = row1.GetCell(z);
|
if (CurCell.CellType == NPOI.SS.UserModel.CellType.String && CurCell.StringCellValue != string.Empty)
|
{
|
double CurHeight = row1.Sheet.GetColumnWidth(CurCell.ColumnIndex) / 277;
|
double length = Encoding.Default.GetBytes(CurCell.ToString()).Length;
|
short height = Convert.ToInt16(Math.Ceiling(length / CurHeight));
|
if (height * 256 > OldHg)
|
{
|
OldHg = height * 256;
|
}
|
}
|
}
|
if (row1.Height < OldHg)
|
{
|
row1.Height = Convert.ToInt16(OldHg);
|
}
|
sheet.SetColumnWidth(i + 1, 25 * 256);
|
bool FontFlag = false;
|
//row1.Height = 36 * 20;
|
for (int j = 0; j < dt.Columns.Count; j++)
|
{
|
|
dt.Rows[i][j] = dt.Rows[i][j].ToString().Replace("wqeewqsdad", "").Replace("--------", "*").Replace("木部", "本部").Replace("贷币", "货币");
|
if (i == 0)
|
{
|
if (dt.Rows[i][j].ToString() == "" || dt.Rows[i][j].ToString().Length > 5)
|
{
|
if (j == 0)
|
{
|
dt.Rows[i][j] = "项目";
|
}
|
}
|
}
|
if (dt.Columns.Count == 4 && i == 0)
|
{
|
if (dt.Rows[i][j].ToString() == "")
|
{
|
if (j == 0)
|
{
|
dt.Rows[i][j] = "项目";
|
}
|
if (j == 2)
|
{
|
dt.Rows[i][j] = "本年金额";
|
}
|
if (j == 3)
|
{
|
dt.Rows[i][j] = "上年金额";
|
}
|
}
|
}
|
|
ICell cell = row1.CreateCell(j);
|
if (j == 0)
|
{
|
if ((dt.Rows[i][j].ToString().Contains("流动资产") && dt.Rows[i][j].ToString().Length < 6) || (dt.Rows[i][j].ToString().Contains("非流动资产")) && dt.Rows[i][j].ToString().Length < 7 || dt.Rows[i][j].ToString().Contains("合计") || dt.Rows[i][j].ToString().Contains("总计"))
|
{
|
FontFlag = true;
|
cell.CellStyle = style3;
|
}
|
else
|
{
|
cell.CellStyle = style2;
|
}
|
|
}
|
else
|
{
|
if (FontFlag)
|
{
|
cell.CellStyle = style4;
|
}
|
else
|
{
|
cell.CellStyle = style1;
|
}
|
|
}
|
double Money = -1;
|
if (double.TryParse(dt.Rows[i][j].ToString(), out Money))
|
{
|
cell.SetCellValue(Convert.ToDouble(dt.Rows[i][j].ToString()).ToString("0.00"));
|
}
|
else
|
{
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
|
}
|
}
|
|
//转为字节数组
|
MemoryStream stream = new MemoryStream();
|
workbook.Write(stream);
|
var buf = stream.ToArray();
|
|
//保存为Excel文件
|
//if (System.IO.Directory.Exists(file) == false)
|
//{
|
// System.IO.Directory.CreateDirectory(file);
|
//}
|
|
using (FileStream fs = new FileStream(file, FileMode.OpenOrCreate, FileAccess.Write))
|
{
|
fs.Write(buf, 0, buf.Length);
|
fs.Flush();
|
}
|
}
|
}
|
|
/// <summary>
|
/// DataTable导出到Excel文件
|
/// </summary>
|
/// <param name="dtSource">源DataTable</param>
|
/// <param name="strHeaderText">表头文本</param>
|
/// <param name="strFileName">保存位置</param>
|
/// <param name="strSheetName">工作表名称</param>
|
/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
|
public static void Export(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName,
|
string[] oldColumnNames, string[] newColumnNames)
|
{
|
if (strSheetName == "")
|
{
|
strSheetName = "Sheet";
|
}
|
|
using (MemoryStream ms = Export(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames))
|
{
|
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
|
{
|
byte[] data = ms.ToArray();
|
fs.Write(data, 0, data.Length);
|
fs.Flush();
|
}
|
}
|
}
|
|
/// <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();
|
FillWorkbook(ref workbook, dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames);
|
|
using (MemoryStream ms = new MemoryStream())
|
{
|
workbook.Write(ms);
|
ms.Flush();
|
ms.Position = 0;
|
|
workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
|
return ms;
|
}
|
}
|
|
/// <summary>
|
/// DataSet导出到Excel的MemoryStream
|
/// </summary>
|
/// <param name="dtSource">源DataSet</param>
|
public static MemoryStream ExportDataSet(DataSet dtSource)
|
{
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
var index = 0;
|
foreach (DataTable dt in dtSource.Tables)
|
{
|
index++;
|
//生成列
|
string columns = "";
|
for (int i = 0; i < dt.Columns.Count; i++)
|
{
|
if (i > 0)
|
{
|
columns += ",";
|
}
|
|
columns += dt.Columns[i].ColumnName;
|
}
|
|
var sheetname = string.IsNullOrWhiteSpace(dt.TableName) ? ("Sheet" + index) : dt.TableName;
|
FillWorkbook(ref workbook, dt, dt.TableName, sheetname, columns.Split(','), columns.Split(','));
|
}
|
|
using (MemoryStream ms = new MemoryStream())
|
{
|
workbook.Write(ms);
|
ms.Flush();
|
ms.Position = 0;
|
workbook = null;
|
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
|
return ms;
|
}
|
}
|
|
private static void FillWorkbook(ref HSSFWorkbook workbook, DataTable dtSource, string strHeaderText, string strSheetName, string[] oldColumnNames,
|
string[] newColumnNames)
|
{
|
if (oldColumnNames.Length != newColumnNames.Length)
|
{
|
return;
|
}
|
ISheet sheet = workbook.CreateSheet(strSheetName);
|
|
#region 右击文件 属性信息
|
|
//{
|
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
|
// 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文件作者信息
|
// si.Title = strHeaderText; //填加xls文件标题信息
|
// si.Subject = strHeaderText; //填加文件主题信息
|
// si.CreateDateTime = DateTime.Now;
|
// workbook.SummaryInformation = si;
|
//}
|
|
#endregion
|
|
ICellStyle dateStyle = workbook.CreateCellStyle();
|
IDataFormat format = workbook.CreateDataFormat();
|
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
|
|
#region 取得列宽
|
|
int[] arrColWidth = new int[oldColumnNames.Length];
|
for (int i = 0; i < oldColumnNames.Length; i++)
|
{
|
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
|
}
|
/*
|
foreach (DataColumn item in dtSource.Columns)
|
{
|
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
|
}
|
* */
|
|
for (int i = 0; i < dtSource.Rows.Count; i++)
|
{
|
for (int j = 0; j < oldColumnNames.Length; j++)
|
{
|
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString())
|
.Length;
|
if (intTemp > arrColWidth[j])
|
{
|
arrColWidth[j] = intTemp;
|
}
|
}
|
|
/*
|
for (int j = 0; j < dtSource.Columns.Count; j++)
|
{
|
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
|
if (intTemp > arrColWidth[j])
|
{
|
arrColWidth[j] = intTemp;
|
}
|
}
|
* */
|
}
|
|
#endregion
|
|
int rowIndex = 0;
|
|
foreach (DataRow row in dtSource.Rows)
|
{
|
#region 新建表，填充表头，填充列头，样式
|
|
if (rowIndex == 65535 || rowIndex == 0)
|
{
|
if (rowIndex != 0)
|
{
|
sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
|
}
|
|
#region 表头及样式
|
|
{
|
IRow headerRow = sheet.CreateRow(0);
|
headerRow.HeightInPoints = 25;
|
headerRow.CreateCell(0).SetCellValue(strHeaderText);
|
|
ICellStyle headStyle = workbook.CreateCellStyle();
|
headStyle.Alignment = HorizontalAlignment.Center;
|
IFont font = workbook.CreateFont();
|
font.FontHeightInPoints = 20;
|
font.Boldweight = 700;
|
headStyle.SetFont(font);
|
|
headerRow.GetCell(0).CellStyle = headStyle;
|
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
|
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
|
}
|
|
#endregion
|
|
|
#region 列头及样式
|
|
{
|
//HSSFRow headerRow = sheet.CreateRow(1);
|
IRow headerRow = sheet.CreateRow(1);
|
|
ICellStyle headStyle = workbook.CreateCellStyle();
|
headStyle.Alignment = HorizontalAlignment.Center;
|
IFont font = workbook.CreateFont();
|
font.FontHeightInPoints = 10;
|
font.Boldweight = 700;
|
headStyle.SetFont(font);
|
|
for (int i = 0; i < oldColumnNames.Length; i++)
|
{
|
headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
|
headerRow.GetCell(i).CellStyle = headStyle;
|
//设置列宽
|
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
|
}
|
|
/*
|
foreach (DataColumn column in dtSource.Columns)
|
{
|
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
|
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
|
|
//设置列宽
|
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
|
}
|
* */
|
}
|
|
#endregion
|
|
rowIndex = 2;
|
}
|
|
#endregion
|
|
|
#region 填充内容
|
|
IRow dataRow = sheet.CreateRow(rowIndex);
|
//foreach (DataColumn column in dtSource.Columns)
|
for (int i = 0; i < oldColumnNames.Length; i++)
|
{
|
ICell newCell = dataRow.CreateCell(i);
|
|
string drValue = row[oldColumnNames[i]].ToString();
|
|
switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
|
{
|
case "System.String": //字符串类型
|
newCell.SetCellValue(drValue);
|
break;
|
case "System.DateTime": //日期类型
|
DateTime dateV;
|
DateTime.TryParse(drValue, out dateV);
|
newCell.SetCellValue(dateV);
|
|
newCell.CellStyle = dateStyle; //格式化显示
|
break;
|
case "System.Boolean": //布尔型
|
bool boolV = false;
|
bool.TryParse(drValue, out boolV);
|
newCell.SetCellValue(boolV);
|
break;
|
case "System.Int16": //整型
|
case "System.Int32":
|
case "System.Int64":
|
case "System.Byte":
|
int intV = 0;
|
int.TryParse(drValue, out intV);
|
newCell.SetCellValue(intV);
|
break;
|
case "System.Decimal": //浮点型
|
case "System.Double":
|
double doubV = 0;
|
double.TryParse(drValue, out doubV);
|
newCell.SetCellValue(doubV);
|
break;
|
case "System.DBNull": //空值处理
|
newCell.SetCellValue("");
|
break;
|
default:
|
newCell.SetCellValue("");
|
break;
|
}
|
|
}
|
|
#endregion
|
|
rowIndex++;
|
}
|
}
|
|
/// <summary>
|
/// WEB导出DataTable到Excel
|
/// </summary>
|
/// <param name="dtSource">源DataTable</param>
|
/// <param name="strHeaderText">表头文本</param>
|
/// <param name="strFileName">文件名</param>
|
/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
|
public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
|
{
|
ExportByWeb(dtSource, strHeaderText, strFileName, "sheet");
|
}
|
|
/// <summary>
|
/// WEB导出DataTable到Excel
|
/// </summary>
|
/// <param name="dtSource">源DataTable</param>
|
/// <param name="strHeaderText">表头文本</param>
|
/// <param name="strFileName">输出文件名，包含扩展名</param>
|
/// <param name="oldColumnNames">要导出的DataTable列数组</param>
|
/// <param name="newColumnNames">导出后的对应列名</param>
|
public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName,
|
string[] oldColumnNames, string[] newColumnNames)
|
{
|
ExportByWeb(dtSource, strHeaderText, strFileName, "sheet", oldColumnNames, newColumnNames);
|
}
|
|
/// <summary>
|
/// WEB导出DataTable到Excel
|
/// </summary>
|
/// <param name="dtSource">源DataTable</param>
|
/// <param name="strHeaderText">表头文本</param>
|
/// <param name="strFileName">输出文件名</param>
|
/// <param name="strSheetName">工作表名称</param>
|
public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName,
|
string strSheetName)
|
{
|
HttpContext curContext = HttpContext.Current;
|
|
// 设置编码和附件格式
|
curContext.Response.ContentType = "application/octet-stream";
|
curContext.Response.ContentEncoding = Encoding.UTF8;
|
curContext.Response.Charset = "";
|
curContext.Response.AppendHeader("Content-Disposition",
|
"attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
|
|
//生成列
|
string columns = "";
|
for (int i = 0; i < dtSource.Columns.Count; i++)
|
{
|
if (i > 0)
|
{
|
columns += ",";
|
}
|
|
columns += dtSource.Columns[i].ColumnName;
|
}
|
|
curContext.Response.BinaryWrite(
|
Export(dtSource, strHeaderText, strSheetName, columns.Split(','), columns.Split(',')).GetBuffer());
|
curContext.Response.End();
|
|
}
|
|
public static void ExportByWeb(DataSet dtSource, string strFileName)
|
{
|
HttpContext curContext = HttpContext.Current;
|
|
// 设置编码和附件格式
|
curContext.Response.ContentType = "application/octet-stream";
|
curContext.Response.ContentEncoding = Encoding.UTF8;
|
curContext.Response.Charset = "";
|
curContext.Response.AppendHeader("Content-Disposition",
|
"attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
|
curContext.Response.BinaryWrite(
|
ExportDataSet(dtSource).GetBuffer());
|
curContext.Response.End();
|
}
|
|
/// <summary>
|
/// 导出DataTable到Excel
|
/// </summary>
|
/// <param name="dtSource">要导出的DataTable</param>
|
/// <param name="strHeaderText">标题文字</param>
|
/// <param name="strFileName">文件名，包含扩展名</param>
|
/// <param name="strSheetName">工作表名</param>
|
/// <param name="oldColumnNames">要导出的DataTable列数组</param>
|
/// <param name="newColumnNames">导出后的对应列名</param>
|
public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName,
|
string strSheetName, string[] oldColumnNames, string[] newColumnNames)
|
{
|
HttpContext curContext = HttpContext.Current;
|
|
// 设置编码和附件格式
|
curContext.Response.ContentType = "application/vnd.ms-excel";
|
curContext.Response.ContentEncoding = Encoding.UTF8;
|
curContext.Response.Charset = "";
|
curContext.Response.AppendHeader("Content-Disposition",
|
"attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
|
|
curContext.Response.BinaryWrite(
|
Export(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames).GetBuffer());
|
curContext.Response.End();
|
}
|
|
/// <summary>读取excel
|
/// 默认第一行为表头，导入第一个工作表
|
/// </summary>
|
/// <param name="strFileName">excel文档路径</param>
|
/// <returns></returns>
|
public static DataTable Import(string strFileName)
|
{
|
//int m = 0;
|
try
|
{
|
DataTable dt = new DataTable();
|
|
HSSFWorkbook hssfworkbook;
|
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
|
{
|
hssfworkbook = new HSSFWorkbook(file);
|
}
|
|
ISheet sheet = hssfworkbook.GetSheetAt(0);
|
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
|
|
IRow headerRow = sheet.GetRow(0);
|
int cellCount = headerRow.LastCellNum;
|
|
for (int j = 0; j < cellCount; j++)
|
{
|
//m = j;
|
ICell cell = headerRow.GetCell(j);
|
dt.Columns.Add(cell.ToString());
|
}
|
|
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
|
{
|
//if (i == 3)
|
//{
|
// string mm = "";
|
//}
|
IRow row = sheet.GetRow(i);
|
if (row != null)
|
{
|
DataRow dataRow = dt.NewRow();
|
for (int j = row.FirstCellNum; j < cellCount; j++)
|
{
|
if (row.GetCell(j) != null)
|
dataRow[j] = row.GetCell(j).ToString();
|
}
|
|
dt.Rows.Add(dataRow);
|
}
|
}
|
|
return dt;
|
}
|
catch (Exception e)
|
{
|
//Console.WriteLine(m.ToString());
|
return null;
|
}
|
|
}
|
|
/// <summary>
|
/// 从Excel中获取数据到DataTable
|
/// </summary>
|
/// <param name="strFileName">Excel文件全路径(服务器路径)</param>
|
/// <param name="SheetName">要获取数据的工作表名称</param>
|
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
|
/// <returns></returns>
|
public static DataTable RenderDataTableFromExcel(string strFileName, string SheetName, int HeaderRowIndex)
|
{
|
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
|
{
|
IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(file);
|
return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
|
}
|
}
|
|
/// <summary>
|
/// 从Excel中获取数据到DataTable
|
/// </summary>
|
/// <param name="strFileName">Excel文件全路径(服务器路径)</param>
|
/// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
|
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
|
/// <returns></returns>
|
public static DataTable RenderDataTableFromExcel(string strFileName, int SheetIndex, int HeaderRowIndex)
|
{
|
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
|
{
|
IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(file);
|
string SheetName = workbook.GetSheetName(SheetIndex);
|
return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
|
}
|
}
|
|
/// <summary>
|
/// 从Excel中获取数据到DataTable
|
/// </summary>
|
/// <param name="ExcelFileStream">Excel文件流</param>
|
/// <param name="SheetName">要获取数据的工作表名称</param>
|
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
|
/// <returns></returns>
|
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
|
{
|
IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
|
ExcelFileStream.Close();
|
return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
|
}
|
|
/// <summary>
|
/// 从Excel中获取数据到DataTable
|
/// </summary>
|
/// <param name="ExcelFileStream">Excel文件流</param>
|
/// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
|
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
|
/// <returns></returns>
|
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
|
{
|
IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
|
ExcelFileStream.Close();
|
string SheetName = workbook.GetSheetName(SheetIndex);
|
return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
|
}
|
/// <summary>
|
/// 从Excel中获取数据到DataTable(针对公司)
|
/// </summary>
|
/// <param name="ExcelFileStream">Excel文件流</param>
|
/// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
|
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
|
/// <returns></returns>
|
public static DataTable RenderDataTableFromExcelCompany(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex = 0)
|
{
|
IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
|
ExcelFileStream.Close();
|
string SheetName = workbook.GetSheetName(SheetIndex);
|
return RenderDataTableFromExcelReport(workbook, SheetName, HeaderRowIndex, true);
|
}
|
|
/// <summary>
|
/// 从Excel中获取数据到DataTable
|
/// </summary>
|
/// <param name="workbook">要处理的工作薄</param>
|
/// <param name="SheetName">要获取数据的工作表名称</param>
|
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
|
/// <returns></returns>
|
public static DataTable RenderDataTableFromExcel(IWorkbook workbook, string SheetName, int HeaderRowIndex)
|
{
|
ISheet sheet = workbook.GetSheet(SheetName);
|
DataTable table = new DataTable();
|
try
|
{
|
IRow headerRow = sheet.GetRow(HeaderRowIndex);
|
int cellCount = headerRow.LastCellNum;
|
|
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
|
{
|
//headerRow.GetCell(i).SetCellType(CellType.String);
|
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
|
table.Columns.Add(column);
|
}
|
|
//int rowCount = sheet.LastRowNum;
|
|
#region 循环各行各列,写入数据到DataTable
|
|
for (int i = (sheet.FirstRowNum + 2); i < sheet.LastRowNum + 1; i++)
|
{
|
IRow row = sheet.GetRow(i);
|
DataRow dataRow = table.NewRow();
|
for (int j = row.FirstCellNum; j < cellCount; j++)
|
{
|
ICell cell = row.GetCell(j);
|
if (cell == null)
|
{
|
dataRow[j] = null;
|
}
|
else
|
{
|
//dataRow[j] = cell.ToString();
|
switch (cell.CellType)
|
{
|
case CellType.Blank:
|
dataRow[j] = null;
|
break;
|
case CellType.Boolean:
|
dataRow[j] = cell.BooleanCellValue;
|
break;
|
case CellType.Numeric:
|
if (DateUtil.IsCellDateFormatted(cell))
|
{
|
dataRow[j] = cell.DateCellValue.ToString();
|
}
|
else
|
{
|
dataRow[j] = cell.ToString();
|
}
|
break;
|
case CellType.String:
|
dataRow[j] = cell.StringCellValue;
|
break;
|
case CellType.Error:
|
dataRow[j] = cell.ErrorCellValue;
|
break;
|
case CellType.Formula:
|
cell.SetCellType(CellType.String);
|
dataRow[j] = cell.StringCellValue;
|
break;
|
default:
|
dataRow[j] = "=" + cell.CellFormula;
|
break;
|
}
|
}
|
}
|
|
table.Rows.Add(dataRow);
|
//dataRow[j] = row.GetCell(j).ToString();
|
}
|
|
#endregion
|
}
|
catch (System.Exception ex)
|
{
|
table.Clear();
|
table.Columns.Clear();
|
table.Columns.Add("出错了");
|
DataRow dr = table.NewRow();
|
dr[0] = ex.Message;
|
table.Rows.Add(dr);
|
return table;
|
}
|
finally
|
{
|
//sheet.Dispose();
|
workbook = null;
|
sheet = null;
|
}
|
|
#region 清除最后的空行
|
|
for (int i = table.Rows.Count - 1; i > 0; i--)
|
{
|
bool isnull = true;
|
for (int j = 0; j < table.Columns.Count; j++)
|
{
|
if (table.Rows[i][j] != null)
|
{
|
if (table.Rows[i][j].ToString() != "")
|
{
|
isnull = false;
|
break;
|
}
|
}
|
}
|
|
if (isnull)
|
{
|
table.Rows[i].Delete();
|
}
|
else
|
{
|
break;
|
}
|
}
|
|
|
|
|
|
|
#endregion
|
|
return table;
|
}
|
#region 报告
|
/// <summary>
|
/// 从Excel中获取数据到DataTable
|
/// </summary>
|
/// <param name="workbook">要处理的工作薄</param>
|
/// <param name="SheetName">要获取数据的工作表名称</param>
|
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
|
/// <returns></returns>
|
public static DataTable RenderDataTableFromExcelReport(IWorkbook workbook, string SheetName, int HeaderRowIndex, bool CompanyFlag = false)
|
{
|
ISheet sheet = workbook.GetSheet(SheetName);
|
DataTable table = new DataTable();
|
int Cou = 0;
|
try
|
{
|
IRow headerRow = sheet.GetRow(HeaderRowIndex);
|
int cellCount = headerRow.LastCellNum;
|
|
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
|
{
|
//headerRow.GetCell(i).SetCellType(CellType.String);
|
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
|
table.Columns.Add(column);
|
}
|
|
//int rowCount = sheet.LastRowNum;
|
|
#region 循环各行各列,写入数据到DataTable
|
if (CompanyFlag)
|
{
|
|
try
|
{
|
for (int i = (sheet.FirstRowNum); i < sheet.LastRowNum + 1; i++)
|
{
|
|
IRow row = sheet.GetRow(i);
|
if (row != null)
|
{
|
DataRow dataRow = table.NewRow();
|
for (int j = row.FirstCellNum; j < cellCount; j++)
|
{
|
ICell cell = row.GetCell(j);
|
if (cell == null)
|
{
|
dataRow[j] = null;
|
}
|
else
|
{
|
//dataRow[j] = cell.ToString();
|
switch (cell.CellType)
|
{
|
case CellType.Blank:
|
dataRow[j] = null;
|
break;
|
case CellType.Boolean:
|
dataRow[j] = cell.BooleanCellValue;
|
break;
|
case CellType.Numeric:
|
|
if (DateUtil.IsCellDateFormatted(cell))
|
{
|
if (SheetName == "资产负债表" || SheetName == "利润表" || SheetName == "现金流量表")
|
{
|
dataRow[j] = "";
|
}
|
else
|
{
|
dataRow[j] = cell.DateCellValue.ToString();
|
}
|
}
|
else
|
{
|
dataRow[j] = cell.ToString();
|
}
|
break;
|
case CellType.String:
|
dataRow[j] = cell.StringCellValue;
|
break;
|
case CellType.Error:
|
dataRow[j] = cell.ErrorCellValue;
|
break;
|
case CellType.Formula:
|
cell.SetCellType(CellType.String);
|
dataRow[j] = cell.StringCellValue;
|
break;
|
default:
|
dataRow[j] = "=" + cell.CellFormula;
|
break;
|
}
|
}
|
}
|
|
table.Rows.Add(dataRow);
|
}
|
|
//dataRow[j] = row.GetCell(j).ToString();
|
}
|
}
|
catch (Exception)
|
{
|
var s = Cou;
|
throw;
|
}
|
|
}
|
else
|
{
|
for (int i = (sheet.FirstRowNum + 2); i < sheet.LastRowNum + 1; i++)
|
{
|
IRow row = sheet.GetRow(i);
|
DataRow dataRow = table.NewRow();
|
for (int j = row.FirstCellNum; j < cellCount; j++)
|
{
|
ICell cell = row.GetCell(j);
|
if (cell == null)
|
{
|
dataRow[j] = null;
|
}
|
else
|
{
|
//dataRow[j] = cell.ToString();
|
switch (cell.CellType)
|
{
|
case CellType.Blank:
|
dataRow[j] = null;
|
break;
|
case CellType.Boolean:
|
dataRow[j] = cell.BooleanCellValue;
|
break;
|
case CellType.Numeric:
|
|
if (DateUtil.IsCellDateFormatted(cell))
|
{
|
if (SheetName == "资产负债表" || SheetName == "利润表" || SheetName == "现金流量表")
|
{
|
dataRow[j] = "";
|
}
|
else
|
{
|
dataRow[j] = cell.DateCellValue.ToString();
|
}
|
}
|
else
|
{
|
dataRow[j] = cell.ToString();
|
}
|
break;
|
case CellType.String:
|
dataRow[j] = cell.StringCellValue;
|
break;
|
case CellType.Error:
|
dataRow[j] = cell.ErrorCellValue;
|
break;
|
case CellType.Formula:
|
cell.SetCellType(CellType.String);
|
dataRow[j] = cell.StringCellValue;
|
break;
|
default:
|
dataRow[j] = "=" + cell.CellFormula;
|
break;
|
}
|
}
|
}
|
|
table.Rows.Add(dataRow);
|
//dataRow[j] = row.GetCell(j).ToString();
|
}
|
}
|
|
|
#endregion
|
}
|
catch (System.Exception ex)
|
{
|
var s = table.Rows.Count;
|
table.Clear();
|
table.Columns.Clear();
|
table.Columns.Add("出错了");
|
DataRow dr = table.NewRow();
|
dr[0] = ex.Message;
|
table.Rows.Add(dr);
|
return table;
|
}
|
finally
|
{
|
//sheet.Dispose();
|
workbook = null;
|
sheet = null;
|
}
|
|
#region 清除最后的空行
|
|
for (int i = table.Rows.Count - 1; i > 0; i--)
|
{
|
bool isnull = true;
|
for (int j = 0; j < table.Columns.Count; j++)
|
{
|
if (table.Rows[i][j] != null)
|
{
|
if (table.Rows[i][j].ToString() != "")
|
{
|
isnull = false;
|
break;
|
}
|
}
|
}
|
|
if (isnull)
|
{
|
table.Rows[i].Delete();
|
}
|
else
|
{
|
break;
|
}
|
}
|
|
|
|
|
|
|
#endregion
|
|
return table;
|
}
|
public static DataSet RenderDataTableFromExcelReport(Stream ExcelFileStream, string[] SheetName, int HeaderRowIndex, ref bool NameExistFlag)
|
{
|
DataSet dataSet = new DataSet();
|
IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
|
ExcelFileStream.Close();
|
int SheetCount = workbook.NumberOfSheets; //获取表的数量
|
string[] SheetNames = new string[SheetCount]; //保存表的名称
|
for (int i = 0; i < SheetCount; i++)
|
{
|
SheetNames[i] = workbook.GetSheetName(i);
|
}
|
foreach (var item in SheetName)
|
{
|
if (SheetNames.Contains(item))
|
{
|
NameExistFlag = true;
|
DataTable dt = RenderDataTableFromExcelReport(workbook, item, HeaderRowIndex);
|
dt.TableName = item;
|
dataSet.Tables.Add(dt.Copy());
|
}
|
}
|
return dataSet;
|
//if (SheetNames.Contains(SheetName))
|
//{
|
// NameExistFlag = true;
|
// return RenderDataTableFromExcelReport(workbook, SheetName, HeaderRowIndex);
|
//}
|
//else
|
//{
|
// NameExistFlag = false;
|
// return null;
|
//}
|
}
|
#endregion
|
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex, ref bool NameExistFlag)
|
{
|
IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
|
ExcelFileStream.Close();
|
int SheetCount = workbook.NumberOfSheets; //获取表的数量
|
string[] SheetNames = new string[SheetCount]; //保存表的名称
|
for (int i = 0; i < SheetCount; i++)
|
{
|
SheetNames[i] = workbook.GetSheetName(i);
|
}
|
if (SheetNames.Contains(SheetName))
|
{
|
NameExistFlag = true;
|
return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
|
}
|
else
|
{
|
NameExistFlag = false;
|
return null;
|
}
|
}
|
|
public static void GenerateFiletoExcel(string TempletFilePath, string ReportFilePath, string SheetName, DataTable dt, int MaxColumn, int MaxDateCol = 1, int MaxNoteCol = 0, int MaxTypeCol = 0)
|
{
|
using (FileStream ExcelFileStream = new FileStream(TempletFilePath, FileMode.Open, FileAccess.Read))
|
{
|
IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
|
|
ISheet sheet1 = workbook.GetSheet(SheetName);
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
for (int j = 0; j < MaxColumn; j++)
|
{
|
if (j < MaxDateCol)//前面0-MaxDateCol列日期格式
|
{
|
//ICellStyle cellStyle = workbook.CreateCellStyle();
|
//cellStyle.IsLocked = false;
|
//sheet1.GetRow(i + 2).GetCell(j).CellStyle = cellStyle;
|
//string test = dt.Rows[i][j].ToString().Replace("/","-");
|
if ((SheetName == "回收计划" || SheetName == "支付计划") && MaxTypeCol == 1)
|
{
|
sheet1.GetRow(i + 2).GetCell(j).SetCellValue(dt.Rows[i][j].ToString().Replace("0:00:00", "").Replace("-", "/"));
|
}
|
else
|
{
|
sheet1.GetRow(i + 2).GetCell(j).SetCellValue(dt.Rows[i][j].ToString().Replace("0:00:00", "").Replace("/", "-"));
|
}
|
|
}
|
else if (MaxNoteCol > 0 && j >= MaxColumn - 1) //后面最后一列是备注内容
|
{
|
IRow row = sheet1.GetRow(i + 2);
|
ICell cell = row.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
else if (MaxTypeCol > 0 && j >= MaxColumn - 2)//列中有布尔类型
|
{
|
IRow row = sheet1.GetRow(i + 2);
|
ICell cell = row.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(Convert.ToBoolean(dt.Rows[i][j].ToString()) == true ? "是" : "否");
|
}
|
else if (SheetName == "流动性分析")
|
{
|
if (j == 0)
|
{
|
IRow row = sheet1.GetRow(i + 2);
|
ICell cell = row.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(Convert.ToInt32(dt.Rows[i][j].ToString()));
|
}
|
else if (j == 3 || j == 5)
|
{
|
IRow row = sheet1.GetRow(i + 2);
|
ICell cell = row.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
else
|
{
|
sheet1.GetRow(i + 2).GetCell(j).SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
|
}
|
}
|
else if (SheetName == "收款分析" || SheetName == "付款分析")
|
{
|
if (j == 0)
|
{
|
IRow row = sheet1.GetRow(i + 2);
|
ICell cell = row.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(Convert.ToInt32(dt.Rows[i][j].ToString()));
|
}
|
else if (j == 1)
|
{
|
sheet1.GetRow(i + 2).GetCell(j).SetCellValue(dt.Rows[i][j].ToString().Replace("0:00:00", "").Replace("-", "/"));
|
}
|
else if (j == 2)
|
{
|
IRow row = sheet1.GetRow(i + 2);
|
ICell cell = row.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
else
|
{
|
sheet1.GetRow(i + 2).GetCell(j).SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
|
}
|
}
|
|
}
|
}
|
sheet1.ProtectSheet(SheetLockPassword);
|
ISheet sheetNote = workbook.GetSheet("填写说明");
|
if (sheetNote != null)
|
{
|
sheetNote.ProtectSheet(SheetLockPassword);
|
}
|
|
using (FileStream filess = File.OpenWrite(ReportFilePath))
|
{
|
workbook.Write(filess);
|
}
|
}
|
}
|
#region 导出报表
|
public static void GenerateReportExcel(string[] SheetArr, string TempletFilePath, string ReportFilePath, DataSet dt, int MaxColumn)
|
{
|
//IWorkbook workbook;
|
//string fileExt = Path.GetExtension(ReportFilePath).ToLower();
|
//if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
|
//if (workbook == null) { return; }
|
|
using (FileStream ExcelFileStream = new FileStream(TempletFilePath, FileMode.Open, FileAccess.Read))
|
{
|
string[] SheetAll = new[] { "填写说明", "一般企业-资产负债表", "一般企业-利润表", "一般企业-现金流量表", "一般企业-现金流量表补充资料", "一般企业-所有者权益变动表", "商业银行-资产负债表", "商业银行-利润表", "商业银行-现金流量表", "商业银行-现金流量表补充资料", "商业银行-所有者权益变动表", "证券公司-资产负债表", "证券公司-利润表", "证券公司-现金流量表", "证券公司-现金流量表补充资料", "证券公司-所有者权益变动表", "保险公司-资产负债表", "保险公司-利润表", "保险公司-现金流量表", "保险公司-现金流量表补充资料", "保险公司-所有者权益变动表", "综合企业-资产负债表", "综合企业-利润表", "综合企业-现金流量表", "综合企业-现金流量表补充资料", "综合企业-所有者权益变动表" };
|
#region 预设行数
|
|
//var SheetRow = 0;
|
//if (SheetName == "资产负债表")
|
//{
|
// if (ReportType == "合并报表")
|
// {
|
// SheetName = "资产负债表-合并";
|
// SheetRow = 136;
|
// }
|
// else
|
// {
|
// SheetName = "资产负债表-母公司";
|
// SheetRow = 134;
|
// }
|
|
//}
|
//else if (SheetName == "利润表")
|
//{
|
// if (ReportType == "合并报表")
|
// {
|
// SheetName = "利润表-合并";
|
// SheetRow = 87;
|
// }
|
// else
|
// {
|
// SheetName = "利润表-母公司";
|
// SheetRow = 80;
|
// }
|
|
//}
|
//else if(SheetName== "现金流量表")
|
//{
|
// if (ReportType == "合并报表")
|
// {
|
// SheetName = "现金流量表-合并";
|
// SheetRow = 66;
|
// }
|
// else
|
// {
|
// SheetName = "现金流量表-母公司";
|
// SheetRow = 64;
|
// }
|
|
//}
|
//else if (SheetName=="现金流量表补充资料")
|
//{
|
// if (ReportType== "合并报表")
|
// {
|
// SheetName = "现金流量表补充资料-合并";
|
// }
|
// else
|
// {
|
// SheetName = "现金流量表补充资料-母公司";
|
// }
|
// SheetRow = 32;
|
//}
|
//else if (SheetName== "所有者权益变动表")
|
//{
|
// if (ReportType == "合并报表")
|
// {
|
// SheetName = "所有者权益变动表-合并";
|
// }
|
// else
|
// {
|
// SheetName = "所有者权益变动表-母公司";
|
// }
|
// SheetRow = 28;
|
//}
|
#endregion
|
IWorkbook workbook1 = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
|
var DelSheet = SheetAll.Except(SheetArr).ToList();
|
List<int> Index = new List<int>();
|
//foreach (var item in SheetArr)
|
//{
|
// Index.Add(workbook1.GetSheetIndex(item));
|
//}
|
//var SheetNum = workbook1.NumberOfSheets;
|
//for (int i = 0; i < SheetNum; i++)
|
//{
|
// if (!Index.Equals(i))
|
// {
|
// workbook1.RemoveSheetAt(i);
|
// }
|
|
//}
|
for (int i = 0; i < DelSheet.Count; i++)
|
{
|
workbook1.RemoveSheetAt(workbook1.GetSheetIndex(DelSheet[i]));
|
|
}
|
|
|
//ISheet sheet2 = workbook1.GetSheet(SheetArr[1]);
|
//ISheet sheet3 = workbook1.GetSheet(SheetArr[2]);
|
//ISheet sheet4 = workbook1.GetSheet(SheetArr[3]);
|
//ISheet sheet5 = workbook1.GetSheet(SheetArr[4]);
|
|
// ISheet sheet = workbook.CreateSheet(dt.Tables[z].TableName);
|
//CopySheet(workbook, sheet1, sheet, true);
|
// XSSFWorkbook myBook = new XSSFWorkbook(fs2);
|
// workbook.CreateSheet(dt.Tables[z].TableName);
|
//ISheet sheet=string.IsNullOrEmpty(dt.Tables[z].TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.Tables[z].TableName);
|
//string.IsNullOrEmpty(dt.Tables[z].TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.Tables[z].TableName);
|
//sheet1.ForceFormulaRecalculation = false;
|
//for (int i = 0; i < dt.Tables[z].Rows.Count; i++)
|
//{
|
|
//ISheet sheet2 = workbook1.GetSheet(SheetArr[1]);
|
//ISheet sheet3 = workbook1.GetSheet(SheetArr[2]);
|
//ISheet sheet4 = workbook1.GetSheet(SheetArr[3]);
|
//ISheet sheet5 = workbook1.GetSheet(SheetArr[4]);
|
#region 按模板导出
|
for (int z = 0; z < dt.Tables.Count; z++)
|
{
|
ISheet sheet1 = workbook1.GetSheet(SheetArr[z]);
|
int i = 0;
|
if (dt.Tables[z].TableName == "资产负债表")
|
{
|
i += 4;
|
}
|
else if (dt.Tables[z].TableName == "利润表")
|
{
|
i += 4;
|
}
|
else if (dt.Tables[z].TableName == "现金流量表")
|
{
|
i += 4;
|
}
|
else if (dt.Tables[z].TableName == "现金流量表补充资料")
|
{
|
i += 4;
|
}
|
else if (dt.Tables[z].TableName == "所有者权益变动表")
|
{
|
i += 6;
|
}
|
if (!SheetArr[z].Contains("综合企业"))
|
{
|
for (int j = 0; j < dt.Tables[z].Rows.Count; j++)
|
{
|
IRow row = sheet1.GetRow(j + i);
|
if (dt.Tables[z].TableName == "所有者权益变动表")
|
{
|
var SName = row.GetCell(0);
|
|
if (SName != null && SName.ToString().Trim() != "")
|
{
|
var Sel = dt.Tables[z].Select("Name like '%" + SName.ToString().Trim().Replace("%", "[%]").Replace("*", "[*]") + "%'").ToList();
|
if (Sel != null && Sel.Count == 1)
|
{
|
int index = 1;
|
foreach (var item in Sel[0].Table.Rows)
|
{
|
ICell cell = row.GetCell(index);
|
if (index == 15)
|
{
|
cell.SetCellValue(Sel[0][index].ToString() == null ? "--" : Sel[0][index].ToString());
|
}
|
else if (index < 15)
|
{
|
cell.SetCellValue(double.Parse(Sel[0][index].ToString()));
|
}
|
|
index++;
|
}
|
}
|
else if (Sel.Count > 1)
|
{
|
|
var OneArr = dt.Tables[z].Select("Name like '" + SName.ToString().Trim() + "'").ToList();
|
|
|
if (OneArr.Count > 0)
|
{
|
int index = 1;
|
foreach (var item in Sel[0].Table.Rows)
|
{
|
ICell cell = row.GetCell(index);
|
|
if (index == 15)
|
{
|
cell.SetCellValue(Sel[0][index].ToString() == null ? "--" : Sel[0][index].ToString());
|
}
|
else if (index < 15)
|
{
|
cell.SetCellValue(double.Parse(Sel[0][index].ToString()));
|
}
|
index++;
|
}
|
}
|
|
|
|
}
|
|
}
|
}
|
else
|
{
|
if (row != null)
|
{
|
var SName = row.GetCell(1);
|
if (SName != null && SName.ToString().Trim() != "")
|
{
|
if (SName.ToString().Trim() != "流动资产合计" && SName.ToString().Trim() != "非流动资产合计" && SName.ToString().Trim() != "资产总计" && SName.ToString().Trim() != "流动负债合计" && SName.ToString().Trim() != "非流动负债合计" && SName.ToString().Trim() != "负债总计" && SName.ToString().Trim() != "归属于母公司所有者权益 （或股东权益）合计" && SName.ToString().Trim() != "所有者权益（或股东权益）合计" && SName.ToString().Trim() != "负债和所有者权益总计")
|
{
|
var Sel = dt.Tables[z].Select("Name like '%" + SName.ToString().Trim().Replace("%", "[%]").Replace("*", "[*]") + "%'").ToList();
|
if (Sel != null && Sel.Count == 1)
|
{
|
ICell cell = row.GetCell(2);
|
cell.SetCellValue(double.Parse(Sel[0][2].ToString()));
|
if (!dt.Tables[z].TableName.Equals("现金流量表") && !dt.Tables[z].TableName.Equals("现金流量表补充资料"))
|
{
|
if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
|
{
|
ICell cell2 = row.GetCell(3);
|
cell2.SetCellValue(Sel[0][3].ToString() == null ? double.Parse("0") : double.Parse(Sel[0][3].ToString()));
|
}
|
|
ICell cell3 = row.GetCell(4);
|
if (z == 1)
|
{
|
cell3.SetCellValue(Sel[0][5].ToString() == null ? "--" : Sel[0][5].ToString());
|
}
|
else
|
{
|
cell3.SetCellValue(Sel[0][4].ToString() == null ? "--" : Sel[0][4].ToString());
|
}
|
|
}
|
else
|
{
|
ICell cell2 = row.GetCell(3);
|
cell2.SetCellValue(Sel[0][3].ToString() == null ? "--" : Sel[0][3].ToString());
|
}
|
|
}
|
else if (Sel.Count > 1)
|
{
|
|
var OneArr = dt.Tables[z].Select("Name like '" + SName.ToString().Trim() + "'").ToList();
|
ICell cell = row.GetCell(2);
|
if (OneArr.Count > 0)
|
{
|
cell.SetCellValue(double.Parse(OneArr[0][2].ToString()));
|
}
|
|
|
if (!dt.Tables[z].TableName.Contains("现金流量表"))
|
{
|
|
if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
|
{
|
ICell cell2 = row.GetCell(3);
|
if (OneArr.Count > 0)
|
{
|
cell2.SetCellValue(OneArr[0][3].ToString() == null ? double.Parse("0") : double.Parse(OneArr[0][3].ToString()));
|
}
|
|
}
|
ICell cell3 = row.GetCell(4);
|
if (OneArr.Count > 0)
|
{
|
if (z == 1)
|
{
|
cell3.SetCellValue(Sel[0][5].ToString() == null ? "--" : Sel[0][5].ToString());
|
}
|
else
|
{
|
cell3.SetCellValue(Sel[0][4].ToString() == null ? "--" : Sel[0][4].ToString());
|
}
|
}
|
|
}
|
else
|
{
|
ICell cell2 = row.GetCell(3);
|
if (OneArr.Count > 0)
|
{
|
cell2.SetCellValue(OneArr[0][3].ToString() == null ? "--" : OneArr[0][3].ToString());
|
}
|
cell2.SetCellValue(OneArr[0][3].ToString() == null ? "--" : OneArr[0][3].ToString());
|
|
}
|
}
|
}
|
}
|
}
|
|
}
|
}
|
}
|
else
|
{
|
for (int j = 0; j < dt.Tables[z].Rows.Count; j++)
|
{
|
IRow row = sheet1.GetRow(j + i);
|
if (dt.Tables[z].TableName == "所有者权益变动表")
|
{
|
var SName = row.GetCell(0);
|
|
if (SName != null && SName.ToString().Trim() != "")
|
{
|
var Sel = dt.Tables[z].Select("Name like '%" + SName.ToString().Trim().Replace("%", "[%]").Replace("*", "[*]") + "%'").ToList();
|
if (Sel != null && Sel.Count == 1)
|
{
|
int index = 1;
|
foreach (var item in Sel[0].Table.Rows)
|
{
|
ICell cell = row.GetCell(index);
|
if (index == 15)
|
{
|
cell.SetCellValue(Sel[0][index].ToString() == null ? "--" : Sel[0][index].ToString());
|
}
|
else if (index < 15)
|
{
|
cell.SetCellValue(double.Parse(Sel[0][index].ToString()));
|
}
|
|
index++;
|
}
|
}
|
else if (Sel.Count > 1)
|
{
|
|
var OneArr = dt.Tables[z].Select("Name like '" + SName.ToString().Trim() + "'").ToList();
|
|
|
if (OneArr.Count > 0)
|
{
|
int index = 1;
|
foreach (var item in Sel[0].Table.Rows)
|
{
|
ICell cell = row.GetCell(index);
|
|
if (index == 15)
|
{
|
cell.SetCellValue(Sel[0][index].ToString() == null ? "--" : Sel[0][index].ToString());
|
}
|
else if (index < 15)
|
{
|
cell.SetCellValue(double.Parse(Sel[0][index].ToString()));
|
}
|
index++;
|
}
|
}
|
|
|
|
}
|
|
}
|
}
|
else
|
{
|
if (row != null)
|
{
|
var SName = row.GetCell(0);
|
if (SName != null && SName.ToString().Trim() != "")
|
{
|
if (SName.ToString().Trim() != "流动资产合计" && SName.ToString().Trim() != "非流动资产合计" && SName.ToString().Trim() != "资产总计" && SName.ToString().Trim() != "流动负债合计" && SName.ToString().Trim() != "非流动负债合计" && SName.ToString().Trim() != "负债总计" && SName.ToString().Trim() != "归属于母公司所有者权益合计" && SName.ToString().Trim() != "所有者权益合计" && SName.ToString().Trim() != "负债和所有者权益总计")
|
{
|
var Sel = dt.Tables[z].Select("Name like '" + SName.ToString().Trim().Replace("%", "[%]").Replace("*", "[*]") + "'").ToList();
|
if (Sel != null && Sel.Count == 1)
|
{
|
ICell cell = row.GetCell(1);
|
cell.SetCellValue(double.Parse(Sel[0][2].ToString()));
|
if (!dt.Tables[z].TableName.Equals("现金流量表") && !dt.Tables[z].TableName.Equals("现金流量表补充资料"))
|
{
|
if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
|
{
|
ICell cell2 = row.GetCell(2);
|
cell2.SetCellValue(Sel[0][3].ToString() == null ? double.Parse("0") : double.Parse(Sel[0][3].ToString()));
|
}
|
|
ICell cell3 = row.GetCell(3);
|
if (z == 1)
|
{
|
cell3.SetCellValue(Sel[0][5].ToString() == null ? "--" : Sel[0][5].ToString());
|
}
|
else
|
{
|
cell3.SetCellValue(Sel[0][4].ToString() == null ? "--" : Sel[0][4].ToString());
|
}
|
|
}
|
else
|
{
|
ICell cell2 = row.GetCell(2);
|
cell2.SetCellValue(Sel[0][3].ToString() == null ? "--" : Sel[0][3].ToString());
|
}
|
|
}
|
else if (Sel.Count > 1)
|
{
|
int SelIndex = 0;
|
if (SName.ToString().IndexOf(" ") >= 0)
|
{
|
SelIndex= Sel[0][0].ToString().Contains("2")?0:1;
|
}
|
ICell cell = row.GetCell(1);
|
cell.SetCellValue(double.Parse(Sel[SelIndex][2].ToString()));
|
if (!dt.Tables[z].TableName.Contains("现金流量表"))
|
{
|
|
if (Sel[SelIndex][0].ToString() != "所有者权益" && Sel[SelIndex][1].ToString() != "其中：优先股" && Sel[SelIndex][1].ToString() != "永续债")
|
{
|
ICell cell2 = row.GetCell(2);
|
if (Sel.Count > 0)
|
{
|
cell2.SetCellValue(Sel[SelIndex][3].ToString() == null ? double.Parse("0") : double.Parse(Sel[SelIndex][3].ToString()));
|
}
|
|
}
|
ICell cell3 = row.GetCell(3);
|
if (Sel.Count > 0)
|
{
|
if (z == 1)
|
{
|
cell3.SetCellValue(Sel[SelIndex][5].ToString() == null ? "--" : Sel[SelIndex][5].ToString());
|
}
|
else
|
{
|
cell3.SetCellValue(Sel[SelIndex][4].ToString() == null ? "--" : Sel[SelIndex][4].ToString());
|
}
|
}
|
|
}
|
else
|
{
|
ICell cell2 = row.GetCell(2);
|
if (Sel.Count > 0)
|
{
|
cell2.SetCellValue(Sel[0][3].ToString() == null ? "--" : Sel[0][3].ToString());
|
}
|
cell2.SetCellValue(Sel[0][3].ToString() == null ? "--" : Sel[0][3].ToString());
|
|
}
|
}
|
#region
|
//if (Sel != null)
|
//{
|
|
// ICell cell = row.GetCell(2);
|
// cell.SetCellValue(double.Parse(Sel[0][2].ToString()));
|
// if (!dt.Tables[z].TableName.Equals("现金流量表") && !dt.Tables[z].TableName.Equals("现金流量表补充资料"))
|
// {
|
// if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
|
// {
|
// ICell cell2 = row.GetCell(3);
|
// cell2.SetCellValue(Sel[0][3].ToString() == null ? double.Parse("0") : double.Parse(Sel[0][3].ToString()));
|
// }
|
|
// ICell cell3 = row.GetCell(4);
|
// if (z == 1)
|
// {
|
// cell3.SetCellValue(Sel[0][5].ToString() == null ? "--" : Sel[0][5].ToString());
|
// }
|
// else
|
// {
|
// cell3.SetCellValue(Sel[0][4].ToString() == null ? "--" : Sel[0][4].ToString());
|
// }
|
|
// }
|
// else
|
// {
|
// ICell cell2 = row.GetCell(3);
|
// cell2.SetCellValue(Sel[0][3].ToString() == null ? "--" : Sel[0][3].ToString());
|
// }
|
|
//}
|
//else if (Sel.Count > 1)
|
//{
|
|
// var OneArr = dt.Tables[z].Select("Name like '" + SName.ToString().Trim() + "'").ToList();
|
// ICell cell = row.GetCell(2);
|
// if (OneArr.Count > 0)
|
// {
|
// cell.SetCellValue(double.Parse(OneArr[0][2].ToString()));
|
// }
|
|
|
// if (!dt.Tables[z].TableName.Contains("现金流量表"))
|
// {
|
|
// if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
|
// {
|
// ICell cell2 = row.GetCell(3);
|
// if (OneArr.Count > 0)
|
// {
|
// cell2.SetCellValue(OneArr[0][3].ToString() == null ? double.Parse("0") : double.Parse(OneArr[0][3].ToString()));
|
// }
|
|
// }
|
// ICell cell3 = row.GetCell(4);
|
// if (OneArr.Count > 0)
|
// {
|
// if (z == 1)
|
// {
|
// cell3.SetCellValue(Sel[0][5].ToString() == null ? "--" : Sel[0][5].ToString());
|
// }
|
// else
|
// {
|
// cell3.SetCellValue(Sel[0][4].ToString() == null ? "--" : Sel[0][4].ToString());
|
// }
|
// }
|
|
// }
|
// else
|
// {
|
// ICell cell2 = row.GetCell(3);
|
// if (OneArr.Count > 0)
|
// {
|
// cell2.SetCellValue(OneArr[0][3].ToString() == null ? "--" : OneArr[0][3].ToString());
|
// }
|
// cell2.SetCellValue(OneArr[0][3].ToString() == null ? "--" : OneArr[0][3].ToString());
|
|
// }
|
//}
|
#endregion
|
}
|
}
|
}
|
|
}
|
|
}
|
}
|
}
|
|
|
#endregion
|
#region 直接导出
|
//if (SheetName.Contains("所有者权益变动表"))
|
//{
|
// for (int j = 0; j < MaxColumn; j++)
|
// {
|
// if (ReportType == "合并报表")
|
// {
|
// if (j == 0 || j == 15)
|
// {
|
// IRow row = sheet1.GetRow(i + 11);
|
// if (row != null)
|
// {
|
// ICell cell = row.CreateCell(j, CellType.String);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell.CellStyle = cellStyle;
|
// cell.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
// else
|
// {
|
// IRow row1 = sheet1.CreateRow(i + 11);
|
// ICell cell = row1.CreateCell(j, CellType.String);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell.CellStyle = cellStyle;
|
// cell.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
|
// }
|
// else
|
// {
|
// IRow row = sheet1.GetRow(i + 11);
|
// if (row != null)
|
// {
|
// ICell cell = row.GetCell(j);
|
// if (cell != null)
|
// {
|
// row.RemoveCell(cell);
|
// }
|
// ICell cell1 = row.CreateCell(j, CellType.Numeric);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell1.CellStyle = cellStyle;
|
// cell1.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
// else
|
// {
|
// IRow row1 = sheet1.CreateRow(i + 11);
|
|
// ICell cell = row1.CreateCell(j, CellType.Numeric);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell.CellStyle = cellStyle;
|
// cell.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
// }
|
// }
|
// else
|
// {
|
// if (j == 0 || j == 13)
|
// {
|
// IRow row = sheet1.GetRow(i + 11);
|
// if (row != null)
|
// {
|
// ICell cell = row.CreateCell(j, CellType.String);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell.CellStyle = cellStyle;
|
// cell.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
// else
|
// {
|
// IRow row1 = sheet1.CreateRow(i + 11);
|
// ICell cell = row1.CreateCell(j, CellType.String);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell.CellStyle = cellStyle;
|
// cell.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
|
// }
|
// else
|
// {
|
// IRow row = sheet1.GetRow(i + 11);
|
// if (row != null)
|
// {
|
// ICell cell = row.GetCell(j);
|
// if (cell != null)
|
// {
|
// row.RemoveCell(cell);
|
// }
|
// ICell cell1 = row.CreateCell(j, CellType.Numeric);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell1.CellStyle = cellStyle;
|
// cell1.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
// else
|
// {
|
// IRow row1 = sheet1.CreateRow(i + 11);
|
|
// ICell cell = row1.CreateCell(j, CellType.Numeric);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell.CellStyle = cellStyle;
|
// cell.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
// }
|
// }
|
|
// }
|
//}
|
//else
|
//{
|
// for (int j = 0; j < MaxColumn; j++)
|
// {
|
// if (MaxColumn == 5)
|
// {
|
// if (j == 0 || j == 1 || j == 4)
|
// {
|
// IRow row = sheet1.GetRow(i + 7);
|
// if (row != null)
|
// {
|
// ICell cell = row.CreateCell(j, CellType.String);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell.CellStyle = cellStyle;
|
// cell.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
// else
|
// {
|
// IRow row1 = sheet1.CreateRow(i + 7);
|
// ICell cell = row1.CreateCell(j, CellType.String);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell.CellStyle = cellStyle;
|
// cell.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
|
// }
|
// else
|
// {
|
// IRow row = sheet1.GetRow(i + 7);
|
// if (row != null)
|
// {
|
// ICell cell = row.GetCell(j);
|
// if (cell != null)
|
// {
|
// row.RemoveCell(cell);
|
// }
|
// ICell cell1 = row.CreateCell(j, CellType.Numeric);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell1.CellStyle = cellStyle;
|
// cell1.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
// else
|
// {
|
// IRow row1 = sheet1.CreateRow(i + 7);
|
|
// ICell cell = row1.CreateCell(j, CellType.Numeric);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell.CellStyle = cellStyle;
|
// cell.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
// }
|
// }
|
// else
|
// {
|
// if (j == 0 || j == 1 || j == 3)
|
// {
|
// IRow row = sheet1.GetRow(i + 7);
|
// if (row != null)
|
// {
|
// ICell cell = row.CreateCell(j, CellType.String);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell.CellStyle = cellStyle;
|
// cell.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
// else
|
// {
|
// IRow row1 = sheet1.CreateRow(i + 7);
|
// ICell cell = row1.CreateCell(j, CellType.String);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell.CellStyle = cellStyle;
|
// cell.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
|
// }
|
// else
|
// {
|
// IRow row = sheet1.GetRow(i + 7);
|
// if (row != null)
|
// {
|
// ICell cell = row.GetCell(j);
|
// if (cell != null)
|
// {
|
// row.RemoveCell(cell);
|
// }
|
// ICell cell1 = row.CreateCell(j, CellType.Numeric);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell1.CellStyle = cellStyle;
|
// cell1.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
// else
|
// {
|
// IRow row1 = sheet1.CreateRow(i + 7);
|
|
// ICell cell = row1.CreateCell(j, CellType.Numeric);
|
// ICellStyle cellStyle = workbook.CreateCellStyle();
|
// cellStyle.BorderBottom = BorderStyle.Thin;
|
// cellStyle.BorderRight = BorderStyle.Thin;
|
// cellStyle.IsLocked = false;
|
// cell.CellStyle = cellStyle;
|
// cell.SetCellValue(dt.Rows[i][j].ToString());
|
// }
|
// }
|
// }
|
|
|
|
// }
|
//}
|
#endregion
|
//}
|
//var Num = SheetRow - dt.Rows.Count;
|
//if (Num>0)
|
//{
|
// for (int i = dt.Rows.Count; i <= SheetRow; i++)
|
// {
|
// IRow row = sheet1.GetRow(i + 7);
|
// for (int j = 0; j < MaxColumn; j++)
|
// {
|
// ICell cell = row.GetCell(j);
|
// if (cell!=null)
|
// {
|
// row.RemoveCell(cell);
|
// }
|
|
// }
|
|
// }
|
//}
|
//sheet1.ProtectSheet(SheetLockPassword);
|
//ISheet sheetNote = workbook.GetSheet("填写说明");
|
//if (sheetNote != null)
|
//{
|
// sheetNote.ProtectSheet(SheetLockPassword);
|
//}
|
// work
|
using (FileStream filess = File.OpenWrite(ReportFilePath))
|
{
|
workbook1.Write(filess);
|
}
|
}
|
|
|
}
|
|
public static void GenerateReportExcel(string TempletFilePath, string ReportFilePath, string SheetName, DataTable dt, string ReportType, int MaxColumn)
|
{
|
using (FileStream ExcelFileStream = new FileStream(TempletFilePath, FileMode.Open, FileAccess.Read))
|
{
|
IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
|
#region 预设行数
|
|
var SheetRow = 0;
|
if (SheetName == "资产负债表")
|
{
|
if (ReportType == "合并报表")
|
{
|
SheetName = "资产负债表-合并";
|
SheetRow = 136;
|
}
|
else
|
{
|
SheetName = "资产负债表-母公司";
|
SheetRow = 134;
|
}
|
|
}
|
else if (SheetName == "利润表")
|
{
|
if (ReportType == "合并报表")
|
{
|
SheetName = "利润表-合并";
|
SheetRow = 87;
|
}
|
else
|
{
|
SheetName = "利润表-母公司";
|
SheetRow = 80;
|
}
|
|
}
|
else if (SheetName == "现金流量表")
|
{
|
if (ReportType == "合并报表")
|
{
|
SheetName = "现金流量表-合并";
|
SheetRow = 66;
|
}
|
else
|
{
|
SheetName = "现金流量表-母公司";
|
SheetRow = 64;
|
}
|
|
}
|
else if (SheetName == "现金流量表补充资料")
|
{
|
if (ReportType == "合并报表")
|
{
|
SheetName = "现金流量表补充资料-合并";
|
}
|
else
|
{
|
SheetName = "现金流量表补充资料-母公司";
|
}
|
SheetRow = 32;
|
}
|
else if (SheetName == "所有者权益变动表")
|
{
|
if (ReportType == "合并报表")
|
{
|
SheetName = "所有者权益变动表-合并";
|
}
|
else
|
{
|
SheetName = "所有者权益变动表-母公司";
|
}
|
SheetRow = 28;
|
}
|
#endregion
|
ISheet sheet1 = workbook.GetSheet(SheetName);
|
sheet1.ForceFormulaRecalculation = false;
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
|
#region 按模板导出
|
//IRow row = sheet1.GetRow(i + 7);
|
//var SName = row.GetCell(1);
|
//if (SName.ToString().Trim() != null && SName.ToString().Trim() != "")
|
//{
|
// if (SName.ToString().Trim() != "流动资产合计" && SName.ToString().Trim() != "非流动资产合计" && SName.ToString().Trim() != "资产总计" && SName.ToString().Trim() != "流动负债合计" && SName.ToString().Trim() != "非流动负债合计" && SName.ToString().Trim() != "负债总计" && SName.ToString().Trim() != "归属于母公司所有者权益 （或股东权益）合计" && SName.ToString().Trim() != "所有者权益（或股东权益）合计" && SName.ToString().Trim() != "负债和所有者权益（或股东权益）总计")
|
// {
|
// var Sel = dt.Select("Name like '%" + SName.ToString().Trim().Replace("%", "[%]").Replace("*", "[*]") + "%'").ToList();
|
|
// if (Sel != null && Sel.Count == 1)
|
// {
|
|
// ICell cell = row.GetCell(2);
|
// cell.SetCellValue(double.Parse(Sel[0][2].ToString()));
|
// if (!SheetName.Contains("现金流量表"))
|
// {
|
// if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
|
// {
|
// ICell cell2 = row.GetCell(3);
|
// cell2.SetCellValue(Sel[0][3].ToString() == null ? double.Parse("0") : double.Parse(Sel[0][3].ToString()));
|
// }
|
|
// ICell cell3 = row.GetCell(4);
|
// cell3.SetCellValue(Sel[0][4].ToString() == null ? "--" : Sel[0][4].ToString());
|
// }
|
// else
|
// {
|
// ICell cell2 = row.GetCell(3);
|
// cell2.SetCellValue(Sel[0][3].ToString() == null ? "--" : Sel[0][3].ToString());
|
// }
|
|
// }
|
// else if (Sel.Count > 1)
|
// {
|
|
// var OneArr = dt.Select("Name like '" + SName.ToString().Trim() + "'").ToList();
|
// ICell cell = row.GetCell(2);
|
// if (OneArr.Count > 0)
|
// {
|
// cell.SetCellValue(double.Parse(OneArr[0][2].ToString()));
|
// }
|
|
|
// if (!SheetName.Contains("现金流量表"))
|
// {
|
|
// if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
|
// {
|
// ICell cell2 = row.GetCell(3);
|
// if (OneArr.Count > 0)
|
// {
|
// cell2.SetCellValue(OneArr[0][3].ToString() == null ? double.Parse("0") : double.Parse(OneArr[0][3].ToString()));
|
// }
|
|
// }
|
// ICell cell3 = row.GetCell(4);
|
// if (OneArr.Count > 0)
|
// {
|
// cell3.SetCellValue(OneArr[0][4].ToString() == null ? "--" : OneArr[0][4].ToString());
|
// }
|
|
// }
|
// else
|
// {
|
// ICell cell2 = row.GetCell(3);
|
// if (OneArr.Count > 0)
|
// {
|
// cell2.SetCellValue(OneArr[0][3].ToString() == null ? "--" : OneArr[0][3].ToString());
|
// }
|
// cell2.SetCellValue(OneArr[0][3].ToString() == null ? "--" : OneArr[0][3].ToString());
|
|
// }
|
// }
|
|
// }
|
//}
|
|
#endregion
|
#region 直接导出
|
if (SheetName.Contains("所有者权益变动表"))
|
{
|
for (int j = 0; j < MaxColumn; j++)
|
{
|
if (ReportType == "合并报表")
|
{
|
if (j == 0 || j == 15)
|
{
|
IRow row = sheet1.GetRow(i + 11);
|
if (row != null)
|
{
|
ICell cell = row.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
else
|
{
|
IRow row1 = sheet1.CreateRow(i + 11);
|
ICell cell = row1.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
|
}
|
else
|
{
|
IRow row = sheet1.GetRow(i + 11);
|
if (row != null)
|
{
|
ICell cell = row.GetCell(j);
|
if (cell != null)
|
{
|
row.RemoveCell(cell);
|
}
|
ICell cell1 = row.CreateCell(j, CellType.Numeric);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell1.CellStyle = cellStyle;
|
cell1.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
else
|
{
|
IRow row1 = sheet1.CreateRow(i + 11);
|
|
ICell cell = row1.CreateCell(j, CellType.Numeric);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
}
|
}
|
else
|
{
|
if (j == 0 || j == 13)
|
{
|
IRow row = sheet1.GetRow(i + 11);
|
if (row != null)
|
{
|
ICell cell = row.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
else
|
{
|
IRow row1 = sheet1.CreateRow(i + 11);
|
ICell cell = row1.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
|
}
|
else
|
{
|
IRow row = sheet1.GetRow(i + 11);
|
if (row != null)
|
{
|
ICell cell = row.GetCell(j);
|
if (cell != null)
|
{
|
row.RemoveCell(cell);
|
}
|
ICell cell1 = row.CreateCell(j, CellType.Numeric);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell1.CellStyle = cellStyle;
|
cell1.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
else
|
{
|
IRow row1 = sheet1.CreateRow(i + 11);
|
|
ICell cell = row1.CreateCell(j, CellType.Numeric);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
}
|
}
|
|
}
|
}
|
else
|
{
|
for (int j = 0; j < MaxColumn; j++)
|
{
|
if (MaxColumn == 5)
|
{
|
if (j == 0 || j == 1 || j == 4)
|
{
|
IRow row = sheet1.GetRow(i + 7);
|
if (row != null)
|
{
|
ICell cell = row.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
else
|
{
|
IRow row1 = sheet1.CreateRow(i + 7);
|
ICell cell = row1.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
|
}
|
else
|
{
|
IRow row = sheet1.GetRow(i + 7);
|
if (row != null)
|
{
|
ICell cell = row.GetCell(j);
|
if (cell != null)
|
{
|
row.RemoveCell(cell);
|
}
|
ICell cell1 = row.CreateCell(j, CellType.Numeric);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell1.CellStyle = cellStyle;
|
cell1.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
else
|
{
|
IRow row1 = sheet1.CreateRow(i + 7);
|
|
ICell cell = row1.CreateCell(j, CellType.Numeric);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
}
|
}
|
else
|
{
|
if (j == 0 || j == 1 || j == 3)
|
{
|
IRow row = sheet1.GetRow(i + 7);
|
if (row != null)
|
{
|
ICell cell = row.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
else
|
{
|
IRow row1 = sheet1.CreateRow(i + 7);
|
ICell cell = row1.CreateCell(j, CellType.String);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
|
}
|
else
|
{
|
IRow row = sheet1.GetRow(i + 7);
|
if (row != null)
|
{
|
ICell cell = row.GetCell(j);
|
if (cell != null)
|
{
|
row.RemoveCell(cell);
|
}
|
ICell cell1 = row.CreateCell(j, CellType.Numeric);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell1.CellStyle = cellStyle;
|
cell1.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
else
|
{
|
IRow row1 = sheet1.CreateRow(i + 7);
|
|
ICell cell = row1.CreateCell(j, CellType.Numeric);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
cell.CellStyle = cellStyle;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
}
|
}
|
|
|
|
}
|
}
|
#endregion
|
}
|
var Num = SheetRow - dt.Rows.Count;
|
if (Num > 0)
|
{
|
for (int i = dt.Rows.Count; i <= SheetRow; i++)
|
{
|
IRow row = sheet1.GetRow(i + 7);
|
for (int j = 0; j < MaxColumn; j++)
|
{
|
ICell cell = row.GetCell(j);
|
if (cell != null)
|
{
|
row.RemoveCell(cell);
|
}
|
|
}
|
|
}
|
}
|
//sheet1.ProtectSheet(SheetLockPassword);
|
ISheet sheetNote = workbook.GetSheet("填写说明");
|
if (sheetNote != null)
|
{
|
sheetNote.ProtectSheet(SheetLockPassword);
|
}
|
|
using (FileStream filess = File.OpenWrite(ReportFilePath))
|
{
|
workbook.Write(filess);
|
}
|
}
|
}
|
#region
|
//public static void CopyCellStyle(IWorkbook wb, ICellStyle fromStyle, ICellStyle toStyle)
|
//{
|
// toStyle.Alignment = fromStyle.Alignment;
|
// //边框和边框颜色
|
// toStyle.BorderBottom = fromStyle.BorderBottom;
|
// toStyle.BorderLeft = fromStyle.BorderLeft;
|
// toStyle.BorderRight = fromStyle.BorderRight;
|
// toStyle.BorderTop = fromStyle.BorderTop;
|
// toStyle.TopBorderColor = fromStyle.TopBorderColor;
|
// toStyle.BottomBorderColor = fromStyle.BottomBorderColor;
|
// toStyle.RightBorderColor = fromStyle.RightBorderColor;
|
// toStyle.LeftBorderColor = fromStyle.LeftBorderColor;
|
// //背景和前景
|
// toStyle.FillBackgroundColor = fromStyle.FillBackgroundColor;
|
// toStyle.FillForegroundColor = fromStyle.FillForegroundColor;
|
// toStyle.DataFormat = fromStyle.DataFormat;
|
// toStyle.FillPattern = fromStyle.FillPattern;
|
// //toStyle.Hidden=fromStyle.Hidden;
|
// toStyle.IsHidden = fromStyle.IsHidden;
|
// toStyle.Indention = fromStyle.Indention;//首行缩进
|
// toStyle.IsLocked = fromStyle.IsLocked;
|
// toStyle.Rotation = fromStyle.Rotation;//旋转
|
// toStyle.VerticalAlignment = fromStyle.VerticalAlignment;
|
// toStyle.WrapText = fromStyle.WrapText;
|
// toStyle.SetFont(fromStyle.GetFont(wb));
|
//}
|
///// <summary>
|
///// 复制表
|
///// </summary>
|
///// <param name="wb"></param>
|
///// <param name="fromSheet"></param>
|
///// <param name="toSheet"></param>
|
///// <param name="copyValueFlag"></param>
|
//public static void CopySheet(IWorkbook wb, ISheet fromSheet, ISheet toSheet, bool copyValueFlag)
|
//{
|
// //合并区域处理
|
// MergerRegion(fromSheet, toSheet);
|
// System.Collections.IEnumerator rows = fromSheet.GetRowEnumerator();
|
// while (rows.MoveNext())
|
// {
|
// IRow row = null;
|
// if (wb is HSSFWorkbook)
|
// row = rows.Current as HSSFRow;
|
// else
|
// row = rows.Current as NPOI.XSSF.UserModel.XSSFRow;
|
// IRow newRow = toSheet.CreateRow(row.RowNum);
|
// CopyRow(wb, row, newRow, copyValueFlag);
|
// }
|
//}
|
///// <summary>
|
///// 复制行
|
///// </summary>
|
///// <param name="wb"></param>
|
///// <param name="fromRow"></param>
|
///// <param name="toRow"></param>
|
///// <param name="copyValueFlag"></param>
|
//public static void CopyRow(IWorkbook wb, IRow fromRow, IRow toRow, bool copyValueFlag)
|
//{
|
// System.Collections.IEnumerator cells = fromRow.GetEnumerator();//.GetRowEnumerator();
|
// toRow.Height = fromRow.Height;
|
// while (cells.MoveNext())
|
// {
|
// ICell cell = null;
|
// //ICell cell = (wb is HSSFWorkbook) ? cells.Current as HSSFCell : cells.Current as NPOI.XSSF.UserModel.XSSFCell;
|
// if (wb is HSSFWorkbook)
|
// cell = cells.Current as HSSFCell;
|
// else
|
// cell = cells.Current as NPOI.XSSF.UserModel.XSSFCell;
|
// ICell newCell = toRow.CreateCell(cell.ColumnIndex);
|
// CopyCell(wb, cell, newCell, copyValueFlag);
|
// }
|
//}
|
///// <summary>
|
///// 复制原有sheet的合并单元格到新创建的sheet
|
///// </summary>
|
///// <param name="fromSheet"></param>
|
///// <param name="toSheet"></param>
|
//public static void MergerRegion(ISheet fromSheet, ISheet toSheet)
|
//{
|
// int sheetMergerCount = fromSheet.NumMergedRegions;
|
// for (int i = 0; i < sheetMergerCount; i++)
|
// {
|
// //Region mergedRegionAt = fromSheet.GetMergedRegion(i); //.MergedRegionAt(i);
|
// //CellRangeAddress[] cra = new CellRangeAddress[1];
|
// //cra[0] = fromSheet.GetMergedRegion(i);
|
// //Region[] rg = Region.ConvertCellRangesToRegions(cra);
|
// toSheet.AddMergedRegion(fromSheet.GetMergedRegion(i));
|
// }
|
//}
|
///// <summary>
|
///// 复制单元格
|
///// </summary>
|
///// <param name="wb"></param>
|
///// <param name="srcCell"></param>
|
///// <param name="distCell"></param>
|
///// <param name="copyValueFlag"></param>
|
//public static void CopyCell(IWorkbook wb, ICell srcCell, ICell distCell, bool copyValueFlag)
|
//{
|
// ICellStyle newstyle = wb.CreateCellStyle();
|
// CopyCellStyle(wb, srcCell.CellStyle, newstyle);
|
// //样式
|
// distCell.CellStyle = newstyle;
|
// //评论
|
// if (srcCell.CellComment != null)
|
// {
|
// distCell.CellComment = srcCell.CellComment;
|
// }
|
// // 不同数据类型处理
|
// CellType srcCellType = srcCell.CellType;
|
// distCell.SetCellType(srcCellType);
|
// if (copyValueFlag)
|
// {
|
// if (srcCellType == CellType.Numeric)
|
// {
|
// if (HSSFDateUtil.IsCellDateFormatted(srcCell))
|
// {
|
// distCell.SetCellValue(srcCell.DateCellValue);
|
// }
|
// else
|
// {
|
// distCell.SetCellValue(srcCell.NumericCellValue);
|
// }
|
// }
|
// else if (srcCellType == CellType.String)
|
// {
|
// distCell.SetCellValue(srcCell.RichStringCellValue);
|
// }
|
// else if (srcCellType == CellType.Blank)
|
// {
|
// // nothing21
|
// }
|
// else if (srcCellType == CellType.Boolean)
|
// {
|
// distCell.SetCellValue(srcCell.BooleanCellValue);
|
// }
|
// else if (srcCellType == CellType.Error)
|
// {
|
// distCell.SetCellErrorValue(srcCell.ErrorCellValue);
|
// }
|
// else if (srcCellType == CellType.Formula)
|
// {
|
// distCell.SetCellFormula(srcCell.CellFormula);
|
// }
|
// else
|
// { // nothing29
|
// }
|
// }
|
//}
|
#endregion
|
|
public static void GenerateReportTemplate(string TempletFilePath, string ReportFilePath, string SheetName)
|
{
|
using (FileStream ExcelFileStream = new FileStream(TempletFilePath, FileMode.Open, FileAccess.Read))
|
{
|
IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
|
|
ISheet sheet1 = workbook.GetSheet(SheetName);
|
//sheet1.ProtectSheet(SheetLockPassword);
|
ISheet sheetNote = workbook.GetSheet("填写说明");
|
if (sheetNote != null)
|
{
|
sheetNote.ProtectSheet(SheetLockPassword);
|
}
|
using (FileStream filess = File.OpenWrite(ReportFilePath))
|
{
|
workbook.Write(filess);
|
}
|
}
|
}
|
#endregion
|
|
|
public static void GenerateFiletoExcel(string TempletFilePath, string ReportFilePath, string SheetName, string SheetNameCashFlow, DataTable dt, DataTable dtCashFlow, int MaxColumn)
|
{
|
using (FileStream ExcelFileStream = new FileStream(TempletFilePath, FileMode.Open, FileAccess.Read))
|
{
|
IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
|
ISheet sheet1 = workbook.GetSheet(SheetName);
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
for (int j = 0; j < MaxColumn; j++)
|
{
|
sheet1.GetRow(i + 2).GetCell(j).SetCellValue(dt.Rows[i][j].ToString().Replace("0:00:00", ""));
|
}
|
}
|
|
ISheet sheet2 = workbook.GetSheet(SheetNameCashFlow);
|
for (int i = 0; i < dtCashFlow.Rows.Count; i++)
|
{
|
for (int j = 0; j < dtCashFlow.Columns.Count; j++)
|
{
|
if ((i < 2 || i == dtCashFlow.Rows.Count - 1) || j <= 1)
|
{
|
sheet2.GetRow(i + 1).GetCell(j)
|
.SetCellValue(dtCashFlow.Rows[i][j].ToString().Replace("0:00:00", ""));
|
}
|
else
|
{
|
double Result = 0;
|
double.TryParse(dtCashFlow.Rows[i][j].ToString(), out Result);
|
ICellStyle cellStyle = workbook.CreateCellStyle();
|
IDataFormat format = workbook.CreateDataFormat();
|
cellStyle.BorderBottom = BorderStyle.Thin;
|
cellStyle.BorderRight = BorderStyle.Thin;
|
cellStyle.BorderLeft = BorderStyle.Thin;
|
cellStyle.IsLocked = false;
|
if (dtCashFlow.Rows[i][0].ToString().IndexOf("数量") < 0)
|
{
|
cellStyle.DataFormat = format.GetFormat("#,##0.00");
|
}
|
else
|
{
|
cellStyle.DataFormat = format.GetFormat("0.00");
|
}
|
sheet2.GetRow(i + 1).GetCell(j).CellStyle = cellStyle;
|
sheet2.GetRow(i + 1).GetCell(j).SetCellValue(Result);
|
}
|
}
|
}
|
sheet1.ProtectSheet(SheetLockPassword);
|
sheet2.ProtectSheet(SheetLockPassword);
|
ISheet sheetNote = workbook.GetSheet("填写说明");
|
if (sheetNote != null)
|
{
|
sheetNote.ProtectSheet(SheetLockPassword);
|
}
|
|
using (FileStream filess = File.OpenWrite(ReportFilePath))
|
{
|
workbook.Write(filess);
|
}
|
}
|
}
|
|
public static double PMTFunction(double Rate, double Year, double Cash)
|
{
|
double Result = 0;
|
|
IWorkbook workbook = new HSSFWorkbook();
|
ISheet sheet1 = workbook.CreateSheet("Sheet1");
|
IRow row1 = sheet1.CreateRow(0);
|
ICell cell1 = row1.CreateCell(0);
|
ICell cell2 = row1.CreateCell(1);
|
ICell cell3 = row1.CreateCell(2);
|
ICell cellSum = row1.CreateCell(3);
|
|
cell1.SetCellValue(Rate);
|
cell2.SetCellValue(Year);
|
cell3.SetCellValue(Cash);
|
cellSum.SetCellFormula("PMT(A1,B1,C1)");
|
|
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
|
cellSum = e.EvaluateInCell(cellSum);
|
Result = cellSum.NumericCellValue * (-1);
|
return Result;
|
}
|
|
//public static double LOGNORMDISTFunction(double BaseAverage, double BaseSqrt)
|
//{
|
// double Result = 0;
|
|
// //IWorkbook workbook = new HSSFWorkbook();
|
// //ISheet sheet1 = workbook.CreateSheet("Sheet1");
|
// //IRow row1 = sheet1.CreateRow(0);
|
// //ICell cell1 = row1.CreateCell(0);
|
// //ICell cell2 = row1.CreateCell(1);
|
// //ICell cell3 = row1.CreateCell(2);
|
// //ICell cellSum = row1.CreateCell(3);
|
|
// //cell1.SetCellValue(1);
|
// //cell2.SetCellValue(BaseAverage);
|
// //cell3.SetCellValue(BaseSqrt);
|
// //cellSum.SetCellFormula("LOGNORMDIST(A1,B1,C1)");
|
|
// //HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
|
// //cellSum = e.EvaluateInCell(cellSum);
|
// //Result = cellSum.NumericCellValue ;
|
// Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
|
// Result = excel.WorksheetFunction.LogNormDist(1.0, BaseAverage, BaseSqrt);
|
// return Result;
|
//}
|
|
}
|
}
|
