Export dataset items to excel sheet

Question

I am using Followin LOC for Export to and Import from Excel sheet in my asp.net application

 protected void LinkbuttonExportToExcel_Click(object sender, EventArgs e)
{
    UserManager manager = new UserManager();
    DataSet dataSet = manager.GetProductDataToExport();

    string attachment = "attachment; filename=Report.xls";
    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/vnd.ms-excel";
    string tab = "";
    string tab1 = "";
    foreach (DataTable table in dataSet.Tables)
    {
        foreach (DataColumn column in table.Columns)
        {
           Response.Write(tab1 + column.ColumnName);
           tab1 = "\t";
        }
    }
    tab = "\n";
        foreach (DataRow dr in dataSet.Tables[0].Rows)
    {
       for (int i = 0; i < dataSet.Tables[0].Columns.Count; i++)
        {
          Response.Write(tab + dr[i].ToString());
          tab = "\t";
        }
        tab = "\n";
    }
    Response.End();
}

protected void ButtonImportDataFromExcel_Click(object sender, EventArgs e)
{
    FileUpload1.PostedFile.SaveAs(Server.MapPath("~/App_Data/ProductImport.xls"));
    OleDbConnection objXConn = new OleDbConnection();
    objXConn.ConnectionString = ConfigurationManager.ConnectionStrings["sqlXCon"].ConnectionString;
   if (objXConn.State == ConnectionState.Closed)
    {
        objXConn.Open();
    }

        DataTable dt = new DataTable();
        dt = objXConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string excelSheetName = string.Empty; ;
        foreach (DataRow row in dt.Rows)
        {
          excelSheetName = row["TABLE_NAME"].ToString();
        }
        OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" +  excelSheetName + "]", objXConn);
        OleDbDataReader rd = cmd.ExecuteReader();
        UserManager manager = new UserManager();

        while (rd.Read())
        {
            int productId = int.Parse((rd.GetValue(0).ToString()));
            string productName = (rd.GetValue(1).ToString());
            string productNameHindi = (rd.GetValue(2).ToString());
            decimal productPrice = decimal.Parse((rd.GetValue(3).ToString()));
            string productStatus = (rd.GetValue(4).ToString());
            manager.UpdateProductMasterFromExcelSheet(productId, productName);
        }
    }
    else
    {
        objXConn.Close();
    }
    msgBox1.alert("Products updated successfully");
}

}

i am using Following connection string..

<add name="sqlXCon" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source='|DataDirectory|ProductImport.xls';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';" />

The file Report.xls exports succesfully. But the problem is I'm not able to import "Report.xls" , unless i open the file and again save it as .xls. Why this is happening?

Answer 1

For More info abour Excel InterOp. Please refer this site..

Excel.Interop

Answer 2

The reason this is happening is because you're not really exporting an Excel file : your LinkbuttonExportToExcel_Click generates a tab separated value file, which can be opened by Excel, but is not an Excel file.

On the other hand, your ButtonImportDataFromExcel_Click function reads from an Excel file (which must then be a real Excel file, and not a TSV)

So you actually have to open it manually and convert it as xls by saving it again.

For that, you could generate your Excel file using Excel.Interop if you have Excel on your server, or use an open source library to generate your excel file.

Answer 3

USE Excel Libraray, download it from http://code.google.com/p/excellibrary/downloads/list

using System;
using System.Data;
using System.IO;
 using ExcelLibrary.SpreadSheet;

 namespace ExcelLibrary
 {
 /// <summary>
 /// Provides simple way to convert Excel workbook into DataSet
 /// </summary>
 public sealed class DataSetHelper
 {
/// <summary>
/// Populate all data (all converted into String) in all worksheets 
/// from a given Excel workbook.
/// </summary>
/// <param name="filePath">File path of the Excel workbook</param>
/// <returns>DataSet with all worksheet populate into DataTable</returns>
public static DataSet CreateDataSet(String filePath)
{
    DataSet ds = new DataSet();
    Workbook workbook = Workbook.Load(filePath);
    foreach (Worksheet ws in workbook.Worksheets)
    {
        DataTable dt = PopulateDataTable(ws);
        ds.Tables.Add(dt);
    }
    return ds;
}

/// <summary>
/// Populate data (all converted into String) from a given Excel 
/// workbook and also work sheet name into a new instance of DataTable.
/// Returns null if given work sheet is not found.
/// </summary>
/// <param name="filePath">File path of the Excel workbook</param>
/// <param name="sheetName">Worksheet name in workbook</param>
/// <returns>DataTable with populate data</returns>
public static DataTable CreateDataTable(String filePath, String sheetName)
{
    Workbook workbook = Workbook.Load(filePath);
    foreach (Worksheet ws in workbook.Worksheets)
    {
        if (ws.Name.Equals(sheetName))
            return PopulateDataTable(ws);
    }
    return null;
}

private static DataTable PopulateDataTable(Worksheet ws)
{
    CellCollection Cells = ws.Cells;

    // Creates DataTable from a Worksheet
    // All values will be treated as Strings
    DataTable dt = new DataTable(ws.Name);

    // Extract columns
    for (int i = 0; i <= Cells.LastColIndex; i++)
        dt.Columns.Add(Cells[0, i].StringValue, typeof(String));

    // Extract data
    for (int currentRowIndex = 1; currentRowIndex <= Cells.LastRowIndex; currentRowIndex++)
    {
        DataRow dr = dt.NewRow();
        for (int currentColumnIndex = 0; currentColumnIndex <= Cells.LastColIndex; currentColumnIndex++)
            dr[currentColumnIndex] = Cells[currentRowIndex, currentColumnIndex].StringValue;
        dt.Rows.Add(dr);
    }

    return dt;
}

/// <summary>
/// Populate all data from the given DataSet into a new Excel workbook
/// </summary>
/// <param name="filePath">File path to new Excel workbook to be created</param>
/// <param name="dataset">Source DataSet</param>
public static void CreateWorkbook(String filePath, DataSet dataset)
{
    if (dataset.Tables.Count == 0)
        throw new ArgumentException("DataSet needs to have at least one DataTable", "dataset");

    Workbook workbook = new Workbook();
    foreach (DataTable dt in dataset.Tables)
    {
        Worksheet worksheet = new Worksheet(dt.TableName);
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            // Add column header
            worksheet.Cells[0, i] = new Cell(dt.Columns[i].ColumnName);

            // Populate row data
            for (int j = 0; j < dt.Rows.Count; j++)
                worksheet.Cells[j + 1, i] = new Cell(dt.Rows[j][i]);
        }
        workbook.Worksheets.Add(worksheet);
    }
    workbook.Save(filePath);
}
 }
}