Data export from c# to excel

Question

I am trying to export data from datatable to excel but when I am opening the excel I am getting the attached error :

Also below is the code that I am using to export data.

StreamWriter wr = new StreamWriter(@"C:\\Docs_Harshit\\file.xls");

for (int i = 0; i < dtFeeForm.Columns.Count; i++)
{
    wr.Write(dtFeeForm.Columns[i].ToString().ToUpper() + "\t");
}

wr.WriteLine();

for (int i = 0; i < (dtFeeForm.Rows.Count); i++)
{
    for (int j = 0; j < dtFeeForm.Columns.Count; j++)
    {
        if (dtFeeForm.Rows[i][j] != null)
        {
            wr.Write(Convert.ToString(dtFeeForm.Rows[i][j]) + "\t");
        }
        else
        {
            wr.Write("\t");
        }
    }                    
    wr.WriteLine();
}                
wr.Close();

See http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp?rq=1 — Gavin Campbell, Sep 22 '16 at 07:09

score 1 · Answer 1 · answered Sep 22 '16 at 07:11

 private void ExportToExl(bool firstTime)
        {
           //string path = string.Empty;               
            //Delete the file if it exists. 
            if (firstTime && File.Exists(savingFileName))
                File.Delete(savingFileName);

            if (firstTime)
            {
                //This is the first time of creating the excel file and the first sheet.
                // Create a spreadsheet document by supplying the filepath.
                // By default, AutoSave = true, Editable = true, and Type = xlsx.
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
                    Create(savingFileName, SpreadsheetDocumentType.Workbook);

                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                // Add a WorksheetPart to the WorkbookPart.
                var worksheetPart = workbookpart.AddNewPart<WorksheetPart>(); 
                var sheetData = new SheetData();
                worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
                var bold1 = new System.Windows.Documents.Bold();
                DocumentFormat.OpenXml.Spreadsheet.CellFormat cf = new DocumentFormat.OpenXml.Spreadsheet.CellFormat();

                // Add Sheets to the Workbook.
                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets;
                sheets = spreadsheetDocument.WorkbookPart.Workbook.
                    AppendChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());

                // Append a new worksheet and associate it with the workbook.
                var sheet = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                        GetIdOfPart(worksheetPart),
                    SheetId = sheetId,
                    Name = "Sheet" + sheetId
                };
                sheets.Append(sheet);

                //Add Header Row.
                var headerRow = new Row();
                foreach (DataColumn column in ResultsData.Columns)
                {
                    var cell = new Cell { DataType = CellValues.String, CellValue = new CellValue(column.ColumnName) };
                    headerRow.AppendChild(cell);
                }
                sheetData.AppendChild(headerRow);

                foreach (DataRow row in ResultsData.Rows)
                {
                    var newRow = new Row();
                    foreach (DataColumn col in ResultsData.Columns)
                    {
                        var cell = new Cell
                        {
                            DataType = CellValues.String,
                            CellValue = new CellValue(row[col].ToString())
                        };
                        newRow.AppendChild(cell);
                    }
                    sheetData.AppendChild(newRow);
                }               
                workbookpart.Workbook.Save();
                spreadsheetDocument.Close();
            }
    }

Datatable is ResultsData

You can use this code to export in excel or in any format.. .xlxs,.csv,.xlx — Thomas, Sep 22 '16 at 07:11

score 0 · Answer 2 · answered Sep 22 '16 at 07:36

The issue is you are not using any excel component to write excel file. If you can use then try this otherwise the comment on your question will be helpful:

public void CreateExcelFile()
{
    //Get the data from database into datatable
    string cmdQry = "dbo.GET_Report";
    SqlCommand cmd = new SqlCommand(cmdQry);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@bySource", SqlDbType.VarChar).Value = "CLIENT".ToUpper();
    DataTable dtExcel = GetData(cmd);

    //Clears all content output from the buffer stream.  
    Response.ClearContent();
    Response.Clear();
    //Adds HTTP header to the output stream  
    Response.AddHeader("content-disposition", string.Format("attachment; filename=try.xls"));

    // Gets or sets the HTTP MIME type of the output stream  
    Response.ContentType = "application/vnd.ms-excel";
    string space = "";

    foreach (DataColumn dcolumn in dtExcel.Columns)
    {
        Response.Write(space + dcolumn.ColumnName);
        space = "\t";
    }
    Response.Write("\n");
    int countcolumn;
    foreach (DataRow dr in dtExcel.Rows)
    {
        space = "";
        for (countcolumn = 0; countcolumn < dtExcel.Columns.Count; countcolumn++)
        {
            Response.Write(space + dr[countcolumn].ToString());
            space = "\t";
        }
        Response.Write("\n");
    }
    Response.Flush();
    Response.End();
}

private DataTable GetData(SqlCommand cmd)
    {
        SqlConnection con = new SqlConnection(Bussiness.GetConnectionString("Default"));
        cmd.Connection = con;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();

        try
        {
            con.Open();
            da.Fill(dt);
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
            da.Dispose();
            con.Dispose();
        }
    }

I am getting error at the response object which is "The name 'Response' does not exist in the current context". I am using the namespace of System.Web — Harshit, Sep 22 '16 at 07:43
I hope you are developing it in a web application. System.Web should work. Try System.Web.HttpContext.Current.Response as well. — Akshay, Sep 22 '16 at 07:51
I am usding a console appliation so this wont work. Is there any work around for this — Harshit, Sep 22 '16 at 08:46
yes, http://stackoverflow.com/questions/8818404/how-to-export-dataset-to-excel-in-c-sharp-console-application-using-microsoft-of — Akshay, Sep 22 '16 at 09:49

score 0 · Answer 3 · answered Oct 12 '16 at 06:48

0

I Found an easy way for exporting. I simply used CSV file for Data Export and used Encoding UTF32. That helped for data export and the task was done easily.

answered Oct 12 '16 at 06:48

Harshit

17
4

Data export from c# to excel

3 Answers3