Пример #1
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));
        }
    }
Пример #2
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();
        }
    }