I’m trying to convert a large CSV file (up to 65K records with about 50 columns) to Excel format, primarily for the purpose of creating hyperlinks in one of the columns. It’s working, but the process takes a long time, consumes a large amount of memory and the CPU utilization is very high the entire time. Stats for a 25,000 record file on a P4 2.4 GHz with 1GB RAM are:
Load duration: 25 sec
Hyperlink creation duration: 185 sec
Save duration: 159 sec
Memory consumed: 176 MB
CPU utiliziation: 95%+
if I take out the step to create the hyperlinks, it only takes about 28 seconds to save. Is there anything I can do to improve any of these metrics? My code is below:
Excel ss = new Excel();
DateTime start = DateTime.Now;
ss.Open(“itr 2005073b.vsi”, FileFormatType.CSV);
int loaded = (int)DateTime.Now.Subtract(start).TotalMilliseconds;
Worksheet ws = ss.Worksheets[0];
int hlIdx = 0;
for(int i = 4; i <= ws.Cells.MaxRow; i++)
{
string url = ws.Cells[i, 39].Value.ToString();
if (url != null)
{
ws.Cells.ClearRange(i, 39, i, 39);
ws.Hyperlinks.Add(i, 39, 1, 1, url);
ws.Hyperlinks[hlIdx++].TextToDisplay = “Detail”;
}
}
int formatted = (int)DateTime.Now.Subtract(start).TotalMilliseconds;
ss.Save(“itr 2005073b.xls”, FileFormatType.Excel2000);
int saved = (int)DateTime.Now.Subtract(start).TotalMilliseconds;
MessageBox.Show(loaded.ToString() + " " + formatted.ToString() + " " + saved.ToString());