none
Method to Import Excel File to Data Table and Export Specific Elements to a Different Excel File RRS feed

  • Question

  • I'm trying to write code in a Winform that will enable me to import an Excel file, which contains 44 columns and an undefined number of rows, to a data table and export specific rows back out to another Excel file. I definitely need to import the entire Excel file, because before generating the export I need my program to do work on each row of data to present in my export file. The solution cannot utilize EPPlus, as it needs to work for both .xls & .xlsx files.

    So, for example, I will import columns A, B, C & D into my program, do work on that data, and export an Excel file that contains column A from the import file and column B with results the work I've done on the data.

    Currently this is the code I have in place:

    public void loadExcelBulkData()
            {
                string[] excelSheets = getSheetNames();
    
                String pathConn = "";
                if (fileExtension == ".xls")
                {
                    pathConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                        path +
                        ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                }
                else
                {
                    if (fileExtension == ".xlsx")
                    {
                        pathConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                            path +
                            ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    }
                }
    
                using (OleDbConnection conn = new OleDbConnection(pathConn))
                {
                    for (int i = 0; i < excelSheets.Length; i++)
                    {
                        OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" +
                            excelSheets[i] + "]", conn);
                        System.Data.DataTable dt = new System.Data.DataTable();
    
                        adapter.Fill(dt);
                        string dtBrokerName = dt.Rows[0].Field<string>("ProducerName");
      
    //I import 43 other fields, and do some work on them here
    
                        // create final output report based on imported data
                        GenerateExcelData(dtBrokerName, dtExpirationDate, dtPolicyNumber, dtCurrentPremium, 
                            tier, usage, city, dtcValue);
                    }
                }
            }

    Calling this method:

    public void GenerateExcelData(string dtBrokerName, DateTime dtExpirationDate, string dtPolicyNumber, 
                                          double dtCurrentPremium, string tier, string usage, string city, decimal dtcValue)
            {
                CreateExcelDoc BatchRenewalReport = new CreateExcelDoc();
                //creates the header row
                BatchRenewalReport.createHeaders(1, 1, "Producer", "A1", "A1", 0, "#", true, 30, "n");
                BatchRenewalReport.createHeaders(1, 2, "Expiration Date", "B1", "B1", 0, "#", true, 15, "n");
                BatchRenewalReport.createHeaders(1, 3, "Policy #", "C1", "C1", 0, "#", true, 12.86, "n");
                BatchRenewalReport.createHeaders(1, 4, "Current Price", "D1", "D1", 0, "#",  true, 15.71, "n");
                BatchRenewalReport.createHeaders(1, 5, "New Price", "E1", "E1", 0, "#", true, 15.71, "n");
                BatchRenewalReport.createHeaders(1, 6, "$ Difference", "F1", "F1", 0, "#", true, 12.14, "n");
                BatchRenewalReport.createHeaders(1, 7, "% Difference", "G1", "G1", 0, "#", true, 12.14, "n");
                BatchRenewalReport.createHeaders(1, 8, "Tier", "H1", "H1", 0, "#", true, 15, "n");
                BatchRenewalReport.createHeaders(1, 9, "Usage", "I1", "I1", 0, "#", true, 9, "n");
                BatchRenewalReport.createHeaders(1, 10, "City", "J1", "J1", 0, "#", true, 16.43, "n");
                BatchRenewalReport.createHeaders(1, 11, "DTC", "K1", "K1", 0, "#", true, 6.43, "n");
    
                //fills additional rows
                BatchRenewalReport.addData(2, 1, dtBrokerName, "A2", "A2", "");
                BatchRenewalReport.addData(2, 2, dtExpirationDate.ToString(), "B2", "B2", "mm/dd/yyyy");
                BatchRenewalReport.addData(2, 3, dtPolicyNumber, "C2", "C2", "");
                BatchRenewalReport.addData(2, 4, dtCurrentPremium.ToString(), "D2", "D2", "$#,##0");
                BatchRenewalReport.addData(2, 5, "3117", "E2", "E2", "$#,##0");
                BatchRenewalReport.addData(2, 6, "=E2-D2", "F2", "F2", "$#,##0");
                BatchRenewalReport.addData(2, 7, "=(E2/D2)-1", "G2", "G2", "#.#0%");
                BatchRenewalReport.addData(2, 8, tier, "H2", "H2", "");
                BatchRenewalReport.addData(2, 9, usage, "I2", "I2", "");
                BatchRenewalReport.addData(2, 10, city, "J2", "J2", "");
                BatchRenewalReport.addData(2, 11, dtcValue.ToString(), "K2", "K2", "0#.##0"); 
            }

    Which references this class developed by a 3rd party.

    class CreateExcelDoc
            {
                private Excel.Application app = null;
                private Excel.Workbook workbook = null;
                private Excel.Worksheet worksheet = null;
                private Excel.Range workSheet_range = null;
                public CreateExcelDoc()
                {
                    createDoc();
                }
                public void createDoc()
                {
                    try
                    {
                        app = new Excel.Application();
                        app.Visible = true;
                        workbook = app.Workbooks.Add(1);
                        worksheet = (Excel.Worksheet)workbook.Sheets[1];
                    }
                    catch (Exception e)
                    {
                        Console.Write("Error");
                    }
                    finally
                    {
                    }
                }
    
                public void createHeaders(int row, int col, string htext, string cell1,
                string cell2, int mergeColumns, string b, bool font, double size, string
                fcolor)
                {
                    worksheet.Cells[row, col] = htext;
                    workSheet_range = worksheet.get_Range(cell1, cell2);
                    workSheet_range.Merge(mergeColumns);
                    switch (b)
                    {
                        case "Black":
                            workSheet_range.Interior.Color = Color.Black.ToArgb();
                            break;
                        case "Gray":
                            workSheet_range.Interior.Color = Color.Gray.ToArgb();
                            break;
                        case "Red":
                            workSheet_range.Interior.Color = Color.Red.ToArgb();
                            break;
                        case "Green":
                            workSheet_range.Interior.Color = Color.ForestGreen.ToArgb();
                            break;
                        case "Yellow":
                            workSheet_range.Interior.Color = Color.LightYellow.ToArgb();
                            break;
                        default:
                            //  workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
                            break;
                    }
                    workSheet_range.Borders.Color = Color.Black.ToArgb();
                    workSheet_range.Font.Bold = font;
                    workSheet_range.ColumnWidth = size;
                    if (fcolor.Equals(""))
                    {
                        workSheet_range.Font.Color = Color.White.ToArgb();
                    }
                    else
                    {
                        workSheet_range.Font.Color = Color.Black.ToArgb();
                    }
                }
    
                public void addData(int row, int col, string data,
                    string cell1, string cell2, string format)
                {
                    worksheet.Cells[row, col] = data;
                    workSheet_range = worksheet.get_Range(cell1, cell2);
                    workSheet_range.Borders.Color = Color.Black.ToArgb();
                    workSheet_range.NumberFormat = format;
                }
            }

    This code is working to import the Excel data, do the work I need done, and export an Excel file with the desired columns, however, I am unable to get it to export multiple rows of data. I'm fairly sure that I'm going to have to pull out the class or else significantly rewrite it in order to have the code function the way I want, I just have no idea how to get the results. So far all of my attempts to alter the existing code to export a row of data for each row read have been unsuccessful.

    Any help would be much appreciated!


    • Edited by JoeCamb Wednesday, November 4, 2015 6:35 PM Clarification of Application Type
    • Moved by CoolDadTx Wednesday, November 11, 2015 3:55 PM Office related
    Wednesday, November 4, 2015 6:31 PM

