C# .NET - Save the excel file instead of open as excel format

Asked By gnanam gnanam on 04-May-09 05:32 AM

i read the data from table then move the data to excel then  then save the file in application folder

ex: select *  from employee

then move to emp.csv fomat

im application:Employeefile/emp.csv

You need save excel sheet data to csv. - Ravenet Rasaiyah replied to gnanam gnanam on 04-May-09 05:38 AM

Hi

Do like this way


Excel.Application app = new Excel.ApplicationClass(); 
Excel.Workbook WB = app.Workbooks.Add(Type.Missing);

Excel.Sheets WS = WB.Worksheets;

Excel.Worksheet CurSheet = (Excel.Worksheet)WS[1];

Excel.Range CurCell = (Excel.Range)CurSheet.Cells[1,1];

CurCell.Value2 = "hi";

WB.SaveAs ("c:/can_primary.xls",Excel.XlFileFormat.xlWorkbookNormal ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlSaveAsAccessMode.xlShared ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing );

WB.SaveAs ("c:/can_primary1.xls",Excel.XlFileFormat.xlCSVWindows ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlSaveAsAccessMode.xlShared ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing );

WB.Close(false,"",true);

thank you

dataset to csv - sri sri replied to gnanam gnanam on 04-May-09 05:47 AM

hi,

try the following code to convert dataset to csv format.

string body = "", record = "";

        foreach (DataRow row in ds.Tables[0].Rows)
        {
            object[] arr = row.ItemArray;

            for (int i = 0; i <= arr.Length - 1; i++)
            {
                if (arr[i].ToString().IndexOf(",") > 0)
                {
                    record = record + arr[i].ToString() + ",";
                }
                else
                {
                    record = record + arr[i].ToString() + ",";
                }
            }
            body = body + record.Substring(0, record.Length - 1) + System.Environment.NewLine;
            record = "";
        }
    string strData = body;


 byte[] data = System.Text.ASCIIEncoding.ASCII.GetBytes(strData);
                Response.Clear();
                Response.AddHeader("Content-Type", "application/Excel");
                Response.AddHeader("Content-Disposition", "attachment;filename=ExportData.csv");
                Response.BinaryWrite(data);
                Response.End();

re - Web Star replied to sri sri on 04-May-09 06:25 AM

use this

public static void ExportToExcel(DataSet dataSet, string outputPath)
        {
        
            // Create the Excel Application object
            try
            {
                Excel.ApplicationClass excelApp = new Excel.ApplicationClass();

                // Create a new Excel Workbook
                Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);

                int sheetIndex = 0;

                // Copy each DataTable
                foreach (System.Data.DataTable dt in dataSet.Tables)
                {

                    // Copy the DataTable to an object array
                    object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];

                    // Copy the column names to the first row of the object array
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        rawData[0, col] = dt.Columns[col].ColumnName;
                    }

                    // Copy the values to the object array
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        for (int row = 0; row < dt.Rows.Count; row++)
                        {
                            rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
                        }
                    }

                    // Calculate the final column letter
                    string finalColLetter = string.Empty;
                    string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
                    int colCharsetLen = colCharset.Length;

                    if (dt.Columns.Count > colCharsetLen)
                    {
                        finalColLetter = colCharset.Substring((dt.Columns.Count - 1) / colCharsetLen - 1, 1);
                    }

                    finalColLetter += colCharset.Substring((dt.Columns.Count - 1) % colCharsetLen, 1);

                    // Create a new Sheet
                    Excel.Worksheet excelSheet = (Excel.Worksheet)excelWorkbook.Sheets.Add(excelWorkbook.Sheets.get_Item(++sheetIndex), Type.Missing, 1, Excel.XlSheetType.xlWorksheet);
                    excelSheet.Name = dt.TableName;

                    // Fast data export to Excel
                    string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);

                    excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
                    // Mark the first row as BOLD
                    ((Excel.Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
                }

                // Save and Close the Workbook
                excelWorkbook.SaveAs(outputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                excelWorkbook.Close(true, Type.Missing, Type.Missing);
                excelWorkbook = null;

                // Release the Application object
                excelApp.Quit();
                excelApp = null;

                // Collect the unreferenced objects
                GC.Collect();
                GC.WaitForPendingFinalizers();
                MessageBox.Show("The Search Result Has Been Stored TO Location " + outputPath, " Resume Parser", MessageBoxButtons.OK, MessageBoxIcon.Information);


            }
            catch (Exception ex)
            {
              
                MessageBox.Show("Error in Excel Operation: " + ex.ToString(), "Resume Parser", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
          
        }

simpler way is to make use of datagrid to export the data into excel format
H K replied to gnanam gnanam on 04-May-09 10:32 PM
Once you have the results in to a table, just bind the table to a temporary datagrid and use the datagrid/gridview  rendercontrol method.. as shown in the code below.

before rendering the html to the response, you need to do the following,

1. Clear the response headers
2. Clear the response content
3. Set the charset of the response to empty
4. Set the MIME type by setting the Reponse.ContentType  property..to    "application/vnd.ms-excel"



 dgReport.DataSource = Tableview;
            dgUserSummary.DataBind();

            Response.ClearHeaders();
            Response.ClearContent();
            Response.Charset = "";

            // Turn off the view state.
            this.EnableViewState = false;
            System.IO.StringWriter stringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlTextWriter = new System.Web.UI.HtmlTextWriter(stringWriter);

            //Get the html for the datagrid, which has the userdetails
            dgReport.RenderControl(htmlTextWriter);

            // Set the content type to Excel

            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment;filename=Report.cvs");
            Response.Write(stringWriter.ToString());
            Response.End();

how to save,open,select particular one record from excel csv file using asp with c#
krupa c replied to gnanam gnanam on 12-May-09 03:21 AM
end of post
c# oledb - lee suko replied to gnanam gnanam on 09-Jun-09 07:17 AM

try to use OLEDB

http://csharp.net-informations.com/excel/csharp-excel-oledb.htm


thanks.


John Glenn replied to gnanam gnanam on 06-Dec-11 03:51 AM
Hello,

since you are writing to CSV file, you can import data from database table to DataTable and then make your own CSV writer, it is much more easier to implement than CSV reader.

On the other hand, if you are looking for easy to use solution that can be easily extended to support other formats like XLS or XLSX, then take a look at this http://www.gemboxsoftware.com/spreadsheet/overview component.

Here is a sample http://www.gemboxsoftware.com/spreadsheet/overview code how to export http://www.gemboxsoftware.com/support/articles/import-export-datatable-xls-xlsx-ods-csv-html-net:

var ef = new ExcelFile();

 

ef.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, 0, 0, true);

 

ef.SaveCsv(dataTable.TableName + ".csv", CsvType.CommaDelimited);