public static ExcelPackage WStoExport(ExcelPackage ex, tempJson cList) // make chart here
{
List <string> tempString = new List <string>();
FileInfo nf = new FileInfo("C:\\Users\\bzaffiro\\workrepos\\New_Data.xlsx");
if (cList == null)
{
return(null);
}
ex.Workbook.Worksheets.Add("Cool Tab"); // Adds worksheet to workbook
ex.Workbook.Worksheets.Add("Data_File");
// Adding data into Tab 2: Worksheet 1 == Data_File
ex.Workbook.Worksheets[1].Cells.LoadFromCollection(cList.position); // puts position List<t> to worksheet 2
//ExcelRange data = ex.Workbook.Worksheets[1].Cells;
//ex = GetWSOneVals(ex);
// var data2 = data.
ex = CreatingChart(ex, cList);
// example to load code into list
cList.position.ForEach(x => // thats how you loop with Linq
{
tempString.Add(Convert.ToString(x.CompanyId)); //looping through all of the candidateIds
});
ex.SaveAs(nf);
return(ex);
}
public ExcelPackage EPPJsontoWS(tempJson cList) // List<t>
{
using (ExcelPackage ex = new ExcelPackage())
{
return(WStoExport(ex, cList));
}
}
public tempJson EPPFiletoWS(FileInfo fi, IFormFile file)
{
tempJson tempjson = new tempJson();
switch (fi.Extension)
{
case ".xlsx":
using (ExcelPackage ex = new ExcelPackage())
{
ExcelWorksheet ws = ex.Workbook.Worksheets.Add("Sheet 1");
ExcelWorksheet ws2 = ex.Workbook.Worksheets.Add("Sheet 2"); // Create WS
var format = new ExcelTextFormat {
Delimiter = '\t', EOL = "\r"
};
ws.Cells["A1"].LoadFromText(fi, format);
WStoExport(ex, tempjson);
}
break;
case ".csv":
break;
}
return(null);
}
//=== Json ==//
public tempJson JsonToEPPlus() // GET Master/JsonToEPPlus
{
// Declarations
tempJson tempjson = new tempJson();
EPlusPlus eppService = new EPlusPlus();
tempjson = GetAndConvJson(tempjson); // Call tempJson and convert
return(eppService.EPPJsontoWS(tempjson)); // Create workbook
}
//=== Json ===/
public tempJson JsonToAspose() // GET /Master/JsonToAspose
{
// Declarations
tempJson tempjson = new tempJson();
AsposeExcel service = new AsposeExcel();
tempjson = GetAndConvJson(tempjson); // Call tempJson and convert
return(service.AsposeJsontoWB(tempjson)); // Create workbook
}
//=== Json Converter ===//
public tempJson GetAndConvJson(tempJson tj) // Converts json file to list<>
{
// Set Path
string posPath = Path.Combine(Directory.GetCurrentDirectory(), "Json", "Position.json");
string compPath = Path.Combine(Directory.GetCurrentDirectory(), "Json", "Company.json");
string canPath = Path.Combine(Directory.GetCurrentDirectory(), "Json", "Candidate.json");
List <string> paths = new List <string> {
posPath, compPath, canPath
};
using (StreamReader r = new StreamReader(posPath))
tj.position = JsonConvert.DeserializeObject <List <Position> >(r.ReadToEnd());
using (StreamReader r = new StreamReader(compPath))
tj.company = JsonConvert.DeserializeObject <List <Company> >(r.ReadToEnd());
using (StreamReader r = new StreamReader(posPath))
tj.position = JsonConvert.DeserializeObject <List <Position> >(r.ReadToEnd());
return(tj);
}
public static ExcelPackage CreatingChart(ExcelPackage ex, tempJson cList) // Create Chart code
{
var chart = ex.Workbook.Worksheets[0].Drawings.AddChart("Cool Chart", OfficeOpenXml.Drawing.Chart.eChartType.Line); //adding chart
int lastRow = ex.Workbook.Worksheets[1].Cells.Where(cell => !cell.Value.ToString().Equals("")).Last().End.Row;
int lastColumn = ex.Workbook.Worksheets[1].Cells.Where(cell => !cell.Value.ToString().Equals("")).Last().End.Column;
var a = "A";
var range1 = string.Concat(a, lastRow);
var range2 = string.Concat(a, lastColumn);
chart.Series.Add(range1, range2);
// chart.Series.Add(ex.Workbook.Worksheets[1].Cells["A1:A + lastRow"], ex.Workbook.Worksheets[1].Cells["A1:A2"]);
// Loop to determine chart
#region code
// foreach (var cell in ex.Workbook.Worksheets[1].Cells[1, 1, 1, ex.Workbook.Worksheets[1].Dimension.End.Column])
// {
// var data = cell.Value;
// // for (int i = 2; i <= ex.Workbook.Worksheets[1].Dimension.End.Row; i++) {
// // Enumerable.Range(ex.Workbook.Worksheets[1].Dimension.Start.Row + 1, ex.Workbook.Worksheets[1].Dimension.End.Row).Select(i => Convert.ToString(ex.Workbook.Worksheets[1].Cells[i, 1].Value));
// //ExcelRange range = ex.Workbook.Worksheets[1].Cells;
// // chart.Series.Add(ex.Workbook.Worksheets[1].Cells[data], ex.Workbook.Worksheets[1].Cells[data]);
// // }
// }
#endregion code
// ex.Workbook.Worksheets[0].Drawings[0].SetSize(800, 600);
//ex.Workbook.Worksheets[0].Drawings[0]. System.Drawing.Color.Green;
//myChart.Border.Fill.Color = System.Drawing.Color.Green;
//consumptionWorksheet.Cells[1, 1].LoadFromCollection(consumptionComparisonDetails, false, OfficeOpenXml.Table.TableStyles.Medium1);
return(ex);
}
public tempJson EPPJsontoWS(tempJson tj)
{
return(null);
}
public tempJson AsposeJsontoWB(tempJson tj)
{
return(null);
}