/// <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 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));
}