private void CreaEstiloCeldas(ref SpreadsheetGear.IRange RangeCells) { RangeCells.Font.Size = 8; RangeCells.Font.Name = "Arial"; RangeCells.Font.Color = System.Drawing.Color.Black; RangeCells.Interior.Color = System.Drawing.Color.Transparent; }
public PreparedImage(Workbook workbook, string worksheetName) { this.workbook = workbook; spreadworksheet = InitializationSpreedsheetGearWorksheet(worksheetName); cells = spreadworksheet.Cells; ApplyDeaultTitle(worksheetName); }
private void CreaEstiloCeldas(ref SpreadsheetGear.IRange RangeCells, GridView gridView, GridViewRow Row) { RangeCells.Font.Size = 8; RangeCells.Font.Name = "Arial"; RangeCells.Font.Color = ColorRowFont(gridView, Row); RangeCells.Interior.Color = ColorRow(gridView, Row); }
private void CreaEstiloEncabezado(ref SpreadsheetGear.IRange RangeCells) { RangeCells.Font.Size = 8; RangeCells.Font.Name = "Arial"; RangeCells.Font.Bold = true; RangeCells.Font.Color = System.Drawing.Color.White; RangeCells.Interior.Color = System.Drawing.Color.Blue; }
private void CreaEstiloEncabezado(ref SpreadsheetGear.IRange RangeCells, GridView gridView) { RangeCells.Font.Size = 8; RangeCells.Font.Name = "Arial"; RangeCells.Font.Bold = true; RangeCells.Font.Color = ColorHeaderFont(gridView); RangeCells.Interior.Color = ColorHeader(gridView); }
private void CreaHoja(string nombreHoja) { worksheet = workbook.Worksheets.Add(); cells = worksheet.Cells; worksheet.Name = nombreHoja; rowIndex = 1; counterPage++; }
private void ObtenRango(GridView gridView) { if (configExcel.ArrDatakeys != null) { RangeCells = cells["A" + rowIndex + ":" + headerColumns[gridView.Columns.Count - 1 - configExcel.LstIndOmitir.Count() + configExcel.ArrDatakeys.Length] + rowIndex]; } else { RangeCells = cells["A" + rowIndex + ":" + headerColumns[gridView.Columns.Count - 1 - configExcel.LstIndOmitir.Count()] + rowIndex]; } }
private void CreaEstiloCeldaSingle(ref SpreadsheetGear.IRange RangeCells, GridView gridView, GridViewRow Row) { int cunterColumns = 0; foreach (IRange celda in RangeCells.Cells) { celda.Font.Size = 8; celda.Font.Name = "Arial"; celda.Font.Color = ColorRowFontSingle(gridView, Row, cunterColumns); celda.Interior.Color = ColorRowSingle(gridView, Row, cunterColumns); cunterColumns++; } }
private void CorteHoja(string nombreHoja) { rowIndex++; if (rowIndex >= configExcel.CorteHoja) { worksheet = workbook.Worksheets.Add(); cells = worksheet.Cells; worksheet.Name = nombreHoja + headerColumns[counterPage]; rowIndex = 1; counterPage++; } else { rowIndex++; } }
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
private string RowGuess() //Get HeaderRow (Works with template files, because he find last number row) { string answer = "1"; string column = "A"; int counter = 0; SpreadsheetGear.IRange cel = worksheet.Cells;// need check if cell after it is empty foreach (IRange cell in worksheet.UsedRange) { CountNotEmptyCells(ref counter, cell); // counter is use check worksheet is worksheet is empty (counter =0) and then delete worksheet from database answer = cell.Address.Remove(0, cell.Address.LastIndexOf('$') + 1); // column = cell.Address.Remove(0, 1); column = column.Remove(column.IndexOf('$'), column.Length - column.IndexOf('$')); if (IsNextCellEmpty(cel, column, answer) && !CellIsNumber(cell) && !IsCellEmpty(cell)) { break; } } if (counter == 0) { DeleteWorksheetFromDatabase(); } return(answer); //return Row number }
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)); } }
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 Util_Excel_GridView() { workbook = SpreadsheetGear.Factory.GetWorkbook(); worksheet = workbook.Worksheets[counterPage]; cells = worksheet.Cells; }
private void ObtenRango(DataTable ds) { RangeCells = cells["A" + rowIndex + ":" + headerColumns[ds.Columns.Count - 1] + rowIndex]; }