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

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

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

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();

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);
            }
          
        }

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();

end of post

try to use OLEDB

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

thanks.

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);