操作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{

var v = $('#verify-banner');

$('[data-loading]').text('正在生成评级报告，请稍后……');

$('#loadingPDF').modal('show');

svgAsPngUri(document.getElementsByTagName("svg")[0], {}, function (uri) {

$('#certificate').html('<img style="max-width:720px;width: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>

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>

}

posted on 2019-03-14 15:40 张行行阅读(179) 评论(0) 编辑收藏举报

刷新页面返回顶部

操作Work、Excel、PDF

张行行

公告

导航