Click here to Skip to main content
15,867,885 members
Articles / Programming Languages / C#

Import and Export to Multiple Worksheets

Rate me:
Please Sign up or sign in to vote.
3.00/5 (5 votes)
12 Feb 2009CPOL 66.3K   1.8K   15   7
Import and export to multiple Worksheets.

Introduction

This article will explain how to import multiple Worksheets content to a database and export content from the data base to multiple Worksheets in Excel.

Import multiple Worksheet contents to a database

Step 1

Upload the file into a server using the ASP.NET FileUpload control.

C#
public static void uploadXLFile(FileUpload fileUpload, string mPath)
{       
    mPath = mPath + fileUpload.FileName.ToString();
    fileUpload.SaveAs(mPath);
}

Step 2

Once the Excel file is uploaded to the server, read the Excel file using the OLEDB adapter and get the Excel sheet names using the code below:

C#
public static string[] getExcelSheets(string mFile)
{
    try
    {
        string strXlsConnString;
        strXlsConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
                           mFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
        OleDbConnection xlsConn = new OleDbConnection(strXlsConnString);
        xlsConn.Open();
        DataTable xlTable = new DataTable();
        xlTable = xlsConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        System.String strExcelSheetNames = "";
        string sheetName;
        //Loop through the excel database table names and take only 
        //the tables that ends with a $ characters. Other tables are not worksheets...
        for (int lngStart = 0; lngStart < xlTable.Rows.Count; lngStart++)
        {
            //Remove the single-quote surrounding the table name...
            sheetName = xlTable.Rows[lngStart][2].ToString().Replace("'", "");
            if (sheetName.EndsWith("$")) //Yes, this is a worksheet
            {
                //concatenate with a single-quote delimeter... to be returned 
                //as a string array later using the split function
                strExcelSheetNames += sheetName.Substring(0, sheetName.Length - 1) + "~";
            }
        }

        if (strExcelSheetNames.EndsWith("~"))
        //the last single quote needs to be removed 
        //so that the array index ends with the last sheetname
        {
            strExcelSheetNames = strExcelSheetNames.Substring(0, 
                                    strExcelSheetNames.Length - 1);
        }

        xlsConn.Close();
        xlsConn.Dispose();
        char[] chrDelimter = { '~' };
        return strExcelSheetNames.Split(chrDelimter);
    }
    catch (Exception exp)
    {
        throw new Exception("Error while listing the excel" + 
                            " sheets from upload file " + exp.Message, exp);
    }
}

Step 3

Once sheet names are stored in a string array variable, you can read the contents from the Excel sheet using the OLEDB adapter:

C#
public static DataSet getXLData(string xlSheetName, 
              string xlFileName, string AdditionalFields)
{
    try
    {
        string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
                         xlFileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
        OleDbConnection xlConn = new OleDbConnection(connstr);
        DataSet xlTDS = new DataSet("xlDataSet");
        xlConn.Open();
        OleDbDataAdapter xlDA = new OleDbDataAdapter("Select" + AdditionalFields + 
                                " * from [" + xlSheetName + "$] ", xlConn);
        xlDA.Fill(xlTDS);
        xlConn.Close();
        xlConn.Dispose();

        RemoveEmptyRows(xlTDS.Tables[0], (AdditionalFields.Length - 
                  AdditionalFields.ToLower().Replace(" as ", "").Length) / 4);
        return xlTDS;
    }
    catch (Exception e)
    {
        throw new Exception("Error while reading data from excel sheet", e);
    }
}

public static void RemoveEmptyRows(DataTable dtbl, 
                   System.Int32 intNumberOfFieldsToIgnore)
{
    System.String strFilter = "";
    //Check at least 3/4th of the columns for null value
    System.Int32 intAvgColsToCheck = 
      Convert.ToInt32((dtbl.Columns.Count - intNumberOfFieldsToIgnore) * 0.75);
    //Can't entertain checking less than three columns.
    if (intAvgColsToCheck < 3)
    {
        intAvgColsToCheck = dtbl.Columns.Count;
    }

    //Building the filter string that checks null
    //value in 3/4th of the total column numbers...

    //We will be doing it in reverse, checking the last three-quarter columns
    System.Int32 lngEnd = dtbl.Columns.Count;
    lngEnd = lngEnd - intAvgColsToCheck;
    for (int lngStartColumn = dtbl.Columns.Count; 
         lngStartColumn > lngEnd; lngStartColumn--)
    {
        strFilter += "[" + dtbl.Columns[lngStartColumn - 1].ColumnName + 
                     "] IS NULL AND ";
                     //AND to concatenate the next column in the filter
    }

    //Remove the trailing AND
    if (strFilter.Length > 1)
    //At least one column is added (and thus, the trailing AND)
    {
        strFilter = strFilter.Remove(strFilter.Length - 4);
    }
    DataRow[] drows = dtbl.Select(strFilter);

    //Remove the rows that are empty...
    foreach (DataRow drow in drows)
    {
        dtbl.Rows.Remove(drow);
    }
}

