public IActionResult UploadFile(IFormFile file)
{
FileService FileService = new FileService(_hostingEnvironment, _context);
m_ConfigurationPartial model = new m_ConfigurationPartial();
ExcelHepler Helper = new ExcelHepler(_hostingEnvironment);
string userId = _userManager.GetUserId(HttpContext.User);
string userName = HttpContext.User.Identity.Name;
Stream stream = file.OpenReadStream();
//轉NPOI類型
XSSFWorkbook ExcelFile = new XSSFWorkbook(stream);
if (Helper.ExcelSampleCheck(ExcelFile))
{
////存檔並返回檔案路徑
//string FilePath = Helper.SaveAndGetExcelPath(file);
//if (FilePath != "Error")
//{
// model.CNS_Sample.FileFullName = Path.GetFileName(FilePath);
// model.CNS_Sample.FileName = file.FileName;
// model.CNS_Sample.FileExtension = Path.GetExtension(FilePath);
// model.CNS_Sample.FileType = 2;
// model.CNS_Sample.FileCategory = 1;
// model.CNS_Sample.FileSize = file.Length;
// model.CNS_Sample.FunctionName = "ConfigurationSample";
// model.CNS_Sample.Creator = "c5805dbf-dac5-41e6-bb72-5eb0b449134d";
// model.CNS_Sample.CreatorDate = DateTime.Now;
// model.CNS_Sample.CreatorName = "*****@*****.**";
// _context.PDC_File.Add(model.CNS_Sample);
// _context.SaveChanges();
//}
PDC_File item = new PDC_File();
if (!FileService.FileAdd(file, "ConfigurationSample", userId, userName, out item))
{
model.ErrorMsg = "檔案儲存失敗";
}
model.CNS_Sample = item;
}
else
{
model.ErrorMsg = "Failed, 含多餘工作表";
}
return(Json(model));
}
public IActionResult DownloadCheckFile([FromBody] object model)
{
ExcelHepler Helper = new ExcelHepler(_hostingEnvironment);
FileService FileService = new FileService(_hostingEnvironment, _context);
var jsonString = JsonConvert.SerializeObject(model);
m_ExcelPartial ViewModel = JsonConvert.DeserializeObject <m_ExcelPartial>(jsonString);
//取得範例
MemoryStream stream = Helper.ExportExcelSample(ViewModel.StackupDetalList);
string FilePath = FileService.SaveAndGetExcelPath(stream);
string FileName = Path.GetFileName(FilePath);
return(Json(FileName));
}
public IActionResult PCBParameterAdd(string ParameterText, Int64 ParameterID, int OrderNo)
{
ExcelHepler Helper = new ExcelHepler(_hostingEnvironment);
PDC_Parameter item = new PDC_Parameter();
item.IsSync = false;
item.OrderNo = OrderNo;
item.ParameterText = ParameterText;
item.ParameterGroup = "PCBTypeList_Item";
item.ParameterParentID = ParameterID;
item.Creator = "c5805dbf-dac5-41e6-bb72-5eb0b449134d";
item.CreatorDate = DateTime.Now;
item.CreatorName = "*****@*****.**";
_context.PDC_Parameter.Add(item);
_context.SaveChanges();
return(Json(item));
}
public IActionResult HomeLinkDelete(Int64 ParameterID)
{
ExcelHepler Helper = new ExcelHepler(_hostingEnvironment);
if (_context.PDC_Parameter.Where(x => x.ParameterID == ParameterID).Any())
{
PDC_Parameter item = _context.PDC_Parameter.Where(x => x.ParameterID == ParameterID).SingleOrDefault();
_context.PDC_Parameter.Remove(item);
if (_context.PDC_File.Where(x => x.SourceID == ParameterID).Any())
{
PDC_File File = _context.PDC_File.Where(x => x.SourceID == ParameterID && x.FunctionName == "Configuration_HomeLink").SingleOrDefault();
_context.PDC_File.Remove(File);
}
_context.SaveChanges();
}
return(Json("刪除成功"));
}
public IActionResult DownloadSample()
{
ExcelHepler Helper = new ExcelHepler(_hostingEnvironment);
FileService FileService = new FileService(_hostingEnvironment, _context);
if (FileService.GetFileList("ConfigurationSample").Any())
{
PDC_File Sample = FileService.GetFileList("ConfigurationSample").FirstOrDefault();
return(RedirectToAction("Download", new { fileName = Sample.FileFullName }));
}
//取得範例
MemoryStream stream = Helper.ExportExcelSample();
string sFileName = HttpUtility.UrlEncode("Sample.xlsx");
return(File(stream.ToArray(), "application/vnd.ms-excel", sFileName));
}
/// <summary>
/// Создание списка учасников для групировки из документа excel
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private List <SortableCompetitor> BindCompetotorsList(string filePath)
{
List <SortableCompetitor> result = new List <SortableCompetitor>();
Result <DataSet> dataSetResult = ExcelHepler.ImportFromExcel(filePath);
if (!dataSetResult.Success)
{
return(result);
}
DataSet dataSet = dataSetResult.Data;
// now we get data from first page
if (!ExcelHepler.ExistsMinimumColumnsCount(dataSet.Tables[0], Constants.COL_COUNT_IN_IMPOERTED_FILE).Success)
{
return(result);
}
List <ImportedCompetitor> importedCompetitorsList = BindImpoertedCompetitorsListByDataSet(dataSet);
List <SortableCompetitor> compertitorsList = BindSoratableCompetitorsList(importedCompetitorsList);
return(compertitorsList);
}
public IActionResult DownExcel(string data)
{
List <JObject> dowExcels = JsonConvert.DeserializeObject <List <JObject> >(data);
IWorkbook workbook;
workbook = new HSSFWorkbook();
//赋予表格默认样式，因为样式文件存储只能有4000个
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
cellStyle.WrapText = true; //自动换行
cellStyle.Alignment = HorizontalAlignment.Center; //水平居中
cellStyle.FillPattern = FillPattern.NoFill; //背景色是必须要这数据
cellStyle.FillForegroundColor = IndexedColors.White.Index; //默认背景色
foreach (var item in dowExcels)
{
var columnlen = item["config"] != null ? item["config"]["columnlen"] : null;
var rowlen = item["config"] != null ? item["config"]["rowlen"] : null;
var borderInfo = item["config"] != null ? item["config"]["borderInfo"] : null;
var merge = item["config"] != null ? item["config"]["merge"] : null;
var Cjarray = item["celldata"];
//读取了模板内所有sheet内容
ISheet sheet = workbook.CreateSheet(item["name"].ToString());
//判断是否有值，并且赋予样式
if (Cjarray != null)
{
for (int i = 0; i < Cjarray.Count(); i++)
{
//判断行，存不存在，不存在创建
IRow row = sheet.GetRow(int.Parse(Cjarray[i]["r"].ToString()));
if (row == null)
{
row = sheet.CreateRow(int.Parse(Cjarray[i]["r"].ToString()));
}
var ct = Cjarray[i]["v"]["ct"];
var cct = Cjarray[i]["v"];
if (ct != null && ct["s"] != null)
{
//合并单元格的走这边
string celldatas = "";
//合并单元格时，会导致文字丢失，提前处理文字信息
for (int j = 0; j < ct["s"].Count(); j++)
{
var stv = ct["s"][j];
celldatas += stv["v"] != null ? stv["v"].ToString() : "";
}
//判断列，不存在创建
ICell Cell = row.GetCell(int.Parse(Cjarray[i]["c"].ToString()));
if (Cell == null)
{
HSSFRichTextString richtext = new HSSFRichTextString(celldatas);
Cell = row.CreateCell(int.Parse(Cjarray[i]["c"].ToString()));
for (int k = 0; k < ct["s"].Count(); k++)
{
IFont font = workbook.CreateFont();
var stv = ct["s"][k];
//文字颜色
if (stv["fc"] != null)
{
var rGB = HTMLColorTranslator.Translate(stv["fc"].ToString());
var color = Color.FromArgb(rGB.R, rGB.G, rGB.B);
font.Color = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).Indexed;
}
else
{
font.Color = HSSFColor.Black.Index;
}
//是否加粗
if (stv["bl"] != null)
{
font.IsBold = !string.IsNullOrEmpty(stv["bl"].ToString()) && (stv["bl"].ToString() == "1" ? true : false);
font.Boldweight = stv["bl"].ToString() == "1" ? (short)FontBoldWeight.Bold : (short)FontBoldWeight.None;
}
else
{
font.IsBold = false;
font.Boldweight = (short)FontBoldWeight.None;
}
//是否斜体
if (stv["it"] != null)
{
font.IsItalic = !string.IsNullOrEmpty(stv["it"].ToString()) && (stv["it"].ToString() == "1" ? true : false);
}
else
{
font.IsItalic = false;
}
//下划线
if (stv["un"] != null)
{
font.Underline = stv["un"].ToString() == "1" ? FontUnderlineType.Single : FontUnderlineType.None;
}
else
{
font.Underline = FontUnderlineType.None;
}
//字体
if (stv["ff"] != null)
{
font.FontName = stv["ff"].ToString();
}
//文字大小
if (stv["fs"] != null)
{
font.FontHeightInPoints = double.Parse(stv["fs"].ToString());
}
Cell.CellStyle.SetFont(font);
richtext.ApplyFont(celldatas.IndexOf(stv["v"].ToString()), celldatas.IndexOf(stv["v"].ToString()) + stv["v"].ToString().Length, font);
Cell.SetCellValue(richtext);
}
//背景颜色
if (cct["bg"] != null)
{
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.CloneStyleFrom(cellStyle);
if (cct["bg"] != null)
{
var rGB = HTMLColorTranslator.Translate(cct["bg"].ToString());
var color = Color.FromArgb(rGB.R, rGB.G, rGB.B);
cellStyle1.FillPattern = FillPattern.SolidForeground;
cellStyle1.FillForegroundColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).Indexed;
}
Cell.CellStyle = cellStyle1;
}
else
{
Cell.CellStyle = cellStyle;
}
}
}
else
{
//没有合并单元格的走这边
//判断列，不存在创建
ICell Cell = row.GetCell(int.Parse(Cjarray[i]["c"].ToString()));
if (Cell == null)
{
Cell = row.CreateCell(int.Parse(Cjarray[i]["c"].ToString()));
IFont font = workbook.CreateFont();
ct = Cjarray[i]["v"];
//字体颜色
if (ct["fc"] != null)
{
var rGB = HTMLColorTranslator.Translate(ct["fc"].ToString());
var color = Color.FromArgb(rGB.R, rGB.G, rGB.B);
font.Color = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).Indexed;
}
else
{
font.Color = HSSFColor.Black.Index;
}
//是否加粗
if (ct["bl"] != null)
{
font.IsBold = !string.IsNullOrEmpty(ct["bl"].ToString()) && (ct["bl"].ToString() == "1" ? true : false);
font.Boldweight = ct["bl"].ToString() == "1" ? (short)FontBoldWeight.Bold : (short)FontBoldWeight.None;
}
else
{
font.IsBold = false;
font.Boldweight = (short)FontBoldWeight.None;
}
//斜体
if (ct["it"] != null)
{
font.IsItalic = !string.IsNullOrEmpty(ct["it"].ToString()) && (ct["it"].ToString() == "1" ? true : false);
}
else
{
font.IsItalic = false;
}
//下划线
if (ct["un"] != null)
{
font.Underline = ct["un"].ToString() == "1" ? FontUnderlineType.Single : FontUnderlineType.None;
}
else
{
font.Underline = FontUnderlineType.None;
}
//字体
if (ct["ff"] != null)
{
font.FontName = ct["ff"].ToString();
}
//文字大小
if (ct["fs"] != null)
{
font.FontHeightInPoints = double.Parse(ct["fs"].ToString());
}
Cell.CellStyle.SetFont(font);
//判断背景色
if (ct["bg"] != null)
{
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.CloneStyleFrom(cellStyle);
if (ct["bg"] != null)
{
var rGB = HTMLColorTranslator.Translate(ct["bg"].ToString());
var color = Color.FromArgb(rGB.R, rGB.G, rGB.B);
cellStyle1.FillPattern = FillPattern.SolidForeground;
cellStyle1.FillForegroundColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).Indexed;
}
Cell.CellStyle = cellStyle1;
}
else
{
Cell.CellStyle = cellStyle;
}
Cell.SetCellValue(ct["v"] != null ? ct["v"].ToString() : "");
}
}
}
sheet.ForceFormulaRecalculation = true;
}
//判断是否要设置列宽度
if (columnlen != null)
{
foreach (var cols in columnlen)
{
var p = cols as JProperty;
sheet.SetColumnWidth(int.Parse(p.Name), int.Parse(p.Value.ToString()) * 38);
}
}
//判断是否要设置行高度
if (rowlen != null)
{
foreach (var rows in rowlen)
{
var p = rows as JProperty;
sheet.GetRow(int.Parse(p.Name)).HeightInPoints = float.Parse(p.Value.ToString());
}
}
//判断是否要加边框
if (borderInfo != null)
{
for (int i = 0; i < borderInfo.Count(); i++)
{
var bordervalue = borderInfo[i]["value"];
if (bordervalue != null)
{
var rowindex = bordervalue["row_index"];
var colindex = bordervalue["col_index"];
var l = bordervalue["l"];
var r = bordervalue["r"];
var t = bordervalue["t"];
var b = bordervalue["b"];
if (rowindex != null)
{
IRow rows = sheet.GetRow(int.Parse(bordervalue["row_index"].ToString()));
if (colindex != null)
{
ICell cell = rows.GetCell(int.Parse(bordervalue["col_index"].ToString()));
if (b != null)
{
cell.CellStyle.BorderBottom = ExcelHepler.GetBorderStyle(int.Parse(b["style"].ToString()));
var rGB = HTMLColorTranslator.Translate(b["color"].ToString());
var bcolor = Color.FromArgb(rGB.R, rGB.G, rGB.B);
cell.CellStyle.BottomBorderColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(bcolor.R, bcolor.G, bcolor.B).Indexed;
}
else
{
cell.CellStyle.BorderBottom = BorderStyle.None;
cell.CellStyle.BottomBorderColor = HSSFColor.COLOR_NORMAL;
}
if (t != null)
{
cell.CellStyle.BorderTop = ExcelHepler.GetBorderStyle(int.Parse(t["style"].ToString()));
var rGB = HTMLColorTranslator.Translate(t["color"].ToString());
var tcolor = Color.FromArgb(rGB.R, rGB.G, rGB.B);
cell.CellStyle.TopBorderColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(tcolor.R, tcolor.G, tcolor.B).Indexed;
}
else
{
cell.CellStyle.BorderBottom = BorderStyle.None;
cell.CellStyle.BottomBorderColor = HSSFColor.COLOR_NORMAL;
}
if (l != null)
{
cell.CellStyle.BorderLeft = ExcelHepler.GetBorderStyle(int.Parse(l["style"].ToString()));
var rGB = HTMLColorTranslator.Translate(l["color"].ToString());
var lcolor = Color.FromArgb(rGB.R, rGB.G, rGB.B);
cell.CellStyle.LeftBorderColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(lcolor.R, lcolor.G, lcolor.B).Indexed;
}
else
{
cell.CellStyle.BorderBottom = BorderStyle.None;
cell.CellStyle.BottomBorderColor = HSSFColor.COLOR_NORMAL;
}
if (r != null)
{
cell.CellStyle.BorderRight = ExcelHepler.GetBorderStyle(int.Parse(r["style"].ToString()));
var rGB = HTMLColorTranslator.Translate(r["color"].ToString());
var rcolor = Color.FromArgb(rGB.R, rGB.G, rGB.B);
cell.CellStyle.RightBorderColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(rcolor.R, rcolor.G, rcolor.B).Indexed;
}
else
{
cell.CellStyle.BorderBottom = BorderStyle.None;
cell.CellStyle.BottomBorderColor = HSSFColor.COLOR_NORMAL;
}
}
}
}
}
}
//判断是否要合并单元格
if (merge != null)
{
foreach (var imerge in merge)
{
var firstmer = imerge.First();
int r = int.Parse(firstmer["r"].ToString()); //主单元格的行号,开始行号
int rs = int.Parse(firstmer["rs"].ToString()); //合并单元格占的行数,合并多少行
int c = int.Parse(firstmer["c"].ToString()); //主单元格的列号,开始列号
int cs = int.Parse(firstmer["cs"].ToString()); //合并单元格占的列数,合并多少列
CellRangeAddress region = new CellRangeAddress(r, r + rs - 1, c, c + cs - 1);
sheet.AddMergedRegion(region);
}
}
}
var dir = Path.Combine(Directory.GetCurrentDirectory(), @"wwwroot\Files");
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
var fileId = Guid.NewGuid();
var fileName = $"{fileId}.xls";
FileStream stream = new FileStream(Path.Combine(dir, fileName), FileMode.OpenOrCreate);
workbook.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
workbook.Close();
stream.Close();
return(Ok(fileId));
}
public IActionResult HomeLinkAdd(IFormFile file, string ParameterText, int IsSync, string Link, int HomeLinkOrderNo)
{
ExcelHepler Helper = new ExcelHepler(_hostingEnvironment);
ParameterService parameterService = new ParameterService(_context);
FileService FileService = new FileService(_hostingEnvironment, _context);
string userId = _userManager.GetUserId(HttpContext.User);
string userName = HttpContext.User.Identity.Name;
m_ConfigurationPartial ViewModel = new m_ConfigurationPartial();
if (!string.IsNullOrEmpty(Link))
{
string ErrorMsg = string.Empty;
PDC_Parameter NewParameter = new PDC_Parameter();
NewParameter.IsSync = IsSync == 1 ? true : false;
NewParameter.OrderNo = HomeLinkOrderNo;
NewParameter.ParameterText = ParameterText;
NewParameter.ParameterGroup = "Configuration_HomeLink";
NewParameter.Creator = userId;
NewParameter.CreatorName = userName;
NewParameter.CreatorDate = DateTime.Now;
if (parameterService.AddParameter(ref NewParameter, ref ErrorMsg))
{
PDC_File NewFile = new PDC_File();
NewFile.SourceID = NewParameter.ParameterID;
NewFile.FunctionName = "Configuration_HomeLink";
NewFile.FileType = 5;
NewFile.FileName = Link;
NewFile.FileCategory = 2;
NewFile.Creator = userId;
NewFile.CreatorName = userName;
NewFile.CreatorDate = DateTime.Now;
FileService.FileAdd(ref NewFile);
ViewModel.m_HomeLink = NewParameter;
ViewModel.m_HomeLinkFile = NewFile;
}
}
else
{
//存檔並返回檔案路徑
string FilePath = Helper.SaveAndGetExcelPath(file);
if (FilePath != "Error")
{
ViewModel.m_HomeLink.IsSync = IsSync == 1 ? true : false;
ViewModel.m_HomeLink.OrderNo = HomeLinkOrderNo;
ViewModel.m_HomeLink.ParameterText = ParameterText;
ViewModel.m_HomeLink.ParameterGroup = "Configuration_HomeLink";
ViewModel.m_HomeLink.Creator = "m901c018";
ViewModel.m_HomeLink.CreatorDate = DateTime.Now;
ViewModel.m_HomeLink.CreatorName = "Mike";
_context.PDC_Parameter.Add(ViewModel.m_HomeLink);
_context.SaveChanges();
ViewModel.m_HomeLinkFile.SourceID = ViewModel.m_HomeLink.ParameterID;
ViewModel.m_HomeLinkFile.FunctionName = "Configuration_HomeLink";
ViewModel.m_HomeLinkFile.FileFullName = Path.GetFileName(FilePath);
ViewModel.m_HomeLinkFile.FileName = file.FileName;
ViewModel.m_HomeLinkFile.FileExtension = Path.GetExtension(FilePath);
ViewModel.m_HomeLinkFile.FileType = 2;
ViewModel.m_HomeLinkFile.FileCategory = 1;
ViewModel.m_HomeLinkFile.FileSize = file.Length;
ViewModel.m_HomeLinkFile.Creator = "m901c018";
ViewModel.m_HomeLinkFile.CreatorDate = DateTime.Now;
ViewModel.m_HomeLinkFile.CreatorName = "Mike";
_context.PDC_File.Add(ViewModel.m_HomeLinkFile);
_context.SaveChanges();
}
}
//存檔並返回檔案路徑UserManager<IdentityUser>
//string FilePath = Helper.SaveAndGetExcelPath(file);
return(Json(ViewModel));
}
