public void ExportarExcel(DataSet ds, string ruta, string NombreArchivo)
{
int Countertables = 0;
foreach (System.Data.DataTable itemTable in ds.Tables)
{
Countertables++;
if (Countertables > 1)
{
CreaHoja(itemTable.TableName.Contains("Table") ? "Hoja" + Countertables : itemTable.TableName);
}
//foreach (System.Data.DataRow item in itemTable.Rows)
//{
if (Countertables == 1)
{
worksheet.Name = itemTable.TableName.Contains("Table") ? "Hoja" + Countertables : itemTable.TableName;
}
CreaEncabezadosExcel(itemTable);
CreaCuerpoExcel(itemTable);
//}
}
workbook.SaveAs(ruta + NombreArchivo, SpreadsheetGear.FileFormat.OpenXMLWorkbook);
}
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 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);
}
public static void CreateWorkBook(string filename)
{
SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.Add();
workbook.SaveAs(filename, FileFormat.Excel8);
}
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);
}