Step 4

Then, you can use a bulk upload function to insert the contents to the database:

C#
public static void BulkCopy(DataTable dtTable)
{
    SqlConnection SqlConn1 = new SqlConnection(System.Configuration.
      ConfigurationManager.ConnectionStrings["ArmsConnStr"].ToString());
    SqlConn1.Open();
    using (SqlBulkCopy bc = new SqlBulkCopy((SqlConnection)SqlConn1))
    {
        bc.DestinationTableName = "TableName";
        bc.WriteToServer(dtTable);
        bc.Close();
    }
    SqlConn1.Close();  
}

Export data from database to multiple Excel Worksheets

Step 1

First, retrieve the contents from the database (using the SQL Provider if the database is SQL Server, as in the code below). Then, you can create a sample Excel file for exporting the data.

C#
public static void ImportToMultipleXLSheets(System.String SqlSelect, 
                                            System.String mOutputFileName)
{
    string FolderPath;
    FolderPath = mOutputFileName.Remove(mOutputFileName.LastIndexOf("\\"), 
                 mOutputFileName.Length - mOutputFileName.LastIndexOf("\\"));

    File.Copy(FolderPath + "\\Sample.xls", mOutputFileName, true);

    SqlConnection SqlConn = new SqlConnection(System.Configuration.
      ConfigurationManager.ConnectionStrings["ArmsConnStr"].ToString());
    SqlConn.Open();
    DataSet DS = new DataSet();
    string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
                     mOutputFileName + ";Extended Properties='Excel 8.0'";
    OleDbConnection xlConn = new OleDbConnection(connstr);

    try
    {
        xlConn.Open();

        SqlDataAdapter SqlDA = new SqlDataAdapter(SqlSelect, SqlConn);
        SqlDA.Fill(DS);
        SqlConn.Close();
        SqlConn.Dispose();
        PrepareScript(DS.Tables[0]);
        StartImport(DS.Tables[0], xlConn);

    }
    catch (Exception exp)
    {
        throw new Exception("ImportToMultipleXLSheets", exp.InnerException);
    }
    finally
    {
        if (xlConn != null)
        {
            if (xlConn.State == ConnectionState.Open) xlConn.Close();
            xlConn.Dispose();
        }
        if (SqlConn != null)
        {
            if (SqlConn.State == ConnectionState.Open) SqlConn.Close();
            SqlConn.Dispose();
        }
    }
}

Step 2

Then, generate the table and insert the script using the function below:

C#
private static string PrepareScript(DataTable DTable)
{
    // Prepare Scripts to create excel Sheet

    SqlInsert.Length = 0;
    SqlScript.Length = 0;
    for (int i = 0; i < DTable.Columns.Count; i++)
    {
        SqlInsert.Append(DTable.Columns[i].ColumnName + ",");

        SqlScript.Append("[" + DTable.Columns[i].ColumnName.Replace("'", "''") + "]");

        if (DTable.Columns[i].DataType.ToString().ToLower().Contains("int") || 
               DTable.Columns[i].DataType.ToString().ToLower().Contains("decimal"))
            SqlScript.Append(" double");
        else
            SqlScript.Append(" text");

        SqlScript.Append(", ");
    }
    SqlInsert.Remove(SqlInsert.Length - 1, 1);
    SqlScript.Remove(SqlScript.Length - 2, 1);
    SqlScript.Append(") ");
    return SqlScript.ToString();
}

Step 3

Once the scripts are generated, then we can start the export function:

