Reputation: 1256
This is my original code for setting content of Excel sheet. In short, the code iterates through all rows and columns and set three things: cell content, cell font color and cell background color:
Excel_xp::ExcelApplicationPtr app = excel->Application;
app->set_ScreenUpdating(LOCALE_USER_DEFAULT, TVariant(false));
Excel_xp::ExcelRangePtr cells = worksheet->Cells;
cells->Clear();
for (int i = 0; i < line_count; i++)
{
CsvLine *line = lines[i];
int col_count = line->GetColCount();
for (int j = 0; j < col_count; j++)
{
CsvLine::Data data;
line->GetData(j, data);
TVariant _cell = cells->get_Item(TVariant(i+1), TVariant(j+1));
IDispatch *__cell = (IDispatch*)_cell;
Excel_xp::ExcelRange *cell = NULL;
if (__cell->QueryInterface(DIID_ExcelRange, (void**)&cell) != S_OK)
{
__cell->Release();
__cell->Release();
__cell = NULL;
continue;
}
cell->set_Item(TVariant(1), TVariant(1), TVariant(data.str));
TColor font_color, back_color;
if (data.good || ((!data.good) && (!highlight_bad_values)))
{
font_color = (TColor)0;
back_color = (TColor)-4142;
}
else
{
font_color = (TColor)0x06009C;
back_color = (TColor)0xCEC7FF;
}
Excel_xp::FontPtr font = cell->Font;
font->set_Color(TVariant(font_color));
font->Release();
font = (IUnknown*)NULL;
Excel_xp::InteriorPtr back = cell->Interior;
back->set_Color(TVariant(back_color));
back->Release();
back = (IUnknown*)NULL;
cell->Release();
cell->Release();
cell->Release();
cell = NULL;
}
}
Previous code even when functionally OK had one drawback namely speed. Every call of function from COM interface has some overhead (cca 1 - 3 ms). To generate sheet consisting of 900 rows x 6 columns I needed around 38 seconds which was unacceptable in comparison with performance of todays computets. So I optimized the code. Instead of setting cell by cell I first prepared the whole data in memory in one big array and set content of whole sheet in one step. Later I can optimize it further to minimize memory requirements of my application. I can limit the maximum number of rows written in one step for example to 100. But this is irrelevant to my problem. This is my optimized code:
Excel_xp::ExcelApplicationPtr app = excel->Application;
app->set_ScreenUpdating(LOCALE_USER_DEFAULT, TVariant(false));
Excel_xp::ExcelRangePtr cells = worksheet->Cells;
cells->Clear();
int bounds[] = {1, lines.size(), 1, GetMaxColCount()};
Variant arr_text = VarArrayCreate(bounds, 3, varVariant);
int line_count = lines.size();
int arr_idx[2];
for (int i = 0; i < line_count; i++)
{
arr_idx[0] = i + 1;
CsvLine *line = lines[i];
int col_count = line->GetColCount();
for (int j = 0; j < col_count; j++)
{
arr_idx[1] = j + 1;
CsvLine::Data data;
line->GetData(j, data);
VarArrayPut(arr_text, TVariant(data.str), arr_idx, 1);
}
}
TVariant _cell1 = cells->get_Item(TVariant(1), TVariant(1)); // ref_count = 1
TVariant _cell2 = cells->get_Item(TVariant(bounds[1]), TVariant(bounds[3])); // ref_count = 1
Excel_xp::ExcelRangePtr rng = cells->get_Range(_cell1, _cell2);
_cell1 = NULL;
_cell2 = NULL;
rng->set_Value2(arr_text);
arr_text.Clear();
rng->Release();
rng = (IUnknown*)NULL;
Now I am trying to use the same mechanism for cell font. Is it possible to prepare font color array (where every cell can use different color) and set it for worksheet in one call ?
P.S. The answer should be preferably in C++ but can be also in Delphi.
Upvotes: 0
Views: 650