Solved

VS C# Processing Excel Spreadsheet

Posted on 2013-05-26
4
466 Views
Last Modified: 2013-05-30
I have this code and it works. I have spreadsheets with 7 columns of data and I extract what I need and send it to a SQL database. The issue is of speed. The spreadsheets contain histotical data and each year there is more and more data. The first year had 250 records and my program processed this in about a minute. The second year had 10,000 records and that took about 10 minutes. The third year had 25,000 and that took over 2 hours. The years keep getting more records so the next year will take over 4 hours for just 50,000 records. Is there anything that can be done to this code to speed it up? I watched with Task Manager as this runs and the CPU hits 100% and it is Excel that is chewing up the CPU.

private void Extract_Transactions()
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(InputFilename);
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;
           
            for (int i = 2; i <= rowCount; i++)
            {
                for (int j = 1; j <= colCount; j++)
                {
                    switch (j)
                    {
                        case 1 : if (xlRange.Cells[i, j].Value2 == null)
                                 {
                                    PT_ACTNUMST = " ";
                                 }
                                 else
                                 {
                                    PT_ACTNUMST = xlRange.Cells[i, j].Value2.ToString();
                                 }
                                 break;
                        case 2 : if (xlRange.Cells[i, j].Value2 == null)
                                 {
                                 PT_TRXDATE = BadDate;
                                 }
                                 else
                                 {
                                    PT_TRXDATE = DateTime.FromOADate(xlRange.Cells[i, j].Value2);
                                 }
                                 break;                    
                        case 3 : if (xlRange.Cells[i, j].Value2 == null)
                                 {
                                    PT_REFRENCE = " ";
                                 }
                                 else
                                 {
                                    PT_REFRENCE = xlRange.Cells[i, j].Value2.ToString();
                                 }
                                 break;
                        case 4: if (xlRange.Cells[i, j].Value2 == null)
                                 {
                                    PT_JOURNAL = " ";
                                 }
                                 else
                                 {
                                 PT_JOURNAL = xlRange.Cells[i, j].Value2.ToString();
                                 }
                                 break;
                        case 5: if (xlRange.Cells[i, j].Value2 == null)
                                {
                                    PT_DESCPTON = " ";
                                }
                                else
                                {
                                    PT_DESCPTON = xlRange.Cells[i, j].Value2.ToString();
                                }
                                break;
                        case 7: if (xlRange.Cells[i, j].Value2 == null)
                                {
                                    PT_TRXAMNT = 0.00M;
                                }
                                else
                                {
                                    PT_TRXAMNT = Convert.ToDecimal(xlRange.Cells[i, j].Value2.ToString());
                                }
                                break;
                        }
                  }

                if (PT_JOURNAL.Length > 0 && PT_REFRENCE.Length > 0  )
                {
                    WriteGLTransactionRecords();
                    rc++;
                }
            }
            MessageBox.Show("Number of GL transaction records imported : " + rc.ToString());
        }
0
Comment
Question by:rwheeler23
4 Comments
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 300 total points
ID: 39197550
0
 
LVL 18

Assisted Solution

by:Raheman M. Abdul
Raheman M. Abdul earned 100 total points
ID: 39197576
Try this:
//-------------------------
private void Extract_Transactions()
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(InputFilename);
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;
           
            //declare cellValue variable here
           
            for (int i = 2; i <= rowCount; i++)
            {
                for (int j = 1; j <= colCount; j++)
                {
                   
                    cellValue=xlRange.Cells[i, j].Value2;
                   
                    switch (j)
                    {
                        case 1 : if (cellValue == null)
                                 {
                                    PT_ACTNUMST = " ";
                                 }
                                 else
                                 {
                                    PT_ACTNUMST = cellValue.ToString();
                                 }
                                 break;
                        case 2 : if (cellValue == null)
                                 {
                                 PT_TRXDATE = BadDate;
                                 }
                                 else
                                 {
                                    PT_TRXDATE = DateTime.FromOADate(cellValue);
                                 }
                                 break;                    
                        case 3 : if (cellValue == null)
                                 {
                                    PT_REFRENCE = " ";
                                 }
                                 else
                                 {
                                    PT_REFRENCE = cellValue.ToString();
                                 }
                                 break;
                        case 4: if (cellValue == null)
                                 {
                                    PT_JOURNAL = " ";
                                 }
                                 else
                                 {
                                 PT_JOURNAL = cellValue.ToString();
                                 }
                                 break;
                        case 5: if (cellValue == null)
                                {
                                    PT_DESCPTON = " ";
                                }
                                else
                                {
                                    PT_DESCPTON = cellValue.ToString();
                                }
                                break;
                        case 7: if (cellValue == null)
                                {
                                    PT_TRXAMNT = 0.00M;
                                }
                                else
                                {
                                    PT_TRXAMNT = Convert.ToDecimal(cellValue.ToString());
                                }
                                break;
                        }
                  }

                if (PT_JOURNAL.Length > 0 && PT_REFRENCE.Length > 0  )
                {
                    WriteGLTransactionRecords();
                    rc++;
                }
            }
            MessageBox.Show("Number of GL transaction records imported : " + rc.ToString());
        }
//--------------------------------------
0
 

Author Comment

by:rwheeler23
ID: 39197622
Interesting thoouhgt about bulk copy. There is garbage data in the spreadsheets that I was filtering but I could always filter that away once the data is in SQL. I am curious about relocating the CellValue declaration. How would I define it since it can be a number, string or data value? Why would it make a difference whether I do it inside or outside of the case statement?
0
 
LVL 42

Assisted Solution

by:sedgwick
sedgwick earned 100 total points
ID: 39197650
import the excel worksheet to datatable first, then work on the datatable.
public static DataSet exceldata(string InputFilename)
{
 DataSet ds = new DataSet();

 OleDbCommand excelCommand = new OleDbCommand();OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();

 string excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + InputFilename + "; Extended Properties =Excel 8.0;";

OleDbConnection excelConn = new OleDbConnection(excelConnStr);

excelConn.Open();

DataTable dtPatterns = new DataTable();excelCommand = new OleDbCommand("SELECT `PATTERN` as PATTERN, `PLAN` as PLAN FROM [PATTERNS$]", excelConn);

excelDataAdapter.SelectCommand = excelCommand;

excelDataAdapter.Fill(dtPatterns);

"dtPatterns.TableName = Patterns";

ds.Tables.Add(dtPatterns);

return ds;

}

Open in new window

0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question