/// <summary>
/// To create object of DIExcel
/// </summary>
/// <param name="excelFileName">Excel file name with path</param>
/// <param name="culture">Specify the Cultrue info</param>
public DIExcel(string excelFileName,CultureInfo culture )
{
this.ExcelFileName = excelFileName;
//Get workbook from workbook factory.
this.Workbook = SpreadsheetGear.Factory.GetWorkbook(this.ExcelFileName,culture);
}
/// <summary>
/// To create object of DIExcel. Use "SaveAs(..)" method to save excel file.
/// </summary>
/// <param name="culture">Specify the Cultrue info</param>
public DIExcel(CultureInfo culture)
{
//Get workbook set from workbook factory.
IWorkbookSet WorkbookSet = Factory.GetWorkbookSet(culture);
// Create a new empty workbook in the workbook set.
this.Workbook = WorkbookSet.Workbooks.Add();
}
private void CopyToNew(ref IWorkbook interim, int columnId, string cell) // this method copy one column to interrim column worksheet
{
SpreadsheetGear.IWorkbookSet workbookset = SpreadsheetGear.Factory.GetWorkbookSet();
SpreadsheetGear.IWorkbook workbook = workbookset.Workbooks.OpenFromMemory(GetWorkbookFile(columnId));
string columnName = GetRequiredCell(columnId).ColumnName;
string worksheetName = GetCellWorksheetName(columnId);
string address = FindAddressByName(workbook.Worksheets[worksheetName], columnName);
string columnLast = FindLastRow(address, workbook.Worksheets[worksheetName], GetRequiredCell(columnId).StartRow);
int diference = GetRequiredCell(columnId).StartRow - GetRequiredCell(columnId).HeaderRow;
address = Modify(diference, address);
workbook.Worksheets[worksheetName].Cells[address + ":" + columnLast].Copy(interim.Worksheets[0].Cells[cell]);
}
private bool FormulaEvalute(string text) // this method check if formula is right write
{
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
var val = workbook.Worksheets["Sheet1"].EvaluateValue(text);
if (val != null && (val is double || val is string))
{
return(true);
}
else
{
return(false);
}
}
public string GetOneData(int cellId) //this method gives us one data
{
SpreadsheetGear.IWorkbookSet workbookset = SpreadsheetGear.Factory.GetWorkbookSet();
SpreadsheetGear.IWorkbook workbook = workbookset.Workbooks.OpenFromMemory(GetWorkbookFile(cellId));
string columnName = GetRequiredCell(cellId).ColumnName;
string worksheetName = GetCellWorksheetName(cellId);
string address = FindAddressByName(workbook.Worksheets[worksheetName], columnName);
int diference = GetRequiredCell(cellId).StartRow - GetRequiredCell(cellId).HeaderRow;
address = Modify(diference, address);
string data = workbook.Worksheets[worksheetName].Cells[address].Value.ToString();
return(data);
}
private void WorksheetInitialization(int p1, byte[] p2) //initialization worksheet and call class who initialization cell
{ // p1 is worksheet id p2 is file
SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.OpenFromMemory(p2);
foreach (IWorksheet worksheet in workbook.Sheets)
{
var workSheet = new Worksheet();
workSheet.WorkbookId = p1;
workSheet.WorksheetName = worksheet.Name;
db.Worksheets.Add(workSheet);
db.SaveChanges();
var SaveCells = new OutputCellFill(worksheet, workSheet.Id); //Saving Cell to database
SaveCells.SaveToDatabase();
}
}
public void Downfile(SpreadsheetGear.IWorkbook workbook, HttpContextBase context, string excelName = "report")
{
context.Response.Clear();
context.Response.ContentEncoding = Encoding.UTF8;
if (context.Request.UserAgent.ToUpper().IndexOf("TRIDENT") > -1)
{
excelName = HttpUtility.UrlEncode(excelName, Encoding.UTF8);
}
//context.Response.ContentType = "application/vnd.ms-excel";
//context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", excelName));
//workbook.SaveToStream(context.Response.OutputStream, FileFormat.XLS97);
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", excelName));
workbook.SaveToStream(context.Response.OutputStream, SpreadsheetGear.FileFormat.OpenXMLWorkbook);
context.Response.End();
}
private byte[] GetOutFile(int columnWhere)
{
SpreadsheetGear.IWorkbookSet workbookset = SpreadsheetGear.Factory.GetWorkbookSet();
SpreadsheetGear.IWorkbook interim = workbookset.Workbooks.Add();
SpreadsheetGear.IWorkbook workbookWhere = workbookset.Workbooks.OpenFromMemory(GetWhereToCopy(columnWhere));
Cell requiredcell = GetRequiredCell(columnWhere); // this 3 data until number need to workbookWhere
string name = GetCellWorksheetName(columnWhere);
string address = FindColumnWhereCopy(workbookWhere, requiredcell.ColumnName, name);
PutemporaryData(ref interim);
int number = interim.Worksheets[0].Cells["A1"].CurrentRegion.RowCount; // number to need for cycle
if (actions.Length == 2) // because last element is ""
{
return(JustCopyOperation(requiredcell, interim, workbookWhere, name, address, number));
}
return(MathActions(interim, workbookWhere, number, requiredcell, name, address));
}
public void PrintWareHouses(List <WareHousesDTO> wareHouseList, StoreNamesDTO storeName)
{
storeNameDTO = storeName;
SpreadsheetGear.IWorkbook workbook = Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[0];
SpreadsheetGear.IRange cells = worksheet.Cells;
Dictionary <string, byte> HeaderColumn = new Dictionary <string, byte>();
cellList = wareHouseList;
int line = storeNameDTO.LineCount ?? 0;
int column = storeNameDTO.ColumnCount ?? 0;
int cell = storeNameDTO.CellCount ?? 0;
int k = 0;
int startPosition = 1;
int currentPosition = 3;
byte startHeaderPosition = 1;
# region Header
//this method calculate custom formula to required interim workbook column
private void CostumFormulaToNew(ref IWorkbook interim, string p, string cell) // p is customformula
{
SpreadsheetGear.IWorkbookSet workbookset = SpreadsheetGear.Factory.GetWorkbookSet();
SpreadsheetGear.IWorkbook costumFormula = workbookset.Workbooks.Add();
if (customcolumns[number] != "#")
{
string[] cColumns = customcolumns[number].Split(' ');
for (int i = 0; i < cColumns.Length - 1; i++)
{
string customCell = columnChar[i] + 1.ToString();
CopyToNew(ref costumFormula, int.Parse(cColumns[i]), customCell);// prepare costum formula required columns
}
int quantity = costumFormula.Worksheets[0].Cells["A1"].CurrentRegion.RowCount;
PerformCustomFormula(ref costumFormula, p, cColumns.Length - 1, customnames[number].Split(' '), quantity);
costumFormula.Worksheets[0].Cells["AA1:" + "AA" + quantity.ToString()].Copy(interim.Worksheets[0].Cells[cell]);
}
else
{
interim.Worksheets[0].Cells[cell].Value = costumFormula.Worksheets[0].EvaluateValue(p);
}
number++;
}
/// <summary>
/// Builds a xy graph
/// </summary>
/// <param name="dataWorksheet"></param>
/// <param name="xyGraph"></param>
/// <param name="columnNameIndex"></param>
private static void BuildXYGraph(IWorksheet dataWorksheet, XYGraphBE xyGraphConfig, Dictionary <string, int> columnNameIndex, string pathNameColumnName)
{
SpreadsheetGear.IWorkbook workbook = dataWorksheet.Workbook;
List <string> missingColumnNames = new List <string>();
//// step 3: find the columns we want to reference for the Gains
string pidGainsColumnName = xyGraphConfig.Gains?.PIDGains;
string followerGainsColumnName = xyGraphConfig.Gains?.FollowerGains;
string controlModeColumnName = xyGraphConfig.Gains?.ControlMode;
int pidGainsColumnIdx = -1;
int followerGainsColumnIdx = -1;
int controlModeColumnIdx = -1;
int elapsedDeltaColumnIdx = -1;
int targetColumnIdx = -1;
int actualColumnIdx = -1;
int pathNameColumnIdx = -1;
if (!string.IsNullOrEmpty(pidGainsColumnName))
{
if (!columnNameIndex.TryGetValue(pidGainsColumnName, out pidGainsColumnIdx))
{
//missingColumnNames.Add(pidGainsColumnName);
}
}
if (!string.IsNullOrEmpty(followerGainsColumnName))
{
if (!columnNameIndex.TryGetValue(followerGainsColumnName, out followerGainsColumnIdx))
{
missingColumnNames.Add(followerGainsColumnName);
}
}
if (!string.IsNullOrEmpty(controlModeColumnName))
{
if (!columnNameIndex.TryGetValue(controlModeColumnName, out controlModeColumnIdx))
{
//missingColumnNames.Add(controlModeColumnName);
}
}
//if (!string.IsNullOrEmpty(lineGraphConfig.XAxis.FromColumnName))
//{
// if (!columnNameIndex.TryGetValue(lineGraphConfig.XAxis.FromColumnName, out elapsedDeltaColumnIdx))
// {
// missingColumnNames.Add(lineGraphConfig.XAxis.FromColumnName);
// }
//}
//if (!string.IsNullOrEmpty(lineGraphConfig.CalcAreaDelta?.TargetColumnName))
//{
// if (!columnNameIndex.TryGetValue(lineGraphConfig.CalcAreaDelta.TargetColumnName, out targetColumnIdx))
// {
// missingColumnNames.Add(lineGraphConfig.CalcAreaDelta.TargetColumnName);
// }
//}
//if (!string.IsNullOrEmpty(lineGraphConfig.CalcAreaDelta?.ActualColumnName))
//{
// if (!columnNameIndex.TryGetValue(lineGraphConfig.CalcAreaDelta.ActualColumnName, out actualColumnIdx))
// {
// missingColumnNames.Add(lineGraphConfig.CalcAreaDelta.ActualColumnName);
// }
//}
if (!string.IsNullOrEmpty(pathNameColumnName))
{
if (!columnNameIndex.TryGetValue(pathNameColumnName, out pathNameColumnIdx))
{
missingColumnNames.Add(pathNameColumnName);
}
}
// stop if any were missing
if (missingColumnNames.Count > 0)
{
string errList = String.Join(",", missingColumnNames);
throw new ApplicationException($"... Error building graph: [{xyGraphConfig.Name}], Expected cols: [{errList}] cannot be found!");
}
string pathName = dataWorksheet.Cells[1, pathNameColumnIdx].Text;
// Step 4: add a new worksheet to hold the chart
IWorksheet chartSheet = workbook.Worksheets.Add();
chartSheet.Name = xyGraphConfig.Name;
// Step 5.1: time to build the chart
SpreadsheetGear.Shapes.IShape chartShape = chartSheet.Shapes.AddChart(1, 1, 500, 500);
SpreadsheetGear.Charts.IChart chart = chartShape.Chart;
// working variables
int lastRowIdx = dataWorksheet.UsedRange.RowCount;
IRange xAxisColumn = dataWorksheet.Cells[1, 0, lastRowIdx - 1, 0];
IRange yAxisColumn = null;
ISeries chartSeries = null;
string seriesName = string.Empty;
// Step 5.2: add a chart series for each Y axis column in the config
int xAxisColumnIndex = -1;
int yAxisColumnIndex = -1;
foreach (var series in xyGraphConfig.series)
{
columnNameIndex.TryGetValue(series.XAxisCoumnName, out xAxisColumnIndex);
columnNameIndex.TryGetValue(series.YAxisColumnName, out yAxisColumnIndex);
xAxisColumn = dataWorksheet.Cells[1, xAxisColumnIndex, lastRowIdx - 1, xAxisColumnIndex];
yAxisColumn = dataWorksheet.Cells[1, yAxisColumnIndex, lastRowIdx - 1, yAxisColumnIndex];
chartSeries = chart.SeriesCollection.Add();
chartSeries.XValues = $"={xAxisColumn.ToString()}"; // "Sheet1!$A2:$A200";
chartSeries.Values = yAxisColumn.ToString(); //"Sheet1!$H2:$H200";
chartSeries.ChartType = ChartType.XYScatter;
chartSeries.Name = series.Name;
}
// Step 5.3: format the chart title
chart.HasTitle = true;
StringBuilder chartTitle = new StringBuilder();
chartTitle.AppendLine($"{xyGraphConfig.Name} | Path: [{pathName}]");
// optional add follower gains only if available
if (pidGainsColumnIdx >= 0)
{
chartTitle.AppendLine($"PID Gains: {GetPIDGains(dataWorksheet, pidGainsColumnIdx, controlModeColumnIdx)}");
}
// optional add follower gains only if available
if (followerGainsColumnIdx >= 0)
{
chartTitle.AppendLine($"Follower Gains: {dataWorksheet.Cells[1, followerGainsColumnIdx].Text}");
}
if (xyGraphConfig.CalcFinalErrorDelta != null)
{
(decimal posErr, decimal negErr) = CalcAreaDelta(dataWorksheet, elapsedDeltaColumnIdx, targetColumnIdx, actualColumnIdx, xyGraphConfig.Name);
chartTitle.AppendLine($"Error Area (tot): {posErr:N0} | {negErr:N0}");
}
chart.ChartTitle.Text = chartTitle.ToString();
chart.ChartTitle.Font.Size = 12;
// Step 5.4: format the chart legend
chart.Legend.Position = SpreadsheetGear.Charts.LegendPosition.Bottom;
chart.Legend.Font.Bold = true;
// Step 5.5: format X & Y Axes
IAxis xAxis = chart.Axes[AxisType.Category];
xAxis.HasMinorGridlines = true;
xAxis.HasTitle = true;
if (chart.ChartType == ChartType.Line)
{
// this option not valid on xy graphs
xAxis.TickMarkSpacing = 100; // 10Msec per step * 100 = gidline every second
}
IAxisTitle xAxisTitle = xAxis.AxisTitle;
xAxisTitle.Text = xyGraphConfig.XAxisTitle;
IAxis yAxis = chart.Axes[AxisType.Value, AxisGroup.Primary];
yAxis.HasTitle = true;
yAxis.TickLabels.NumberFormat = "General";
yAxis.ReversePlotOrder = xyGraphConfig.IsYAxisValuesInReverseOrder;
IAxisTitle yAxisTitle = yAxis.AxisTitle;
yAxisTitle.Text = xyGraphConfig.YAxisTitle;
}
public SpreadsheetGear.IWorkbook ExportExcel_One(string title, string subtitle, DataTable allitem, string sheetname)
{
//创建新的workbook
SpreadsheetGear.IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = sheetname;
IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
IShapes shapes = worksheet.Shapes;
while (shapes.Count != 0)
{
shapes[0].Delete();
}
int itemcou = allitem.Columns.Count;
string site = "A1:" + (char)((64 + itemcou)) + "1";
string site1 = null;
if (!string.IsNullOrEmpty(subtitle))
{
site1 = "A2:" + (char)((64 + itemcou)) + "2";
}
else
{
site1 = "A1:" + (char)((64 + itemcou)) + "1";
}
//数据处理
if (!string.IsNullOrEmpty(title))
{
IRange cell = worksheet.Cells[site];
cell.Merge();
cell.HorizontalAlignment = HAlign.Center;
cell.VerticalAlignment = VAlign.Center;
cell.Value = title;
}
if (!string.IsNullOrEmpty(subtitle))
{
IRange cell1 = worksheet.Cells[site1];
cell1.Merge();
cell1.HorizontalAlignment = HAlign.Center;
cell1.VerticalAlignment = VAlign.Center;
cell1.Value = subtitle;
}
if (!string.IsNullOrEmpty(title) && !string.IsNullOrEmpty(subtitle))
{
IRange cell2 = worksheet.Cells["A3"];
cell2.CopyFromDataTable(allitem, SetDataFlags.None);
}
else if (!string.IsNullOrEmpty(title) || !string.IsNullOrEmpty(subtitle))
{
IRange cell2 = worksheet.Cells["A2"];
cell2.CopyFromDataTable(allitem, SetDataFlags.None);
}
else
{
IRange cell2 = worksheet.Cells["A1"];
cell2.CopyFromDataTable(allitem, SetDataFlags.None);
}
worksheet.UsedRange.Columns.AutoFit();
return(workbook);
}
public SpreadsheetGear.IWorkbook BuildSheet(List <SheetModel> sheets)
{
//创建新的workbook
SpreadsheetGear.IWorkbook workbook = Factory.GetWorkbook();
SpreadsheetGear.IWorksheets worksheets = workbook.Worksheets;
for (int i = 0; i < sheets.Count; i++)
{
int high = 1;
if (i > 0)
{
worksheets.Add();
}
IWorksheet worksheet = worksheets[i];
worksheet.Name = sheets[i].SheetName == null ? "sheet" + (i + 1) : sheets[i].SheetName;
IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
IShapes shapes = worksheet.Shapes;
List <DataModel> dataList = sheets[i].DataModels;
for (int j = 0; j < dataList.Count; j++)
{
if (dataList[j].datatype.ToString() != "DataTable")
{
high += 16;
string allsite = "A" + high;
IRange cell = worksheet.Cells[allsite];
cell.CopyFromDataTable(dataList[j].Dataseries, SetDataFlags.None);
worksheet.UsedRange.Columns.AutoFit();
double top = high - 16.5;
double bottom = high - 1.5;
double chartleft = windowInfo.ColumnToPoints(0.15);
double charttop = windowInfo.RowToPoints(top);
double chartright = windowInfo.ColumnToPoints(7.85);
double chartbottom = windowInfo.RowToPoints(bottom);
SpreadsheetGear.Charts.IChart chart = shapes.AddChart(chartleft, charttop, chartright - chartleft, chartbottom - charttop).Chart;
string site = ((char)(65 + dataList[j].StartPoint.x)).ToString() + (high + dataList[j].StartPoint.y) + ":" + ((char)(64 + dataList[j].ColumnCount + dataList[j].StartPoint.x)).ToString() + (high + dataList[j].StartPoint.y + dataList[j].RowCount);
IRange source = worksheet.Cells[site];
chart.SetSourceData(source, RowCol.Columns);
switch (dataList[j].datatype.ToString())
{
case "Column":
chart.ChartType = ChartType.ColumnClustered;
break;
case "Line":
chart.ChartType = ChartType.Line;
break;
case "Radar":
chart.ChartType = ChartType.Radar;
break;
case "Bar":
chart.ChartType = ChartType.BarClustered;
break;
case "BarStacked":
chart.ChartType = ChartType.BarStacked;
break;
case "Pie":
chart.ChartType = ChartType.Pie;
ISeries seriesTotal = chart.SeriesCollection[0];
seriesTotal.HasDataLabels = true;
seriesTotal.DataLabels.ShowPercentage = true;
seriesTotal.DataLabels.ShowValue = false;
break;
}
high += dataList[j].Dataseries.Rows.Count + 1 + sheets[i].space;
}
else
{
string allsite = "A" + high;
IRange cell = worksheet.Cells[allsite];
//cell.CopyFromDataTable(dataList[j].Dataseries, SetDataFlags.None);
cell.CopyFromDataTable(dataList[j].Dataseries, SetDataFlags.AllText);
worksheet.UsedRange.Columns.AutoFit();
high += dataList[j].Dataseries.Rows.Count + 1 + sheets[i].space;
}
}
}
return(workbook);
}
public static void SaveDataTableToFile(DataTable dt, string filename, string format, bool isNDARdata)
{
//
// TODO: Add constructor logic here
//
// Create a new workbook.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
int counter = 0;
counter++;
if (counter > 1)
{
workbook.Worksheets.Add();
}
IWorksheet worksheet = workbook.Worksheets["Sheet" + counter.ToString()];
IRange cells = worksheet.Cells;
// Set the worksheet name.
if (dt.TableName.Length > 31)
{
worksheet.Name = dt.TableName.Substring(0, 31);
}
else if (dt.TableName != "")
{
worksheet.Name = dt.TableName;
}
else
{
worksheet.Name = "Sheet1";
}
// Get the top left cell for the DataTable.
SpreadsheetGear.IRange range = worksheet.Cells["A1"];
// Copy the DataTable to the worksheet range.
range.CopyFromDataTable(dt, SpreadsheetGear.Data.SetDataFlags.None);
cells["1:1"].Font.Bold = true;
string user = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString().Replace(@"AUTISM\", "");
if (format == "Excel")
{
string serverfilename = HttpContext.Current.Server.MapPath(@"~/App_Data/DataDump/" + filename + ".xlsx");
workbook.SaveAs(serverfilename, FileFormat.OpenXMLWorkbook);
// Stream the Excel spreadsheet to the client in a format
// compatible with Excel 97/2000/XP/2003/2007/2010.
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".csv");
workbook.SaveToStream(HttpContext.Current.Response.OutputStream, SpreadsheetGear.FileFormat.OpenXMLWorkbook);
HttpContext.Current.Response.End();
}
else if (format == "CSV" | format == "csv")
{
string serverfilename = HttpContext.Current.Server.MapPath(@"~/App_Data/DataDump/" + filename + ".csv");
if (isNDARdata)
{
//ADD The ShortName in the first row if this is an NDAR data file
// Use SaveToMemory(...) to get a byte array copy of the range data in the CSV format.
string dataRangeStr = Encoding.ASCII.GetString(workbook.SaveToMemory(FileFormat.CSV));
string datafileheader = dt.TableName.Substring(0, (dt.TableName.Length - 2)) + "," + dt.TableName.Substring(dt.TableName.Length - 1, 1);
// Prepend "header" text of the NDAR shortName and a linefeed to the rest of the CSV file.
string csvString = datafileheader + "\r\n" + dataRangeStr;
// Send to output stream
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "text/csv";
bool hasHeader = HttpContext.Current.Response.HeadersWritten;
Debug.WriteLine(String.Format("HttpContext.Current.Response.HeadersWritten = {0}", hasHeader.ToString()));
if (!hasHeader)
{
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".csv");
}
HttpContext.Current.Response.Write(csvString);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.SuppressContent = true;
HttpContext.Current.ApplicationInstance.CompleteRequest();
//HttpContext.Current.Response.End();
}
else
{
workbook.SaveAs(serverfilename, FileFormat.CSV);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".csv");
workbook.SaveToStream(HttpContext.Current.Response.OutputStream, SpreadsheetGear.FileFormat.CSV);
HttpContext.Current.ApplicationInstance.CompleteRequest();
//HttpContext.Current.Response.End();
}
}
else if (format == "IBISVine")
{
//string filepath = @"\\autism-fs03\shared files\R drive\IBIS-2\Vineland_Uploads\";
//string serverfilename = filepath + filename ;
string serverfilename = filename;
//workbook.SaveAs(serverfilename, FileFormat.UnicodeText);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".csv");
workbook.SaveToStream(HttpContext.Current.Response.OutputStream, SpreadsheetGear.FileFormat.CSV);
HttpContext.Current.Response.End();
}
}
public static string WriteDataTableAsCSV(DataTable dt, string filename, string format, bool isNDARdata)
{
// Create a new workbook.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
int counter = 0;
counter++;
if (counter > 1)
{
workbook.Worksheets.Add();
}
IWorksheet worksheet = workbook.Worksheets["Sheet" + counter.ToString()];
IRange cells = worksheet.Cells;
// Set the worksheet name.
if (dt.TableName.Length > 31)
{
worksheet.Name = dt.TableName.Substring(0, 31);
}
else if (dt.TableName != "")
{
worksheet.Name = dt.TableName;
}
else
{
worksheet.Name = "Sheet1";
}
// Get the top left cell for the DataTable.
SpreadsheetGear.IRange range = worksheet.Cells["A1"];
// Copy the DataTable to the worksheet range.
range.CopyFromDataTable(dt, SpreadsheetGear.Data.SetDataFlags.None);
cells["1:1"].Font.Bold = true;
string user = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString().Replace(@"AUTISM\", "");
string csvString = "";
string serverfilename = HttpContext.Current.Server.MapPath(@"~/App_Data/DataDump/" + filename + ".csv");
if (isNDARdata)
{
//ADD The ShortName in the first row if this is an NDAR data file
// Use SaveToMemory(...) to get a byte array copy of the range data in the CSV format.
string dataRangeStr = Encoding.ASCII.GetString(workbook.SaveToMemory(FileFormat.CSV));
string datafileheader = dt.TableName.Substring(0, (dt.TableName.Length - 2)) + "," + dt.TableName.Substring(dt.TableName.Length - 1, 1);
// Prepend "header" text of the NDAR shortName and a linefeed to the rest of the CSV file.
csvString = datafileheader + "\r\n" + dataRangeStr;
}
else
{
csvString = Encoding.ASCII.GetString(workbook.SaveToMemory(FileFormat.CSV));
}
// Write the text to a new file
try
{
File.WriteAllText(serverfilename, csvString);
return(serverfilename);
}
catch (Exception ex)
{
return(String.Format("ERROR_{0}", filename));
}
}
private IWorksheet InitializationSpreedsheetGearWorksheet(string worksheetName) // Create needed worksheet
{
SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
SpreadsheetGear.IWorkbook spreadworkbook = workbookSet.Workbooks.OpenFromMemory(workbook.WorkbookFile);
return(spreadworkbook.Worksheets[worksheetName]);
}
public static void SaveDataSetToExcel(DataSet dset, string filename, bool returnFileToClient, string filetype)
{
// Create a new workbook.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
workbook.WorkbookSet.Experimental = "OleDbOpenXmlWorkaround";
int counter = 0;
foreach (DataTable dt in dset.Tables)
{
ChangeDatesToString(dt);
counter++;
if (counter > 1)
{
workbook.Worksheets.Add();
}
IWorksheet worksheet = workbook.Worksheets["Sheet" + counter.ToString()];
IRange cells = worksheet.Cells;
// Set the worksheet name.
if (dt.TableName.Length > 31)
{
worksheet.Name = dt.TableName.Substring(0, 31);
}
else
{
worksheet.Name = dt.TableName;
}
// Get the top left cell for the DataTable.
SpreadsheetGear.IRange range = worksheet.Cells["A1"];
// Copy the DataTable to the worksheet range.
range.CopyFromDataTable(dt, SpreadsheetGear.Data.SetDataFlags.None);
cells["1:1"].Font.Bold = true;
worksheet.UsedRange.EntireColumn.AutoFit();
}
string user = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString().Replace(@"AUTISM\", "");
string serverfilename = HttpContext.Current.Server.MapPath(@"~/App_Data/DataDownloads/" + filename + "." + filetype);
if (filetype == "xlsx")
{
#region Add the fileinfo sheet
workbook.Worksheets.Add();
int idx = workbook.Worksheets.Count - 1;
IWorksheet infows = workbook.Worksheets[idx];
IRange infocells = infows.Cells;
infows.Name = "fileinfo";
infocells["A1"].Formula = "Attribute";
infocells["A2"].Formula = "FileName:";
infocells["A3"].Formula = "Created:";
infocells["A4"].Formula = "Created By:";
infocells["A5"].Formula = "Source page:";
infocells["B1"].Formula = "Value";
infocells["B2"].Formula = filename;
infocells["B3"].Formula = " " + System.DateTime.Now.ToString();
infocells["B4"].Formula = user;
infocells["B5"].Formula = System.IO.Path.GetFileName(HttpContext.Current.Request.Url.ToString());;
if (filename.StartsWith("Proj_"))
{
infocells["A6"].Formula = "Data Project #:";
infocells["B6"].Formula = filename.Substring(4, 4).Replace("_", "");
}
#endregion
workbook.SaveAs(serverfilename, FileFormat.OpenXMLWorkbook);
if (returnFileToClient)
{
// Stream the Excel spreadsheet to the client in a format
// compatible with Excel 97/2000/XP/2003/2007/2010.
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + "." + filetype);
workbook.SaveToStream(HttpContext.Current.Response.OutputStream, SpreadsheetGear.FileFormat.OpenXMLWorkbook);
HttpContext.Current.Response.End();
}
}
else if (filetype == "csv")
{
workbook.SaveAs(serverfilename, FileFormat.CSV);
if (returnFileToClient)
{
// Stream the Excel spreadsheet to the client in a format
// compatible with Excel 97/2000/XP/2003/2007/2010.
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "application/text";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + "." + filetype);
workbook.SaveToStream(HttpContext.Current.Response.OutputStream, SpreadsheetGear.FileFormat.CSV);
HttpContext.Current.Response.End();
}
}
//string serverfilename2 = HttpContext.Current.Server.MapPath(@"~/App_Data/DataDownloads/" + filename + ".xls");
//workbook.SaveAs(serverfilename2, FileFormat.Excel8);
}
private static void BuildBarGraph(IWorksheet dataWorksheet, BarGraphBE barGraphConfig, Dictionary <string, int> columnNameIndex, string pathNameColumnName)
{
SpreadsheetGear.IWorkbook workbook = dataWorksheet.Workbook;
int columnIdx = -1;
int xAxisTargetColumnIdx = -1;
string xAxisColumnName = barGraphConfig.XAxis.FromColumnName;
List <string> missingColumnNames = new List <string>();
// step 1: find the column we want to target for the XAxis
if (!columnNameIndex.TryGetValue(xAxisColumnName, out xAxisTargetColumnIdx))
{
missingColumnNames.Add(xAxisColumnName);
}
// step 2.1: find the columns we want to target for the YAxis
Dictionary <int, string> yAxisTargetColIdxs = new Dictionary <int, string>();
foreach (string yAxisColumnName in barGraphConfig.YAxis.FromColumnNames)
{
if (columnNameIndex.TryGetValue(yAxisColumnName, out columnIdx))
{
yAxisTargetColIdxs.Add(columnIdx, yAxisColumnName);
}
else
{
missingColumnNames.Add(yAxisColumnName);
}
}
// step 3: find the columns we want to reference for the Gains
string pidGainsColumnName = barGraphConfig.Gains?.PIDGains;
string followerGainsColumnName = barGraphConfig.Gains?.FollowerGains;
string controlModeColumnName = barGraphConfig.Gains?.ControlMode;
int pidGainsColumnIdx = -1;
int followerGainsColumnIdx = -1;
int controlModeColumnIdx = -1;
int elapsedDeltaColumnIdx = -1;
int targetColumnIdx = -1;
int actualColumnIdx = -1;
int pathNameColumnIdx = -1;
if (!string.IsNullOrEmpty(pidGainsColumnName))
{
if (!columnNameIndex.TryGetValue(pidGainsColumnName, out pidGainsColumnIdx))
{
//missingColumnNames.Add(pidGainsColumnName);
}
}
if (!string.IsNullOrEmpty(followerGainsColumnName))
{
if (!columnNameIndex.TryGetValue(followerGainsColumnName, out followerGainsColumnIdx))
{
missingColumnNames.Add(followerGainsColumnName);
}
}
if (!string.IsNullOrEmpty(controlModeColumnName))
{
if (!columnNameIndex.TryGetValue(controlModeColumnName, out controlModeColumnIdx))
{
//missingColumnNames.Add(controlModeColumnName);
}
}
if (!string.IsNullOrEmpty(barGraphConfig.XAxis.FromColumnName))
{
if (!columnNameIndex.TryGetValue(barGraphConfig.XAxis.FromColumnName, out elapsedDeltaColumnIdx))
{
missingColumnNames.Add(barGraphConfig.XAxis.FromColumnName);
}
}
if (!string.IsNullOrEmpty(pathNameColumnName))
{
if (!columnNameIndex.TryGetValue(pathNameColumnName, out pathNameColumnIdx))
{
missingColumnNames.Add(pathNameColumnName);
}
}
//
// stop if any were missing
if (missingColumnNames.Count > 0)
{
string errList = String.Join(",", missingColumnNames);
throw new ApplicationException($"... Error building graph: [{barGraphConfig.Name}], Expected cols: [{errList}] cannot be found!");
}
// Step 4: add a new worksheet to hold the chart
IWorksheet chartSheet = workbook.Worksheets.Add();
chartSheet.Name = barGraphConfig.Name;
// Step 5.1: time to build the chart
SpreadsheetGear.Shapes.IShape chartShape = chartSheet.Shapes.AddChart(1, 1, 500, 500);
SpreadsheetGear.Charts.IChart chart = chartShape.Chart;
// working variables
int lastRowIdx = dataWorksheet.UsedRange.RowCount;
IRange xAxisColumn = dataWorksheet.Cells[1, 0, lastRowIdx - 1, 0];
IRange yAxisColumn = null;
ISeries chartSeries = null;
string seriesName = string.Empty;
// Step 5.2: add a chart series for each Y axis column in the config
foreach (var kvp in yAxisTargetColIdxs)
{
seriesName = dataWorksheet.Cells[0, kvp.Key].Text;
yAxisColumn = dataWorksheet.Cells[1, kvp.Key, lastRowIdx - 1, kvp.Key];
chartSeries = chart.SeriesCollection.Add();
chartSeries.XValues = $"={xAxisColumn.ToString()}"; // "Sheet1!$A2:$A200";
chartSeries.Values = yAxisColumn.ToString(); //"Sheet1!$H2:$H200";
switch (barGraphConfig.ChartTypeOverride)
{
case @"StackedBar":
chartSeries.ChartType = ChartType.ColumnStacked;
break;
default:
chartSeries.ChartType = ChartType.ColumnClustered;
break;
}
chartSeries.Name = seriesName;
}
// Step 5.3: format the chart title
chart.HasTitle = true;
StringBuilder chartTitle = new StringBuilder();
string pathName = dataWorksheet.Cells[1, pathNameColumnIdx].Text;
chartTitle.AppendLine($"{barGraphConfig.Name} | Path: [{pathName}]");
// optional add follower gains only if available
if (pidGainsColumnIdx >= 0)
{
chartTitle.AppendLine($"PID Gains: {GetPIDGains(dataWorksheet, pidGainsColumnIdx, controlModeColumnIdx)}");
}
// optional add follower gains only if available
if (followerGainsColumnIdx >= 0)
{
chartTitle.AppendLine($"Follower Gains: {dataWorksheet.Cells[1, followerGainsColumnIdx].Text}");
}
chart.ChartTitle.Text = chartTitle.ToString();
chart.ChartTitle.Font.Size = 12;
// Step 5.4: format the chart legend
chart.Legend.Position = SpreadsheetGear.Charts.LegendPosition.Bottom;
chart.Legend.Font.Bold = true;
// Step 5.5: format X & Y Axes
IAxis xAxis = chart.Axes[AxisType.Category];
xAxis.HasMinorGridlines = true;
xAxis.HasTitle = true;
if (chart.ChartType == ChartType.Line)
{
// this option not valid on xy graphs
xAxis.TickMarkSpacing = 100; // 10Msec per step * 100 = gidline every second
}
IAxisTitle xAxisTitle = xAxis.AxisTitle;
xAxisTitle.Text = barGraphConfig.XAxis.AxisTitle;
IAxis yAxis = chart.Axes[AxisType.Value, AxisGroup.Primary];
yAxis.HasTitle = true;
yAxis.TickLabels.NumberFormat = "General";
yAxis.ReversePlotOrder = barGraphConfig.YAxis.IsYAxisValuesInReverseOrder;
if (barGraphConfig.YAxis.MajorUnitOverride.HasValue)
{
yAxis.MajorUnit = (double)barGraphConfig.YAxis.MajorUnitOverride.Value;
}
IAxisTitle yAxisTitle = yAxis.AxisTitle;
yAxisTitle.Text = barGraphConfig.YAxis.AxisTitle;
}
public static void CreateWorkBook(string filename)
{
SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.Add();
workbook.SaveAs(filename, FileFormat.Excel8);
}
public SpreadsheetGear.IWorkbook DrawChart(List <ChartModel> columns, List <DataTable> allitem, string sheetname,
bool OpenSheet = false, bool cellText = false, int cellInt = 0)
{
//创建新的workbook
SpreadsheetGear.IWorkbook workbook = Factory.GetWorkbook();
SpreadsheetGear.IWorksheets worksheets = workbook.Worksheets;
IWorksheet worksheet = worksheets[0];
worksheet.Name = sheetname + 1;
IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
IShapes shapes = worksheet.Shapes;
int high = 1;
int Rowhigh = 17;
double top = 0.5;
double bottom = 15.5;
for (int a = 0; a < columns.Count; a++)
{
int row = 0;
int column = 0;
int piecol = 0;
string site = null;
if (columns[a].series.Count > 0)
{
row = columns[a].xAxis.Count;
column = columns[a].series.Count;
}
if (columns[a].pieseries.Count > 0)
{
piecol = columns[a].pieseries.Count;
}
//+((17 + row) * j);
string[,] arrayChart = null;
if ((columns[a].charttype.ToString()) == "Pie")
{
site = "A" + Rowhigh + ":B" + (piecol + Rowhigh);
arrayChart = new string[piecol + 1, 2];
for (int j = 0; j < (piecol + 1); j++)
{
if (j == 0)
{
if (!string.IsNullOrEmpty(columns[a].title))
{
arrayChart[0, 0] = "";
arrayChart[0, 1] = columns[a].title;
}
else
{
arrayChart[0, 0] = "";
arrayChart[0, 1] = "";
}
}
else
{
for (int i = 0; i < 2; i++)
{
if (i == 0)
{
arrayChart[j, i] = columns[a].pieseries[j - 1].name;
}
else
{
arrayChart[j, i] = columns[a].pieseries[j - 1].y.ToString();
}
}
}
}
}
else if ((columns[a].charttype.ToString()) == "Radar")
{
if (row < 3)
{
row = 3;
}
string aaa = column / 26 == 0 ? ((char)((65 + column))).ToString() : "" + (char)((64 + column / 26)) + (char)((65 + column % 26));
site = "A" + Rowhigh + ":" + aaa + (row + Rowhigh);
if (column > 25)
{
arrayChart = new string[(row + 1), (column + 1)];
}
for (int j = 0; j < row + 1; j++)
{
if (j == 0)
{
for (int i = 0; i < column + 1; i++)
{
if (i == 0)
{
arrayChart[j, i] = "";
}
else
{
arrayChart[j, i] = columns[a].series[i - 1].name;
}
}
}
else
{
for (int k = 0; k < column + 1; k++)
{
if (k == 0)
{
if (j > columns[a].xAxis.Count)
{
arrayChart[j, k] = "";
}
else
{
arrayChart[j, k] = columns[a].xAxis[j - 1];
}
}
else
{
if (j > columns[a].xAxis.Count)
{
arrayChart[j, k] = "";
}
else
{
List <double> adata = columns[a].series[k - 1].data;
arrayChart[j, k] = adata[j - 1].ToString();
}
}
}
}
}
}
else
{
string aaa = column / 26 == 0 ? ((char)((65 + column))).ToString() : "" + (char)((64 + column / 26)) + (char)((65 + column % 26));
site = "A" + Rowhigh + ":" + aaa + (row + Rowhigh);
arrayChart = new string[(row + 1), (column + 1)];
for (int j = 0; j < row + 1; j++)
{
if (j == 0)
{
for (int i = 0; i < column + 1; i++)
{
if (i == 0)
{
arrayChart[j, i] = "";
}
else
{
arrayChart[j, i] = columns[a].series[i - 1].name;
}
}
}
else
{
for (int k = 0; k < column + 1; k++)
{
if (k == 0)
{
arrayChart[j, k] = columns[a].xAxis[j - 1];
}
else
{
List <double> adata = columns[a].series[k - 1].data;
arrayChart[j, k] = adata[j - 1].ToString();
}
}
}
}
}
double chartleft = windowInfo.ColumnToPoints(0.15);
double charttop = windowInfo.RowToPoints(top);
double chartright = windowInfo.ColumnToPoints(7.85);
double chartbottom = windowInfo.RowToPoints(bottom);
SpreadsheetGear.Charts.IChart chart =
shapes.AddChart(chartleft, charttop, chartright - chartleft, chartbottom - charttop).Chart;
IRange source = worksheet.Cells[site];
source.Value = arrayChart;
chart.SetSourceData(source, RowCol.Columns);
if ((columns[a].charttype.ToString()) == "Column")
{
chart.ChartType = ChartType.ColumnClustered;
}
else if ((columns[a].charttype.ToString()) == "Line")
{
chart.ChartType = ChartType.Line;
}
else if ((columns[a].charttype.ToString()) == "Pie")
{
chart.ChartType = ChartType.Pie;
ISeries seriesTotal = chart.SeriesCollection[0];
seriesTotal.HasDataLabels = true;
seriesTotal.DataLabels.ShowPercentage = true;
seriesTotal.DataLabels.ShowValue = false;
}
else if ((columns[a].charttype.ToString()) == "Radar")
{
chart.ChartType = ChartType.Radar;
}
else if ((columns[a].charttype.ToString()) == "Bar")
{
chart.ChartType = ChartType.BarClustered;
}
else if ((columns[a].charttype.ToString()) == "BarStacked")
{
chart.ChartType = ChartType.BarStacked;
}
if (!string.IsNullOrEmpty(columns[a].title))
{
chart.HasTitle = true;
chart.ChartTitle.Text = columns[a].title;
chart.ChartTitle.Font.Size = 12;
}
if ((columns[a].charttype.ToString()) == "Pie")
{
Rowhigh += 17 + piecol;
top += 17 + piecol;
bottom += 17 + piecol;
high += 17 + piecol;
}
else
{
Rowhigh += 17 + row;
top += 17 + row;
bottom += 17 + row;
high += 17 + row;
}
}
//数据列表处理
if (allitem.Count > 0)
{
if (OpenSheet)
{
for (int c = 0; c < allitem.Count; c++)
{
if (columns.Count > 0)
{
worksheets.Add();
worksheet = worksheets[worksheets.Count - 1];
worksheet.Name = sheetname + worksheets.Count;
}
else
{
if (c > 0)
{
worksheets.Add();
worksheet = workbook.Worksheets[worksheets.Count - 1];
worksheet.Name = sheetname + worksheets.Count;
}
}
string allsite = "A1";
IRange cell = worksheet.Cells[allsite];
//cell.CopyFromDataTable(allitem[c], SetDataFlags.None);
cell.CopyFromDataTable(allitem[c], SetDataFlags.AllText);
worksheet.UsedRange.Columns.AutoFit();
//high += allitem[c].Rows.Count;
}
}
else
{
if (columns.Count > 0)
{
high += 2;
}
for (int b = 0; b < allitem.Count; b++)
{
if (b > 0)
{
high += 1;
}
string allsite = "A" + high;
IRange cell = worksheet.Cells[allsite];
//cell.CopyFromDataTable(allitem[b], SetDataFlags.None);
cell.CopyFromDataTable(allitem[b], SetDataFlags.AllText);
if (cellText)
{
for (int i = 1; i <= allitem[0].Rows.Count; i++)
{
// cell[i, cellInt].NumberFormat = "@";
cell[i, cellInt].NumberFormat = "@";
}
}
worksheet.UsedRange.Columns.AutoFit();
high += allitem[b].Rows.Count;
}
}
}
return(workbook);
}
public static string ProcessLogFile(string logFilePathName, string graphSetName, GraphConfigsBE config)
{
// Activate SpreadsheetGear
SpreadsheetGear.Factory.SetSignedLicense("SpreadsheetGear.License, Type=Trial, Product=BND, Expires=2019-07-27, Company=Tom Bruns, [email protected], Signature=orH+RFO9hRUB8SJXBSWQZJuXP9OfSkV9fLcU9suehfgA#dgunwBK9VssTgnfowKGWaqMNfVgwVetxEWbayzGM1uIA#K");
// Create a new empty workbook in a new workbook set.
SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
// import the csv file
SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.Open(logFilePathName);
// get a reference to the active (only) worksheet
SpreadsheetGear.IWorksheet dataWorksheet = workbook.ActiveWorksheet;
dataWorksheet.Name = System.IO.Path.GetFileNameWithoutExtension(logFilePathName);
// freeze 1st row & 1st column(to make scrolling more user friendly)
dataWorksheet.WindowInfo.ScrollColumn = 0;
dataWorksheet.WindowInfo.SplitColumns = 1;
dataWorksheet.WindowInfo.ScrollRow = 0;
dataWorksheet.WindowInfo.SplitRows = 1;
dataWorksheet.WindowInfo.FreezePanes = true;
// build index of column names
var columnNameXref = BuildColumnNameXref(dataWorksheet);
// find the config for the requested Set of Graphs
GraphSetBE graphSet = config.GraphSets.Where(gs => gs.SetName.ToLower() == graphSetName.ToLower()).FirstOrDefault();
if (graphSet == null)
{
List <string> availableGraphSetNames = config.GraphSets.Select(gs => gs.SetName).ToList();
throw new ApplicationException($"Requested GraphSet: [{graphSetName}], Options: [{String.Join(",", availableGraphSetNames)}]");
}
// do any required conversions on the source data (ex Radians to Degrees)
if (graphSet.AngleConversions != null)
{
foreach (AngleConversionBE angleConversion in graphSet.AngleConversions)
{
PerformAngleConversion(dataWorksheet, angleConversion, columnNameXref);
}
// rebuild column name index
columnNameXref = BuildColumnNameXref(dataWorksheet);
}
// resize column widths to fit header text
dataWorksheet.UsedRange.Columns.AutoFit();
// ====================================
// create any new sheets with a subset of the original columns to make analysis easier
// ====================================
foreach (NewSheetBE newSheet in graphSet.NewSheets)
{
BuildNewSheet(dataWorksheet, newSheet, columnNameXref);
}
string pathNameColumnName = graphSet.PathNameColumnName;
// ====================================
// build a new line graph for each one in the selected graphset
// ====================================
foreach (LineGraphBE lineGraph in graphSet.LineGraphs)
{
BuildLineGraph(dataWorksheet, lineGraph, columnNameXref, pathNameColumnName);
}
// ====================================
// build a new XY graph for each one in the selected graphset
// fyi: these were separated because they require slightly different config data structures
// ====================================
foreach (XYGraphBE xyGraph in graphSet.XYGraphs)
{
BuildXYGraph(dataWorksheet, xyGraph, columnNameXref, pathNameColumnName);
}
// ====================================
// build a new bar graph for each one in the selected graphset
// ====================================
foreach (BarGraphBE barGraph in graphSet.BarGraphs)
{
BuildBarGraph(dataWorksheet, barGraph, columnNameXref, pathNameColumnName);
}
// ====================================
// build a new histogram for each one in the selected graphset
// ====================================
foreach (HistogramBE histogram in graphSet.Histograms)
{
BuildHistogram(dataWorksheet, histogram, columnNameXref, pathNameColumnName);
}
// save the workbook
string pathName = GetCellValue <string>(dataWorksheet, graphSet.PathNameColumnName, 1, columnNameXref);
string folderPathName = System.IO.Path.GetDirectoryName(logFilePathName);
string fileName = System.IO.Path.GetFileNameWithoutExtension(logFilePathName);
fileName = (!string.IsNullOrEmpty(pathName)) ? $"{fileName}_{pathName}" : fileName;
fileName = System.IO.Path.ChangeExtension(fileName, @".xlsx");
string xlsFilePathName = System.IO.Path.Combine(folderPathName, fileName);
workbook.SaveAs(xlsFilePathName, FileFormat.OpenXMLWorkbook);
return(xlsFilePathName);
}
public Util_Excel_GridView()
{
workbook = SpreadsheetGear.Factory.GetWorkbook();
worksheet = workbook.Worksheets[counterPage];
cells = worksheet.Cells;
}
/// <summary>
/// To create object of DIExcel .
/// </summary>
/// <param name="excelFileName">Excel file name with path</param>
public DIExcel(string excelFileName)
{
this.ExcelFileName = excelFileName;
this.Workbook = ExcelHelper.OpenExcelFile(this.ExcelFileName);
}
/// <summary>
/// To close opened excel file.
/// </summary>
public void Close()
{
if (this.Workbook != null)
{
this.Workbook.Close();
this.Workbook = null;
}
}
public DSSExcel(string filename)
{
workbook = workbookSet.Workbooks.Open(filename);
ChangeActiveSheet(0);
}
private IWorkbook GetWorkbookFile(byte[] p) // p - excel workbook bibary file
{
SpreadsheetGear.IWorkbookSet workbookset = SpreadsheetGear.Factory.GetWorkbookSet();
SpreadsheetGear.IWorkbook workbook = workbookset.Workbooks.OpenFromMemory(p); // workbook is required excel file, not binary
return(workbook);
}