All replies

  • I recently had to do a similar sort of task, this may help you.

    Excel.Worksheet excelWorkSheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet); 
    
    foreach (Excel.Range excelRow in excelWorkSheet.UsedRange.Rows.Cast<Excel.Range>().Skip(1))
    {
    Excel.Range dataRange = excelWorkSheet.get_Range("C" + excelRow.Row.ToString(), "D" + excelRow.Row.ToString());
    
    System.Array dataValues =(System.Array)dataRange.Cells.Value;
    
    string[] dataArray = ConvertToStringArray(dataValues);
    
                                
    List<String> listData = new List<string>();
    
    System.Array rowValues = (System.Array)excelRow.Cells.Value;
    
    string[] rowArray = ConvertToStringArray(rowValues);
    
    foreach (string rowCell in rowArray)
    
    {
      listData.Add(rowCell);
    }
    
    List<String> headerData = new List<string>();
    
    Excel.Range headersRange = excelWorkSheet.get_Range("A1", "BE1");
    
    System.Array headersValues = (System.Array)headersRange.Cells.Value;
    
    string[] headersArray = ConvertToStringArray(headersValues);
    
    foreach (string headerCell in headersArray)
    {
    headerData.Add(headerCell);
    }
    
                              


    Wednesday, November 4, 2015 9:25 PM
    • Edited by leo_456 Wednesday, November 11, 2015 3:47 AM
    Wednesday, November 11, 2015 3:46 AM
  • Hi,

    To improve the performance of set values using Excel object model, we can use arrays. We can build the array base on the requirement from the DataTable, then we can set the value to the specific range via refer it range.

    For example, here is example that set the Range("A1:B3") with arrays for your reference:

    Sub CopyArray()
    Dim columns As Integer
    Dim rows As Integer
    
    
    columns = 1
    rows = 2
    Dim a() As Variant
    ReDim a(rows, columns)
    
    
    For i = 0 To rows
        For j = 0 To columns
            a(i, j) = i & "," & j
        Next j
    
    Next i
    
    Range("A1:B3").Value = a
    End Sub
    

    In addition, here are some helpful links for you learning Excel developing:

    How to automate Microsoft Excel from Microsoft Visual C#.NET

    Excel VBA reference

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 12, 2015 5:28 AM
    Moderator