Ejemplo n.º 1
0
 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;
 }
Ejemplo n.º 2
0
 public PreparedImage(Workbook workbook, string worksheetName)
 {
     this.workbook   = workbook;
     spreadworksheet = InitializationSpreedsheetGearWorksheet(worksheetName);
     cells           = spreadworksheet.Cells;
     ApplyDeaultTitle(worksheetName);
 }
Ejemplo n.º 3
0
 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);
 }
Ejemplo n.º 4
0
 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;
 }
Ejemplo n.º 5
0
 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);
 }
Ejemplo n.º 6
0
 private void CreaHoja(string nombreHoja)
 {
     worksheet      = workbook.Worksheets.Add();
     cells          = worksheet.Cells;
     worksheet.Name = nombreHoja;
     rowIndex       = 1;
     counterPage++;
 }
Ejemplo n.º 7
0
 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];
     }
 }
Ejemplo n.º 8
0
        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++;
            }
        }
Ejemplo n.º 9
0
        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++;
            }
        }
Ejemplo n.º 10
0
        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
Ejemplo n.º 11
0
        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
        }
Ejemplo n.º 12
0
    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));
        }
    }
Ejemplo n.º 13
0
    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();
        }
    }
Ejemplo n.º 14
0
    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);
    }
Ejemplo n.º 15
0
 public Util_Excel_GridView()
 {
     workbook  = SpreadsheetGear.Factory.GetWorkbook();
     worksheet = workbook.Worksheets[counterPage];
     cells     = worksheet.Cells;
 }
Ejemplo n.º 16
0
 private void ObtenRango(DataTable ds)
 {
     RangeCells = cells["A" + rowIndex + ":" + headerColumns[ds.Columns.Count - 1] + rowIndex];
 }