操作Work、Excel、PDF
1、NPOI插件
namespace XBLRDiff.BLL.Excel
{
public class ExcelImport:IDisposable
{
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
//private string fileName = null;//文件名
//private IWorkbook workbook = null;
//private FileStream fs = null;
//private bool disposed;
public string fileName = null;//文件名
public IWorkbook workbook = null;
private FileStream fs = null;
private bool disposed;
public string message = "";//返回错误信息
public ExcelImport(string _fileName)
{
message = "";
this.fileName = _fileName;
disposed = false;
Dispose();
this.fs = new FileStream(this.fileName, FileMode.Open, FileAccess.Read );
try
{
if (fileName.ToLower().IndexOf(".xlsx") > 0)//2007版本
this.workbook = new XSSFWorkbook(this.fs);
else if (fileName.ToLower().IndexOf(".xls") > 0)//2003版本
this.workbook = new HSSFWorkbook(this.fs);
}
catch(Exception ex)
{
//this.workbook = WorkbookFactory.Create(this.fs);
message = ex.Message;
if (!string.IsNullOrEmpty(message) && message.Contains("eg XSSF instead of HSSF"))
message = "文件的后缀名与内容版本不相符，请修改文件后缀为.xlsx";
}
}
/// <summary>
/// 根据sheet名称，读内容到datatable
/// </summary>
/// <param name="sheetName"></param>
/// <param name="isAdjustData"></param>
/// <returns></returns>
public DataTable GetAllBySheetName(string sheetName,bool isAdjustData)
{
DataTable dtData = new DataTable();
ISheet sheet = null;
if (this.workbook == null || this.fs == null)
return null;
if (sheetName != null)
{
sheet = this.workbook.GetSheet(sheetName);
if (sheet == null)//如果没有找到指定的sheetName对应的sheet,则尝试获取第一个 sheet
sheet = this.workbook.GetSheetAt(0);
}
else
sheet = this.workbook.GetSheetAt(0);
if (sheet == null) return null;
int startRow = 0, endRow = sheet.LastRowNum;
IRow firstRow = sheet.GetRow(startRow);
int cellCount = 9;//firstRow.LastCellNum;
//table结构
for (int c = 0; c < cellCount; c++)
{
dtData.Columns.Add("Col" + c.ToString());
}
//内容table
DataRow lastRow = null;
for (int i = startRow; i <= endRow; i++)
{
IRow rowData = sheet.GetRow(i);
if (rowData == null) continue;
DataRow drData = dtData.NewRow();
for (int c = 0; c < cellCount; c++)
{
string strLastCellValue = lastRow == null ? "" : lastRow[c].ToString();
ICell cellData = rowData.GetCell(c);
string strData = "";
if (cellData != null)
{
string strCellValue = "";
switch (cellData.CellType)
{
case CellType.Boolean: strCellValue = cellData.BooleanCellValue.ToString(); break;
case CellType.Error: strCellValue = cellData.ErrorCellValue.ToString(); break;
case CellType.Numeric: strCellValue = cellData.NumericCellValue.ToString(); break;
case CellType.Formula:
{
if (cellData.CachedFormulaResultType == CellType.Numeric)
strCellValue = cellData.NumericCellValue.ToString();
else if (cellData.CachedFormulaResultType == CellType.String)
strCellValue = cellData.StringCellValue;
}
break;
case CellType.String: strCellValue = cellData.StringCellValue.ToString(); break;
default: strCellValue = ""; break;
}
strData = isAdjustData && cellData.IsMergedCell && strCellValue.Trim() == "" ? strLastCellValue : strCellValue;
}
drData["Col" + c.ToString()] = strData;
}
lastRow = drData;
dtData.Rows.Add(drData);
}
return dtData;
}
/// <summary>
/// 获取最大的dataTable
/// </summary>
/// <param name="sheetName"></param>
/// <param name="isAdjustData"></param>
/// <returns></returns>
public DataTable GetMaxTableBySheetName(string sheetName,bool isAdjustData)
{
DataTable dtData = new DataTable();
ISheet sheet = null;
int? cellCount = 0;
if (this.workbook == null || this.fs == null)
return null;
if (sheetName != null)
{
sheet = this.workbook.GetSheet(sheetName);
if (sheet == null)//如果没有找到指定的sheetName对应的sheet,则尝试获取第一个 sheet
sheet = this.workbook.GetSheetAt(0);
}
else
sheet = this.workbook.GetSheetAt(0);
if (sheet == null) return null;
int startRow = 0, endRow = sheet.LastRowNum;
IRow firstRow = null;
for (int i = 0; i < endRow; i++)
{
firstRow = sheet.GetRow(i);
if (firstRow == null)
continue;
cellCount = firstRow.LastCellNum > cellCount ? firstRow.LastCellNum : cellCount;
}
//table结构
for (int c = 0; c < cellCount; c++)
{
dtData.Columns.Add("Col" + c.ToString());
}
//内容table
DataRow lastRow = null;
ICell cellData = null;
for (int i = startRow; i <= endRow; i++)
{
IRow rowData = sheet.GetRow(i);
DataRow drData = dtData.NewRow();
if (rowData == null)
{
dtData.Rows.Add(drData);
continue;
}
for (int c = 0; c < cellCount; c++)
{
string strLastCellValue = lastRow == null ? "" : lastRow[c].ToString();
if (rowData != null)
cellData = rowData.GetCell(c);
string strData = "";
if (cellData != null)
{
string strCellValue = "";
switch (cellData.CellType)
{
case CellType.Boolean: strCellValue = cellData.BooleanCellValue.ToString(); break;
case CellType.Error: strCellValue = cellData.ErrorCellValue.ToString(); break;
case CellType.Numeric: strCellValue = cellData.ToString().Contains("月")||cellData.ToString().Contains("/")?cellData.DateCellValue.ToString(): cellData.NumericCellValue.ToString(); break;
case CellType.Formula:
{
if (cellData.CachedFormulaResultType == CellType.Numeric)
strCellValue = cellData.NumericCellValue.ToString();
else if (cellData.CachedFormulaResultType == CellType.String)
strCellValue = cellData.StringCellValue;
}
break;
case CellType.String: strCellValue = cellData.StringCellValue.ToString(); break;
default: strCellValue = ""; break;
}
strData = isAdjustData && cellData.IsMergedCell && strCellValue.Trim() == "" ? strLastCellValue : strCellValue;
}
drData["Col" + c.ToString()] = strData;
}
lastRow = drData;
dtData.Rows.Add(drData);
}
return dtData;
}
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public int DataTableToExcelBySheet(object model, string sheetName, bool isColumnWritten,string writeFileName,string xbrlPath)
{
int flag = 0;
int count = 0;
ISheet sheet = null;
XbrlHelper xbrlHelper = new XbrlHelper();
try
{
if (workbook != null)
{
sheet = this.workbook.GetSheet(sheetName);
}
else
{
return -1;
}
if (isColumnWritten == true && sheet!=null) //写入DataTable的列名
{
int endRow = sheet.LastRowNum;
for (int r = 0; r <= endRow; r++)
{
IRow row = sheet.GetRow(r);
int cellCount = row.LastCellNum;
for (int c = 0; c <= cellCount; c++)
{
var itemColl = row.GetCell(c);
string strCellValue = "";
if (itemColl == null)
{
continue;
}
switch (itemColl.CellType)
{
case CellType.Boolean: strCellValue = itemColl.BooleanCellValue.ToString(); break;
case CellType.Error: strCellValue = itemColl.ErrorCellValue.ToString(); break;
case CellType.Numeric: strCellValue = itemColl.NumericCellValue.ToString(); break;
case CellType.Formula:
{
if (itemColl.CachedFormulaResultType == CellType.Numeric)
strCellValue = itemColl.NumericCellValue.ToString();
else if (itemColl.CachedFormulaResultType == CellType.String)
strCellValue = itemColl.StringCellValue;
}
break;
case CellType.String: strCellValue = itemColl.StringCellValue.ToString(); break;
default: strCellValue = ""; continue;
}
if (strCellValue.Length > 0 && strCellValue.StartsWith("$"))
{
var property = strCellValue.Substring(1, strCellValue.Length - 1);
//根据字符获取属性
object result = ContainProperty(model, property);
itemColl.SetCellValue(result.ToString());
}
else if(strCellValue.Length > 0 && strCellValue.StartsWith("#"))
{
var str = "";
var code = strCellValue.Substring(1, strCellValue.Length - 1);
//根据code从xbrl中取值
var list = xbrlHelper.GetValueByCode(xbrlPath, code,"");
if (list.Count > 1)
str = list[flag];
else
str = list.First();
double value = double.Parse(str);
itemColl.SetCellValue(value);
}
}
}
}
else
{
count = 0;
}
using (fs = File.OpenWrite(writeFileName))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
}
return count;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return -1;
}
}
/// <summary>
/// 获取所有的sheet页
/// </summary>
/// <returns></returns>
public string DataTableToExcel(object model, bool isColumnWritten, string xbrlPath,string outputPath)
{
try
{
ISheet sheet = null;
if (workbook == null)
return fileName+"excel工作薄打不开";
var gpmxList = new List<GpmxModel>();
var gzbKzzList = new List<GzbKzz>();
var mrfexxList = new List<Mrfexx>();
var mrfexxListA = new List<Mrfexx>();
var mrfexxListB = new List<Mrfexx>();
var mrfexxListC = new List<Mrfexx>();
var gzbZsList = new List<GpmxModel>();
var gzbJJList = new List<GpmxModel>();
var gzbDtList = new List<GpmxModel>();
var gpmxZsSgList = new List<GpmxModel>();
var gpmxJJTpList = new List<GpmxModel>();
var yebList = new List<YebModel>();
var yebSSJJList = new List<YebModel>();
var lrbList = new List<LrbModel>();
var gptzList = new List<GzbKzz>();
var GzbSSJJList = new List<GzbKzz>();
var bjbList = new List<BjbModel>();
var zqhgrzqkList = new List<ZqhgrzqkModel>();
var mrjzxxList = new List<MrjzxxModel>();
var syqxjsb_Yhck = new List<SyqxjsbModel>();
var syqxjsb_Qsbfj = new List<SyqxjsbModel>();
var syqxjsb_Ccbzj = new List<SyqxjsbModel>();
var syqxjsb_Mrfsjrzc = new List<SyqxjsbModel>();
var syqxjsb_Zqqsk = new List<SyqxjsbModel>();
var syqxjsb_Mchgjrzck = new List<SyqxjsbModel>();
var syqxjsb_Zqtz = new List<SyqxjsbModel>();
var gzbZqList = new List<GpmxModel>();
var gzbZqList_FJ = new List<GpmxModel>();
var QDgzb_JJSS_List = new List<GpmxModel>();
var QDgzb_JJSZ_List = new List<GpmxModel>();
var gzbszList = new List<GpmxModel>();
var gzbJRList = new List<GpmxModel>();
var febhlsList = new List<FebhlsModel>();
var ListDictionary = new Dictionary<string,int>() { { "GpmxList", 10 },{ "GzbKzzList",5 },{ "MrfexxList",10 } };
ICellStyle style = workbook.CreateCellStyle();
IDataFormat dataformat = workbook.CreateDataFormat();
int count = workbook.NumberOfSheets; //获取所有SheetName
double doubleValue = 0;
for (int i = 0; i < count; i++)
{
sheet = workbook.GetSheetAt(i);
sheet.ForceFormulaRecalculation = true;
if (isColumnWritten == true && sheet != null) //写入DataTable的列名
{
int endRow = sheet.LastRowNum;
for (int r = 0; r <= endRow; r++)
{
IRow row = sheet.GetRow(r);
if (row == null)
continue;
int cellCount = row.LastCellNum;
for (int c = 0; c <= cellCount; c++)
{
var itemColl = row.GetCell(c);
string strCellValue = "";
if (itemColl == null)
{
continue;
}
switch (itemColl.CellType)
{
case CellType.Boolean: strCellValue = itemColl.BooleanCellValue.ToString(); break;
case CellType.Error: strCellValue = itemColl.ErrorCellValue.ToString(); break;
case CellType.Numeric: strCellValue = itemColl.NumericCellValue.ToString(); break;
case CellType.Formula:
{
if (itemColl.CachedFormulaResultType == CellType.Numeric)
strCellValue = itemColl.NumericCellValue.ToString();
else if (itemColl.CachedFormulaResultType == CellType.String)
strCellValue = itemColl.StringCellValue;
}
break;
case CellType.String: strCellValue = itemColl.StringCellValue.ToString(); break;
default: strCellValue = ""; continue;
}
if (strCellValue.Length > 0 && strCellValue.StartsWith("$"))
{
var property = strCellValue.Substring(1, strCellValue.Length - 1);
if (strCellValue.Contains("GpmxList") && gpmxList.Count == 0)
gpmxList = (List<GpmxModel>)ContainProperty(model, "GpmxList");
if (!strCellValue.Contains("GpmxList") && gpmxList.Count > 0)
gpmxList = new List<GpmxModel>();
if (gpmxList.Count>0)
{
int itemRow =int.Parse( strCellValue.Split('.')[1].ToString());
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(gpmxList[itemRow], itemName);
var gpxmValue = "";
if (itemValue == null)
continue;
gpxmValue = itemValue.ToString();
if (double.TryParse(gpxmValue, out doubleValue))
{
itemColl.SetCellValue(Convert.ToDouble(gpxmValue));
}
else
{
itemColl.SetCellValue(gpxmValue);
}
continue;
}
if (strCellValue.Contains("GzbKzzList") && gzbKzzList.Count == 0)
gzbKzzList = (List<GzbKzz>)ContainProperty(model, "GzbKzzList");
if (!strCellValue.Contains("GzbKzzList") && gzbKzzList.Count > 0)
gzbKzzList = new List<GzbKzz>();
if (gzbKzzList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if(itemRow< gzbKzzList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(gzbKzzList[itemRow], itemName);
var gzbKzzValue = "";
if (itemValue == null)
continue;
gzbKzzValue = itemValue.ToString();
if (double.TryParse(gzbKzzValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gzbKzzValue));
else
itemColl.SetCellValue(gzbKzzValue);
continue;
}
}
if (strCellValue.Contains("GzbGptzList") && gptzList.Count == 0)
gptzList = (List<GzbKzz>)ContainProperty(model, "GzbGptzList");
if (!strCellValue.Contains("GzbGptzList") && gptzList.Count > 0)
gptzList = new List<GzbKzz>();
if (gptzList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < gptzList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(gptzList[itemRow], itemName);
var gptzValue = "";
if (itemValue == null)
continue;
gptzValue = itemValue.ToString();
if (double.TryParse(gptzValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gptzValue));
else
itemColl.SetCellValue(gptzValue);
continue;
}
}
if (strCellValue.Contains("GzbSSJJList") && GzbSSJJList.Count == 0)
GzbSSJJList = (List<GzbKzz>)ContainProperty(model, "GzbSSJJList");
if (!strCellValue.Contains("GzbSSJJList") && GzbSSJJList.Count > 0)
GzbSSJJList = new List<GzbKzz>();
if (GzbSSJJList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < GzbSSJJList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(GzbSSJJList[itemRow], itemName);
var gptzValue = "";
if (itemValue == null)
continue;
gptzValue = itemValue.ToString();
if (double.TryParse(gptzValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gptzValue));
else
itemColl.SetCellValue(gptzValue);
continue;
}
}
if (strCellValue.Contains("MrfexxList") && !strCellValue.Contains("MrfexxListA") && !strCellValue.Contains("MrfexxListB") && !strCellValue.Contains("MrfexxListC") && mrfexxList.Count == 0)
mrfexxList = (List<Mrfexx>)ContainProperty(model, "MrfexxList");
if (!strCellValue.Contains("MrfexxList") && !strCellValue.Contains("MrfexxListA") && !strCellValue.Contains("MrfexxListB") && !strCellValue.Contains("MrfexxListC") && mrfexxList.Count > 0)
mrfexxList = new List<Mrfexx>();
if (mrfexxList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
var itemName = strCellValue.Split('.')[2];
if (itemRow >= mrfexxList.Count)
{
sheet.RemoveRow(row);
continue;
}
var itemValue = ExpendMethodCommon.ContainProperty(mrfexxList[itemRow], itemName);
var mrfexxValue = "";
if (itemValue == null)
continue;
mrfexxValue = itemValue.ToString();
if (double.TryParse(mrfexxValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(mrfexxValue));
else
itemColl.SetCellValue(mrfexxValue);
continue;
}
//if (strCellValue.Contains("MrfexxListC") && !strCellValue.Contains("MrfexxListC") && !strCellValue.Contains("MrfexxListC") && mrfexxList.Count == 0)
//if (!strCellValue.Contains("MrfexxListC") && !strCellValue.Contains("MrfexxListC") && !strCellValue.Contains("MrfexxListC") && mrfexxList.Count > 0)
if (strCellValue.Contains("MrfexxListC") && mrfexxListC.Count == 0)
mrfexxListC = (List<Mrfexx>)ContainProperty(model, "MrfexxListC");
if(!strCellValue.Contains("MrfexxListC") && mrfexxListC.Count > 0)
mrfexxListC = new List<Mrfexx>();
if (mrfexxListC.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
var itemName = strCellValue.Split('.')[2];
if (itemRow >= mrfexxListC.Count)
{
sheet.RemoveRow(row);
continue;
}
var itemValue = ExpendMethodCommon.ContainProperty(mrfexxListC[itemRow], itemName);
var mrfexxValue = "";
if (itemValue == null)
continue;
mrfexxValue = itemValue.ToString();
if (double.TryParse(mrfexxValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(mrfexxValue));
else
itemColl.SetCellValue(mrfexxValue);
continue;
}
if (strCellValue.Contains("MrfexxListA") && mrfexxListA.Count == 0)
mrfexxListA = (List<Mrfexx>)ContainProperty(model, "MrfexxListA");
if (!strCellValue.Contains("MrfexxListA") && mrfexxListA.Count > 0)
mrfexxListA = new List<Mrfexx>();
if (mrfexxListA.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
var itemName = strCellValue.Split('.')[2];
if (itemRow >= mrfexxListA.Count)
{
sheet.RemoveRow(row);
continue;
}
var itemValue = ExpendMethodCommon.ContainProperty(mrfexxListA[itemRow], itemName);
var mrfexxValue = "";
if (itemValue == null)
continue;
mrfexxValue = itemValue.ToString();
if (double.TryParse(mrfexxValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(mrfexxValue));
else
itemColl.SetCellValue(mrfexxValue);
continue;
}
if (strCellValue.Contains("MrfexxListB") && mrfexxListB.Count == 0)
mrfexxListB = (List<Mrfexx>)ContainProperty(model, "MrfexxListB");
if (!strCellValue.Contains("MrfexxListB") && mrfexxListB.Count > 0)
mrfexxListB = new List<Mrfexx>();
if (mrfexxListB.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
var itemName = strCellValue.Split('.')[2];
if (itemRow >= mrfexxListB.Count)
{
sheet.RemoveRow(row);
continue;
}
var itemValue = ExpendMethodCommon.ContainProperty(mrfexxListB[itemRow], itemName);
var mrfexxValue = "";
if (itemValue == null)
continue;
mrfexxValue = itemValue.ToString();
if (double.TryParse(mrfexxValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(mrfexxValue));
else
itemColl.SetCellValue(mrfexxValue);
continue;
}
if (strCellValue.Contains("GpmxZsList") && gzbZsList.Count == 0)
gzbZsList = (List<GpmxModel>)ContainProperty(model, "GpmxZsList");
if (!strCellValue.Contains("GpmxZsList") && gzbZsList.Count > 0)
gzbZsList = new List<GpmxModel>();
if (gzbZsList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(gzbZsList[itemRow], itemName);
var gzbZsValue = "";
if (itemValue == null)
continue;
gzbZsValue = itemValue.ToString();
if (double.TryParse(gzbZsValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gzbZsValue));
else
itemColl.SetCellValue(gzbZsValue);
continue;
}
if (strCellValue.Contains("QDLrbList") && lrbList.Count == 0)
lrbList = (List<LrbModel>)ContainProperty(model, "QDLrbList");
if (!strCellValue.Contains("QDLrbList") && lrbList.Count > 0)
lrbList = new List<LrbModel>();
if (lrbList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(lrbList[itemRow], itemName);
var lrbValue = "";
if (itemValue == null)
continue;
lrbValue = itemValue.ToString();
if (double.TryParse(lrbValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(lrbValue));
else
itemColl.SetCellValue(lrbValue);
continue;
}
if (strCellValue.Contains("GpmxJJList") && gzbJJList.Count == 0)
gzbJJList = (List<GpmxModel>)ContainProperty(model, "GpmxJJList");
if (!strCellValue.Contains("GpmxJJList") && gzbJJList.Count > 0)
gzbJJList = new List<GpmxModel>();
if (gzbJJList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < gzbJJList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(gzbJJList[itemRow], itemName);
var gzbJJValue = "";
if (itemValue == null)
continue;
gzbJJValue = itemValue.ToString();
if (double.TryParse(gzbJJValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gzbJJValue));
else
itemColl.SetCellValue(gzbJJValue);
continue;
}
}
if (strCellValue.Contains("GpmxDtList") && gzbDtList.Count == 0)
gzbDtList = (List<GpmxModel>)ContainProperty(model, "GpmxDtList");
if (!strCellValue.Contains("GpmxDtList") && gzbDtList.Count > 0)
gzbDtList = new List<GpmxModel>();
if (gzbDtList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < gzbDtList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(gzbDtList[itemRow], itemName);
var gzbDtValue = "0";
if (itemValue != null)
gzbDtValue = itemValue.ToString();
if (double.TryParse(gzbDtValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gzbDtValue));
else
itemColl.SetCellValue(gzbDtValue);
continue;
}
}
if (strCellValue.Contains("YebGzqhList") && yebList.Count == 0)
yebList = (List<YebModel>)ContainProperty(model, "YebGzqhList");
if (!strCellValue.Contains("YebGzqhList") && yebList.Count > 0)
yebList = new List<YebModel>();
if (yebList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < yebList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(yebList[itemRow], itemName);
var yebValue = "-";
if (itemValue != null)
yebValue = itemValue.ToString();
if (double.TryParse(yebValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(yebValue));
else
itemColl.SetCellValue(yebValue);
continue;
}
}
if (strCellValue.Contains("YebSSJJList") && yebSSJJList.Count == 0)
yebSSJJList = (List<YebModel>)ContainProperty(model, "YebSSJJList");
if (!strCellValue.Contains("YebSSJJList") && yebSSJJList.Count > 0)
yebSSJJList = new List<YebModel>();
if (yebSSJJList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < yebSSJJList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(yebSSJJList[itemRow], itemName);
var yebValue = "-";
if (itemValue != null)
yebValue = itemValue.ToString();
if (double.TryParse(yebValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(yebValue));
else
itemColl.SetCellValue(yebValue);
continue;
}
}
if (strCellValue.Contains("GpmxZsSgList") && gpmxZsSgList.Count == 0)
gpmxZsSgList = (List<GpmxModel>)ContainProperty(model, "GpmxZsSgList");
if (!strCellValue.Contains("GpmxZsSgList") && gpmxZsSgList.Count > 0)
gpmxZsSgList = new List<GpmxModel>();
if (gpmxZsSgList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < gpmxZsSgList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(gpmxZsSgList[itemRow], itemName);
var gpmxZsSgValue = "-";
if (itemValue != null)
gpmxZsSgValue = itemValue.ToString();
if (double.TryParse(gpmxZsSgValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gpmxZsSgValue));
else
itemColl.SetCellValue(gpmxZsSgValue);
continue;
}
}
if (strCellValue.Contains("GpmxJJTpList") && gpmxJJTpList.Count == 0)
gpmxJJTpList = (List<GpmxModel>)ContainProperty(model, "GpmxJJTpList");
if (!strCellValue.Contains("GpmxJJTpList") && gpmxJJTpList.Count > 0)
gpmxJJTpList = new List<GpmxModel>();
if (gpmxJJTpList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < gpmxJJTpList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(gpmxJJTpList[itemRow], itemName);
var gpmxJJTpValue = "";
if (itemValue == null)
continue;
gpmxJJTpValue = itemValue.ToString();
if (double.TryParse(gpmxJJTpValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gpmxJJTpValue));
else
itemColl.SetCellValue(gpmxJJTpValue);
continue;
}
}
if (strCellValue.Contains("GzbZqList") && gzbZqList.Count == 0)
gzbZqList = (List<GpmxModel>)ContainProperty(model, "GzbZqList");
if (!strCellValue.Contains("GzbZqList") && gzbZqList.Count > 0)
gzbZqList = new List<GpmxModel>();
if (gzbZqList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < gzbZqList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(gzbZqList[itemRow], itemName);
var gpmxJJTpValue = "";
if (itemValue == null)
continue;
gpmxJJTpValue = itemValue.ToString();
if (double.TryParse(gpmxJJTpValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gpmxJJTpValue));
else
itemColl.SetCellValue(gpmxJJTpValue);
continue;
}
}
if (strCellValue.Contains("GzbZqList_FJ") && gzbZqList_FJ.Count == 0)
gzbZqList_FJ = (List<GpmxModel>)ContainProperty(model, "GzbZqList_FJ");
if (!strCellValue.Contains("GzbZqList_FJ") && gzbZqList_FJ.Count > 0)
gzbZqList_FJ = new List<GpmxModel>();
if (gzbZqList_FJ.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < gzbZqList_FJ.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(gzbZqList_FJ[itemRow], itemName);
var gpmxJJTpValue = "";
if (itemValue == null)
continue;
gpmxJJTpValue = itemValue.ToString();
if (double.TryParse(gpmxJJTpValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gpmxJJTpValue));
else
itemColl.SetCellValue(gpmxJJTpValue);
continue;
}
}
if (strCellValue.Contains("QDGzb_JJSZ_List") && QDgzb_JJSZ_List.Count == 0)
QDgzb_JJSZ_List = (List<GpmxModel>)ContainProperty(model, "QDGzb_JJSZ_List");
if (!strCellValue.Contains("QDGzb_JJSZ_List") && QDgzb_JJSZ_List.Count > 0)
QDgzb_JJSZ_List = new List<GpmxModel>();
if (QDgzb_JJSZ_List.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < QDgzb_JJSZ_List.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(QDgzb_JJSZ_List[itemRow], itemName);
var gpmxJJTpValue = "";
if (itemValue == null)
continue;
gpmxJJTpValue = itemValue.ToString();
if (double.TryParse(gpmxJJTpValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gpmxJJTpValue));
else
itemColl.SetCellValue(gpmxJJTpValue);
continue;
}
}
if (strCellValue.Contains("QDGzb_JJSS_List") && QDgzb_JJSS_List.Count == 0)
QDgzb_JJSS_List = (List<GpmxModel>)ContainProperty(model, "QDGzb_JJSS_List");
if (!strCellValue.Contains("QDGzb_JJSS_List") && QDgzb_JJSS_List.Count > 0)
QDgzb_JJSS_List = new List<GpmxModel>();
if (QDgzb_JJSS_List.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < QDgzb_JJSS_List.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(QDgzb_JJSS_List[itemRow], itemName);
var gpmxJJTpValue = "";
if (itemValue == null)
continue;
gpmxJJTpValue = itemValue.ToString();
if (double.TryParse(gpmxJJTpValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gpmxJJTpValue));
else
itemColl.SetCellValue(gpmxJJTpValue);
continue;
}
}
if (strCellValue.Contains("GzbszList") && gzbszList.Count == 0)
gzbszList = (List<GpmxModel>)ContainProperty(model, "GzbszList");
if (!strCellValue.Contains("GzbszList") && gzbszList.Count > 0)
gzbszList = new List<GpmxModel>();
if (gzbszList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(gzbszList[itemRow], itemName);
var gpmxJJTpValue = "";
if (itemValue == null)
continue;
gpmxJJTpValue = itemValue.ToString();
if (double.TryParse(gpmxJJTpValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gpmxJJTpValue));
else
itemColl.SetCellValue(gpmxJJTpValue);
continue;
}
if (strCellValue.Contains("GzbJRList") && gzbJRList.Count == 0)
gzbJRList = (List<GpmxModel>)ContainProperty(model, "GzbJRList");
if (!strCellValue.Contains("GzbJRList") && gzbJRList.Count > 0)
gzbJRList = new List<GpmxModel>();
if (gzbJRList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(gzbJRList[itemRow], itemName);
var gpmxJJTpValue = "";
if (itemValue == null)
continue;
gpmxJJTpValue = itemValue.ToString();
if (double.TryParse(gpmxJJTpValue, out doubleValue))
itemColl.SetCellValue(Convert.ToDouble(gpmxJJTpValue));
else
itemColl.SetCellValue(gpmxJJTpValue);
continue;
}
if (strCellValue.Contains("BjbList") && bjbList.Count == 0)
bjbList = (List<BjbModel>)ContainProperty(model, "BjbList");
if (!strCellValue.Contains("BjbList") && bjbList.Count > 0)
bjbList = new List<BjbModel>();
if (bjbList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < bjbList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(bjbList[itemRow], itemName);
if (itemValue == null)
{
itemValue = "-";
}
itemColl.SetCellValue(itemValue.ToString());
style.DataFormat = dataformat.GetFormat("#,##0");
itemColl.CellStyle = style;
continue;
}
}
if (strCellValue.Contains("FebhlsList") && febhlsList.Count == 0)
febhlsList = (List<FebhlsModel>)ContainProperty(model, "FebhlsList");
if (!strCellValue.Contains("FebhlsList") && febhlsList.Count > 0)
febhlsList = new List<FebhlsModel>();
if (febhlsList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < febhlsList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(febhlsList[itemRow], itemName);
if (itemValue == null)
{
itemValue = "-";
}
itemColl.SetCellValue(itemValue.ToString());
style.DataFormat = dataformat.GetFormat("#,##0");
itemColl.CellStyle = style;
continue;
}
}
if (strCellValue.Contains("ZqhgrzqkList") && zqhgrzqkList.Count == 0)
zqhgrzqkList = (List<ZqhgrzqkModel>)ContainProperty(model, "ZqhgrzqkList");
if (!strCellValue.Contains("ZqhgrzqkList") && zqhgrzqkList.Count > 0)
zqhgrzqkList = new List<ZqhgrzqkModel>();
if (zqhgrzqkList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < zqhgrzqkList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(zqhgrzqkList[itemRow], itemName);
if (itemValue == null)
{
itemValue = "-";
}
itemColl.SetCellValue(itemValue.ToString());
style.DataFormat = dataformat.GetFormat("#,##0");
itemColl.CellStyle = style;
continue;
}
}
if (strCellValue.Contains("MrjzxxList") && mrjzxxList.Count == 0)
mrjzxxList = (List<MrjzxxModel>)ContainProperty(model, "MrjzxxList");
if (!strCellValue.Contains("MrjzxxList") && mrjzxxList.Count > 0)
mrjzxxList = new List<MrjzxxModel>();
if (mrjzxxList.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < mrjzxxList.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(mrjzxxList[itemRow], itemName);
if (itemValue == null)
{
itemValue = "-";
}
itemColl.SetCellValue(itemValue.ToString());
style.DataFormat = dataformat.GetFormat("#,##0");
itemColl.CellStyle = style;
continue;
}
}
if (strCellValue.Contains("Syqxjsb_Yhck") && syqxjsb_Yhck.Count == 0)
syqxjsb_Yhck = (List<SyqxjsbModel>)ContainProperty(model, "Syqxjsb_Yhck");
if (!strCellValue.Contains("Syqxjsb_Yhck") && syqxjsb_Yhck.Count > 0)
syqxjsb_Yhck = new List<SyqxjsbModel>();
if (syqxjsb_Yhck.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < syqxjsb_Yhck.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(syqxjsb_Yhck[itemRow], itemName);
if (itemValue == null)
{
itemValue = "-";
}
itemColl.SetCellValue(itemValue.ToString());
style.DataFormat = dataformat.GetFormat("#,##0");
itemColl.CellStyle = style;
continue;
}
}
if (strCellValue.Contains("Syqxjsb_Qsbfj") && syqxjsb_Qsbfj.Count == 0)
syqxjsb_Qsbfj = (List<SyqxjsbModel>)ContainProperty(model, "Syqxjsb_Qsbfj");
if (!strCellValue.Contains("Syqxjsb_Qsbfj") && syqxjsb_Qsbfj.Count > 0)
syqxjsb_Qsbfj = new List<SyqxjsbModel>();
if (syqxjsb_Qsbfj.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < syqxjsb_Qsbfj.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(syqxjsb_Qsbfj[itemRow], itemName);
if (itemValue == null)
{
itemValue = "-";
}
itemColl.SetCellValue(itemValue.ToString());
style.DataFormat = dataformat.GetFormat("#,##0");
itemColl.CellStyle = style;
continue;
}
}
if (strCellValue.Contains("Syqxjsb_Ccbzj") && syqxjsb_Ccbzj.Count == 0)
syqxjsb_Ccbzj = (List<SyqxjsbModel>)ContainProperty(model, "Syqxjsb_Ccbzj");
if (!strCellValue.Contains("Syqxjsb_Ccbzj") && syqxjsb_Ccbzj.Count > 0)
syqxjsb_Ccbzj = new List<SyqxjsbModel>();
if (syqxjsb_Ccbzj.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < syqxjsb_Ccbzj.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(syqxjsb_Ccbzj[itemRow], itemName);
if (itemValue == null)
{
itemValue = "-";
}
itemColl.SetCellValue(itemValue.ToString());
style.DataFormat = dataformat.GetFormat("#,##0");
itemColl.CellStyle = style;
continue;
}
}
if (strCellValue.Contains("Syqxjsb_Mrfsjrzc") && syqxjsb_Mrfsjrzc.Count == 0)
syqxjsb_Mrfsjrzc = (List<SyqxjsbModel>)ContainProperty(model, "Syqxjsb_Mrfsjrzc");
if (!strCellValue.Contains("Syqxjsb_Mrfsjrzc") && syqxjsb_Mrfsjrzc.Count > 0)
syqxjsb_Mrfsjrzc = new List<SyqxjsbModel>();
if (syqxjsb_Mrfsjrzc.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < syqxjsb_Mrfsjrzc.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(syqxjsb_Mrfsjrzc[itemRow], itemName);
if (itemValue == null)
{
itemValue = "-";
}
itemColl.SetCellValue(itemValue.ToString());
style.DataFormat = dataformat.GetFormat("#,##0");
itemColl.CellStyle = style;
continue;
}
}
if (strCellValue.Contains("Syqxjsb_Zqqsk") && syqxjsb_Zqqsk.Count == 0)
syqxjsb_Zqqsk = (List<SyqxjsbModel>)ContainProperty(model, "Syqxjsb_Zqqsk");
if (!strCellValue.Contains("Syqxjsb_Zqqsk") && syqxjsb_Zqqsk.Count > 0)
syqxjsb_Zqqsk = new List<SyqxjsbModel>();
if (syqxjsb_Zqqsk.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < syqxjsb_Zqqsk.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(syqxjsb_Zqqsk[itemRow], itemName);
if (itemValue == null)
{
itemValue = "-";
}
itemColl.SetCellValue(itemValue.ToString());
style.DataFormat = dataformat.GetFormat("#,##0");
itemColl.CellStyle = style;
continue;
}
}
if (strCellValue.Contains("Syqxjsb_Mchgjrzck") && syqxjsb_Mchgjrzck.Count == 0)
syqxjsb_Mchgjrzck = (List<SyqxjsbModel>)ContainProperty(model, "Syqxjsb_Mchgjrzck");
if (!strCellValue.Contains("Syqxjsb_Mchgjrzck") && syqxjsb_Mchgjrzck.Count > 0)
syqxjsb_Mchgjrzck = new List<SyqxjsbModel>();
if (syqxjsb_Mchgjrzck.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < syqxjsb_Mchgjrzck.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(syqxjsb_Mchgjrzck[itemRow], itemName);
if (itemValue == null)
{
itemValue = "-";
}
itemColl.SetCellValue(itemValue.ToString());
style.DataFormat = dataformat.GetFormat("#,##0");
itemColl.CellStyle = style;
continue;
}
}
if (strCellValue.Contains("Syqxjsb_Zqtz") && syqxjsb_Zqtz.Count == 0)
syqxjsb_Zqtz = (List<SyqxjsbModel>)ContainProperty(model, "Syqxjsb_Zqtz");
if (!strCellValue.Contains("Syqxjsb_Zqtz") && syqxjsb_Zqtz.Count > 0)
syqxjsb_Zqtz = new List<SyqxjsbModel>();
if (syqxjsb_Zqtz.Count > 0)
{
int itemRow = int.Parse(strCellValue.Split('.')[1].ToString());
if (itemRow < syqxjsb_Zqtz.Count)
{
var itemName = strCellValue.Split('.')[2];
var itemValue = ExpendMethodCommon.ContainProperty(syqxjsb_Zqtz[itemRow], itemName);
if (itemValue == null)
{
itemValue = "-";
}
itemColl.SetCellValue(itemValue.ToString());
style.DataFormat = dataformat.GetFormat("#,##0");
itemColl.CellStyle = style;
continue;
}
}
//根据字符获取属性
object result = ContainProperty(model, property);
if (result == null)
itemColl.SetCellValue("-");
else if(double.TryParse(result.ToString(),out doubleValue))
{
itemColl.SetCellValue(Convert.ToDouble(result));
}
else
{
itemColl.SetCellValue(result.ToString());
}
}
else if (strCellValue.Length > 0 && strCellValue.StartsWith("#"))
{
var value = "";
decimal percentage = 0;
var line = -1;
var str = "";
var note = "";
var type = "";
string code = strCellValue.Substring(1, strCellValue.Length - 1);
if (code.Length >4 && code.Length<=6)
{
str = code.Substring(5, 1);
code = code.Substring(0, 4);
}
else if (code.Length > 6 && code.Length <=8)
{
str = code.Substring(5, 1);
type = code.Substring(7,1);
code = code.Substring(0, 4);
}
else if (code.Length == 9)
{
str = code.Substring(5, 1);
type = code.Substring(7, 2);
code = code.Substring(0, 4);
}
else if (code.Length > 8)
{
str = code.Substring(5, 1);
type = code.Substring(7, 1);
note = code.Substring(9, 2);
code = code.Substring(0, 4);
}
if (str != "" &&str!=null)
{
line = Int32.Parse(str);
}
//根据字符获取属性
XbrlHelper xbrlHelper = new XbrlHelper();
var list = new List<string>();
if(type=="A"|| type == "B"|| type == "C")
{
list = xbrlHelper.GetValueByCode(xbrlPath, code, note,type);
}
else
{
list = xbrlHelper.GetValueByCode(xbrlPath, code, note);
}
if (list.Count() == 0)
value = null;
else
{
if (type == "p")
{
if (line > -1 && line < list.Count())
{
if (list[line] != "")
{
percentage = decimal.Parse(list[line]) * 100;
value = percentage.ToString() +"%";
}
}
else if (line < list.Count())
{
percentage = decimal.Parse(list.First()) * 100;
value = percentage.ToString() + "%";
}
else
value = null;
}
else if (type == "h")
{
if (line > -1 && line < list.Count())
{
if (list[line] != "")
{
percentage = decimal.Parse(list[line]) * 100;
value = percentage.ToString();
}
}
else if (line < list.Count())
{
percentage = decimal.Parse(list.First()) * 100;
value = percentage.ToString();
}
else
value = null;
}
else
{
if (line > -1 && line < list.Count())
value = list[line];
else if (line < list.Count())
value = list.First();
else
value = null;
}
}
if (string.IsNullOrEmpty(value))
itemColl.SetCellValue("-");
if (double.TryParse(value, out doubleValue))
{
itemColl.SetCellValue(Convert.ToDouble(value));
}
else
{
itemColl.SetCellValue(value);
}
}
}
}
}
else
{
count = 0;
}
}
using (fs = File.OpenWrite(outputPath))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
}
return "";
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return ex.Message;
}
}
/// <summary>
/// 通过反射，取model里的属性值
/// </summary>
/// <param name="instance"></param>
/// <param name="propertyName"></param>
/// <returns></returns>
public object ContainProperty(object instance, string propertyName)
{
try
{
if (instance != null && !string.IsNullOrEmpty(propertyName))
{
PropertyInfo _findedPropertyInfo = instance.GetType().GetProperty(propertyName);
if (_findedPropertyInfo != null)
return _findedPropertyInfo.GetValue(instance, null);
}
return 0;
}
catch(Exception ex)
{
return 0;
}
}
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
if (fs != null)
fs.Close();
}
disposed = true;
fs = null;
}
}
/// <summary>
/// 查找execl中所有的sheet
/// </summary>
/// <returns></returns>
public Dictionary<int, string> ReturnSheetList()
{
ISheet sheet = null;
Dictionary<int, string> t = new Dictionary<int, string>();
if (workbook == null)
return t;
int count = workbook.NumberOfSheets; //获取所有SheetName
for (int i = 0; i < count; i++)
{
sheet = workbook.GetSheetAt(i);
if (sheet.LastRowNum > 0)
{
t.Add(i, workbook.GetSheetAt(i).SheetName);
}
}
return t;
}
}
}
2、Spire.Doc、Spire.Pdf、Spire.XLS（excel转PDF、word转PDF）
3、OfficeOpenXml
/// <summary>
/// 导出EXCEL
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">导出的内容</param>
/// <param name="fileName">文件名称</param>
/// <param name="titles">标题列</param>
/// <returns></returns>
public IActionResult ExportEXCEL<T>(IList<T> list, string fileName, Dictionary<string, string> fields)
{
_logger.LogInformation(string.Format("后台 管理员{0}将试用版销售业绩汇总导出EXCEL。", User.GetModel().Id));
logHelper.AddLevelLog(2, string.Format("后台 管理员{0}将试用版销售业绩汇总导出EXCEL。", User.GetModel().Id), User.GetModel(), Setting.AppId);
string sWebRootFolder = _hostingEnvironment.WebRootPath;
string sFileName = $"{Guid.NewGuid()}.xlsx";
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder + "\file\Export", sFileName));
if (list.Count <= 0 || fields.Count <= 0)
return null;
using (ExcelPackage package = new ExcelPackage(file))
{
// 添加worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore");
int num = 0;
foreach (var item in fields)
{
worksheet.Cells[1, ++num].Value = item.Value;
}
//添加值
DataTable dt = ConvertDataTable(list, fields.Keys.ToArray());
for (int i = 0; i < dt.Rows.Count; i++)
{
int j = 0;
foreach (var item in fields)
{
worksheet.Cells[((char)(65 + j++)).ToString() + (2 + i).ToString()].Value = dt.Rows[i]["" + item.Key + ""].ToString();
}
}
package.Save();
}
return File("\file\Export\" + sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
}
/// <summary>
/// list转Datatable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
public static DataTable ConvertDataTable<T>(IList<T> list, params string[] propertyName)
{
List<string> propertyNameList = new List<string>();
if (propertyName != null)
propertyNameList.AddRange(propertyName);
DataTable result = new DataTable();
if (list.Count > 0)
{
PropertyInfo[] propertys = list[0].GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
if (propertyNameList.Count == 0)
{
result.Columns.Add(pi.Name, pi.PropertyType);
}
else
{
if (propertyNameList.Contains(pi.Name))
result.Columns.Add(pi.Name);
}
}
for (int i = 0; i < list.Count; i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in propertys)
{
if (propertyNameList.Count == 0)
{
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
}
else
{
if (propertyNameList.Contains(pi.Name))
{
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
}
}
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
}
4、html2canvas(前端h5的方法将html转成pdf)
@section Scripts{
<script src="~/lib/saveSvgAsPng-gh-pages/saveSvgAsPng.js"></script>
<script src="~/lib/jsPDF/jspdf.min.js"></script>
<script src="~/lib/jsPDF/html2canvas.js"></script>
<script>
var v = $('#verify-banner');
$('[data-loading]').text('正在生成评级报告，请稍后……');
$('#loadingPDF').modal('show');
svgAsPngUri(document.getElementsByTagName("svg")[0], {}, function (uri) {
$('#certificate').html('<img style="max-720px;100%;" src="' + uri + '" />');
$('#loadingPDF').remove();
$(".modal-backdrop").remove();
$(".modal-dialog").remove();
$("body").removeClass('modal-open');
v.css('top', (window.innerHeight - 420) / 2);
v.parent().show();
v.show();
});
$('[data-close-verify]').click(function () {
v.parent().hide();
});
function redirectToVerify() {
alert('完成认证才可以下载完整无水印的评级报告，点击确定将为你转到认证页面。');
location.href = '@Url.Action("Index","UserVerify")';
}
function ExportPdf() {
//$('[data-loading]').text('正在生成PDF文档，请稍后……');
//$('#loadingPDF').modal('show');
var doc = new jsPDF('p', 'mm', 'a4');
//添加证书
doc.addImage($('#certificate img').attr('src'), 'png', 0, 0, 210, 297);
doc.addPage();
//渲染基本信息
html2canvas($('#certificate-basic'), {
onrendered: function (canvas) {
var img = canvas.toDataURL();
//a4宽210mm两边缩进10
doc.addImage(img, 'png', 10, 10, 190, 75);
doc.addPage();
//渲染详细指标
html2canvas($('#certificate-addtion'), {
onrendered: function (canvas) {
var width = $('#certificate-addtion').width();
var height = $('#certificate-addtion').height();
var r = 710 / width;
var h = (height*r)/3.74;
var img = canvas.toDataURL();
doc.addImage(img, 'png', 10, 10, 190, h);
debugger
doc.save('@Html.Raw(Model.Product.Name)评级报告.pdf');
$('#loadingPDF').modal('hide');
}
});
}
});
};
</script>
<script type="text/javascript">
function getBase64Image(img) {
var canvas = document.createElement("canvas");
canvas.width = img.width;
canvas.height = img.height;
var ctx = canvas.getContext("2d");
ctx.drawImage(img, 0, 0, img.width, img.height);
var dataURL = canvas.toDataURL("image/png");
return dataURL
// return dataURL.replace("data:image/png;base64,", "");
}
function main() {
var img = document.createElement('img');
img.src = './images/Game of Thrones.jpg'; //此处自己替换本地图片的地址
img.onload = function () {
var data = getBase64Image(img);
var img1 = document.createElement('img');
img1.src = data;
document.body.appendChild(img1);
console.log(data);
}
}
</script>
}