Solved

VS C# Processing Excel Spreadsheet

Posted on 2013-05-26
4
465 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 4 160
Data Saving(2) 2 41
bulid json format 3 46
Vb.net dynamic formulas in runtime 11 63
Introduction                                                 Was the var keyword really only brought out to shorten your syntax? Or have the VB language guys got their way in C#? What type of variable is it? All will be revealed.   Also called…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now