public JsonResult Transfer(string type)
{
MSGReturnModel <string> result = new MSGReturnModel <string>();
ExcelName excelType = ExcelName.Marginp;
excelType = EnumUtil.GetValues <ExcelName>().First(y => y.ToString() == type);
IEnumerable <IFileModel> datas = null;
switch (excelType)
{
case ExcelName.Marginp:
datas = (List <FileMarginpModel>)Cache.Get(CacheList.ExcelfileData);
break;
case ExcelName.Marging:
datas = (List <FileMargingModel>)Cache.Get(CacheList.ExcelfileData);
break;
case ExcelName.Itemimp:
datas = (List <FileItemImpModel>)Cache.Get(CacheList.ExcelfileData);
break;
case ExcelName.Stock:
datas = (List <FileStockModel>)Cache.Get(CacheList.ExcelfileData);
break;
}
result = FileService.saveData(datas, excelType);
return(Json(result));
}
internal static Type GetInstanceType(ExcelName type)
{
FieldInfo data = typeof(ExcelName).GetField(type.ToString());
Attribute attribute = Attribute.GetCustomAttribute(data, typeof(CommucationAttribute));
CommucationAttribute result = (CommucationAttribute)attribute;
return(result.InstanceType);
}
/// <summary>
/// 將 DataTable 資料轉換至 Excel
/// </summary>
/// <param name="thisTable">欲轉換之DataTable</param>
/// <param name="path">檔案放置位置</param>
/// <param name="sheetName">寫入之sheet名稱</param>
/// <returns>失敗時回傳錯誤訊息</returns>
public static string DataTableToExcel(DataTable dt, string path, ExcelName type, List <FormateTitle> titles = null)
{
string result = string.Empty;
try
{
string version = "2003"; //default 2003
IWorkbook wb = null;
ISheet ws;
string configVersion = ConfigurationManager.AppSettings["ExcelVersion"];
if (!configVersion.IsNullOrWhiteSpace())
{
version = configVersion;
}
//建立Excel 2003檔案
if ("2003".Equals(version))
{
wb = new HSSFWorkbook();
}
if ("2007".Equals(version))
{
wb = new XSSFWorkbook();
}
ws = wb.CreateSheet(type.GetDescription());
ExcelSetValue(ws, dt, type, titles);
FileStream file = new FileStream(path, FileMode.Create);//產生檔案
wb.Write(file);
file.Close();
}
catch (Exception ex)
{
result = ex.Message;
}
finally
{
//關閉文件
//oXL.Quit();
}
return(result);
}
public static IFileModel GetInstance(ExcelName type)
{
Type t = TableTypeHelper.GetInstanceType(type);
return((IFileModel)Activator.CreateInstance(t));
}
protected ExcelImportBase(ExcelName eName)
{
this.eName = eName;
}
public JsonResult File_Upload()
{
MSGReturnModel <string> result = new MSGReturnModel <string>();
try
{
ExcelName excelType = ExcelName.Marginp;
#region 前端無傳送檔案進來
if (!Request.Files.AllKeys.Any())
{
result.RETURN_FLAG = false;
result.DESCRIPTION = MessageType.upload_Not_Find.GetDescription();
return(Json(result));
}
var FileModel = Request.Files["UploadedFile"];
string type = Request.Form["type"];
excelType = EnumUtil.GetValues <ExcelName>().First(y => y.ToString() == type);
#endregion 前端無傳送檔案進來
#region 前端檔案大小不符或不為Excel檔案(驗證)
//ModelState
if (!ModelState.IsValid)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = MessageType.excel_Validate.GetDescription();
return(Json(result));
}
#endregion 前端檔案大小不符或不為Excel檔案(驗證)
#region 傳檔案
string pathType = Path.GetExtension(FileModel.FileName)
.Substring(1); //上傳的檔案類型
var fileName = string.Format("{0}.{1}",
excelType.GetDescription(),
pathType); //固定轉成此名稱
//Cache.Invalidate(CacheList.A59ExcelName); //清除 Cache
//Cache.Set(CacheList.A59ExcelName, fileName); //把資料存到 Cache
#region 檢查是否有FileUploads資料夾,如果沒有就新增 並加入 excel 檔案
string projectFile = Server.MapPath("~/" + SetFile.FileUploads); //專案資料夾
string path = Path.Combine(projectFile, fileName);
FileRelated.createFile(projectFile); //檢查是否有FileUploads資料夾,如果沒有就新增
//呼叫上傳檔案 function
result = FileRelated.FileUpLoadinPath <string>(path, FileModel);
if (!result.RETURN_FLAG)
{
return(Json(result));
}
#endregion 檢查是否有FileUploads資料夾,如果沒有就新增 並加入 excel 檔案
#region 讀取Excel資料 使用ExcelDataReader 並且組成 json
var stream = FileModel.InputStream;
IEnumerable <IFileModel> dataModel = null;
var Excelresult = FileService.getExcel(pathType, path, excelType);
if (Excelresult.Item1.IsNullOrWhiteSpace())
{
switch (excelType)
{
case ExcelName.Marginp:
dataModel = Excelresult.Item2.Cast <FileMarginpModel>().ToList();
break;
case ExcelName.Marging:
dataModel = Excelresult.Item2.Cast <FileMargingModel>().ToList();
break;
case ExcelName.Stock:
dataModel = Excelresult.Item2.Cast <FileStockModel>().ToList();
break;
case ExcelName.Itemimp:
dataModel = Excelresult.Item2.Cast <FileItemImpModel>().ToList();
break;
}
}
else
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Excelresult.Item1;
return(Json(result));
}
if (dataModel.Any())
{
result.RETURN_FLAG = true;
Cache.Invalidate(CacheList.ExcelfileData); //清除 Cache
Cache.Set(CacheList.ExcelfileData, dataModel); //把資料存到 Cache
}
else
{
result.RETURN_FLAG = false;
result.DESCRIPTION = MessageType.data_Not_Compare.GetDescription();
}
#endregion 讀取Excel資料 使用ExcelDataReader 並且組成 json
#endregion 傳檔案
}
catch (Exception ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = ex.Message;
}
return(Json(result));
}
public static IEnumerable <IFileModel> ISheetToFileModel(this ISheet sh, ExcelName name, bool Despotion = true)
{
List <IFileModel> results = new List <IFileModel>();
int i = 2; //預設從第三行開始
if (!Despotion)
{
i = 1; //沒有Title從第二行開始
}
if (sh.GetRow(0) != null)
{
var pros = FactoryRegistry.GetInstance(name).GetType()
.GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();
while (sh.GetRow(i) != null)
{
var cls = FactoryRegistry.GetInstance(name);
bool addFlag = false;
// write row value
for (int j = 0; j < sh.GetRow(i).LastCellNum; j++)
{
var cell = sh.GetRow(0).GetCell(j);
var _name = cell?.ToString().ToUpper().Trim();
var p = pros.FirstOrDefault(y => y.Name.ToUpper() == _name);
if (p == null)
{
continue;
}
// If not writable then cannot null it; if not readable then cannot check it's value
if (!p.CanWrite || !p.CanRead)
{
continue;
}
MethodInfo mget = p.GetGetMethod(false);
MethodInfo mset = p.GetSetMethod(false);
// Get and set methods have to be public
if (mget == null)
{
continue;
}
if (mset == null)
{
continue;
}
var value = sh.GetRow(i).GetCell(j);
if (value != null)
{
switch (value.CellType)
{
case CellType.Numeric:
p.SetValue(cls, value.NumericCellValue.ToString());
addFlag = true;
break;
case CellType.String:
var _value = value.StringCellValue;
if (!_value.IsNullOrWhiteSpace())
{
addFlag = true;
}
p.SetValue(cls, value.StringCellValue);
break;
}
}
}
i++;
if (addFlag)
{
results.Add(cls);
}
}
}
return(results);
}
private static void ExcelSetValue(ISheet ws, DataTable dt, ExcelName type, List <FormateTitle> titles = null)
{
ws.CreateRow(0);//第一行為欄位名稱
//if (type == Excel_DownloadName.A95)
//{
// for (int i = 0; i < dt.Columns.Count; i++)
// {
// ws.GetRow(0).CreateCell(i).SetCellValue((dt.Columns[i].ColumnName).formateTitle(titles));
// }
//}
//if (type == Excel_DownloadName.C07AdvancedSum)
//{
// ws.GetRow(0).CreateCell(0).SetCellValue(
// (dt.Columns[0].ColumnName).formateTitle(titles) + "："
// + dt.Rows[0]["報導日"].ToString()
// + " "
// + (dt.Columns[1].ColumnName).formateTitle(titles) + "："
// + dt.Rows[0]["版本"].ToString()
// + " "
// + "產品："
// + dt.Rows[0]["產品群代碼"].ToString() + " " + dt.Rows[0]["產品群名稱"].ToString()
// );
// ws.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3));
// ws.CreateRow(1);
// ws.GetRow(1).CreateCell(0).SetCellValue("預期信用損失統計_台幣(報導日匯率)");
// ws.AddMergedRegion(new CellRangeAddress(1, 1, 0, 3));
// ws.CreateRow(2);
// for (int i = 0; i < dt.Columns.Count - 4; i++)
// {
// ws.GetRow(2).CreateCell(i).SetCellValue((dt.Columns[i + 4].ColumnName).formateTitle(titles));
// }
//}
//else
//{
// for (int i = 0; i < dt.Columns.Count; i++)
// {
// ws.GetRow(0).CreateCell(i).SetCellValue((dt.Columns[i].ColumnName).formateTitle(titles));
// }
//}
//if (type == Excel_DownloadName.A59)
//{
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// ws.CreateRow(i + 1);
// for (int j = 0; j < dt.Columns.Count; j++)
// {
// ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
// }
// }
//}
//else if (type == Excel_DownloadName.A72 || type == Excel_DownloadName.A73)
//{
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// ws.CreateRow(i + 1);
// for (int j = 0; j < dt.Columns.Count; j++)
// {
// if (0.Equals(j)) //第一行固定為 string
// {
// ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
// }
// else //後面皆為 double
// {
// ws.GetRow(i + 1).CreateCell(j).SetCellValue(Convert.ToDouble(dt.Rows[i][j]));
// }
// }
// }
//}
//else if (type == Excel_DownloadName.A95)
//{
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// ws.CreateRow(i + 1);
// for (int j = 0; j < dt.Columns.Count - 2; j++) //Bond_Type,Assessment_Sub_Kind 由程式產生
// {
// ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
// }
// }
//}
//else if (type == Excel_DownloadName.C07Mortgage || type == Excel_DownloadName.C07Bond)
//{
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// ws.CreateRow(i + 1);
// for (int j = 0; j < dt.Columns.Count; j++)
// {
// ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
// }
// }
//}
//else if (type == Excel_DownloadName.D62 || type == Excel_DownloadName.D64)
//{
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// ws.CreateRow(i + 1);
// for (int j = 0; j < dt.Columns.Count; j++)
// {
// ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
// }
// }
//}
//else if (type == Excel_DownloadName.C07AdvancedSum)
//{
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// ws.CreateRow(i + 3);
// for (int j = 0; j < dt.Columns.Count - 4; j++)
// {
// ws.GetRow(i + 3).CreateCell(j).SetCellValue(dt.Rows[i][j + 4].ToString());
// }
// }
//}
//else
//{
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// ws.CreateRow(i + 1);
// for (int j = 0; j < dt.Columns.Count; j++)
// {
// ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
// }
// }
//}
//for (int i = 0; i < dt.Columns.Count; i++)
//{
// ws.AutoSizeColumn(i);
//}
}