C#
private static void StartImport(DataTable DTable, OleDbConnection xlConn)
{
    Int64 rowNo = 0, xlSheetIndex = 2, TotalNoOfRecords = 0;

    System.String NewXLSheetName = "Sheet";
    System.Text.StringBuilder strInsert = new System.Text.StringBuilder();
    TotalNoOfRecords = DTable.Rows.Count;
    OleDbCommand cmdXl = new OleDbCommand();
    cmdXl.Connection = xlConn;
    if (mPredefineFile) xlSheetIndex = 1;
    for (int count = 0; count < DTable.Rows.Count; count++)
    {
        strInsert.Length = 0;

        if (rowNo == 0 && !mPredefineFile)
            CreateXLSheets(DTable, xlConn, NewXLSheetName + xlSheetIndex);
        rowNo += 1;

        // TotalNoOfRecords : Total no of records return
        // by Sql Query, ideally should be set to 65535
        // rowNo : current Row no in the loop 
        if (TotalNoOfRecords > 5000 && rowNo > 5000)
        {
            xlSheetIndex += 1;
            if (!mPredefineFile)
                CreateXLSheets(DTable, xlConn, NewXLSheetName + xlSheetIndex);
            rowNo = 1;
        }
        strInsert.Append("Insert Into [" + NewXLSheetName + xlSheetIndex.ToString() + 
                         "$](" + SqlInsert.ToString() + ") Values (");
        foreach (DataColumn dCol in DTable.Columns)
        {
            if (dCol.DataType.ToString().ToLower().Contains("int"))
            {
                if (DTable.Rows[count][dCol.Ordinal].ToString() == "")
                    strInsert.Append("NULL");
                else
                    strInsert.Append(DTable.Rows[count][dCol.Ordinal]);
            }
            else if (dCol.DataType.ToString().ToLower().ToLower().Contains("decimal"))
            {
                if (DTable.Rows[count][dCol.Ordinal].ToString() == "")
                    strInsert.Append("NULL");
                else
                    strInsert.Append(DTable.Rows[count][dCol.Ordinal]);
            }
            else
                strInsert.Append("\"" + 
                  DTable.Rows[count][dCol.Ordinal].ToString().Replace("'", 
                  "''") + "\"");

            strInsert.Append(",");
        }
        strInsert.Remove(strInsert.Length - 1, 1);
        strInsert.Append(");");
        cmdXl.CommandText = strInsert.ToString();
        cmdXl.ExecuteNonQuery();
    }
}

private static void CreateXLSheets(DataTable DTable, 
                    OleDbConnection xlConn, System.String XLSheetName)
{
    // Create New Excel Sheet

    System.Text.StringBuilder SqlFinalScript = new System.Text.StringBuilder();
    OleDbCommand cmdXl = new OleDbCommand();
    try
    {
        SqlFinalScript.Length = 0;
        cmdXl.Connection = xlConn;

        SqlFinalScript.Append("CREATE TABLE " + XLSheetName + " (");
        SqlFinalScript.Append(SqlScript.ToString());

        cmdXl.CommandText = SqlFinalScript.ToString();
        cmdXl.ExecuteNonQuery();

    }           
    catch (Exception xlSheetExp)
    {
        throw (new Exception("CreateXLSheetException", xlSheetExp.InnerException));
    }
    finally
    {
        cmdXl.Dispose();
    }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionLooks awesome, how to implement? Pin
Member 1219923110-Dec-15 7:11
Member 1219923110-Dec-15 7:11 
GeneralMy vote of 5 Pin
ARKishore19-Apr-12 0:12
ARKishore19-Apr-12 0:12 
QuestionCan we export data from Sheet1? Pin
a_anajwala29-Dec-11 2:14
a_anajwala29-Dec-11 2:14 
QuestionAwesome.. Pin
shahrouz199930-Aug-11 2:08
shahrouz199930-Aug-11 2:08 
GeneralStream to memory and then export to excel Pin
MeDeeSa6-Aug-09 11:35
MeDeeSa6-Aug-09 11:35 
GeneralMy vote of 2 Pin
Qistoph15-Feb-09 20:29
Qistoph15-Feb-09 20:29 
GeneralRe: My vote of 2 Pin
S Gnana Prakash22-Feb-09 22:42
S Gnana Prakash22-Feb-09 22:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.