Click here to Skip to main content
Click here to Skip to main content

Import and Export to Multiple Worksheets

, 12 Feb 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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:

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:

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:

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.

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:

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:

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)

Share

About the Author

S Gnana Prakash
Web Developer
India India
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 PinmemberARKishore19-Apr-12 0:12 
QuestionCan we export data from Sheet1? Pinmembera_anajwala29-Dec-11 2:14 
QuestionAwesome.. Pinmembershahrouz199930-Aug-11 2:08 
You never know when you will be helping somebody.. I had a requirement to transfer over 300 excel files to SQL but the problem was each had multiple sheets. This saved me lots of time..
 
You are awesome!
 
Thank you!
GeneralStream to memory and then export to excel PinmemberMeDeeSa6-Aug-09 11:35 
GeneralRe: Stream to memory and then export to excel Pinmembergg423715-Nov-09 23:54 
GeneralMy vote of 2 PinmemberQistoph15-Feb-09 20:29 
GeneralRe: My vote of 2 PinmemberS Gnana Prakash22-Feb-09 22:42 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141030.1 | Last Updated 12 Feb 2009
Article Copyright 2009 by S Gnana Prakash
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid