При экспорте из отчета в Excel ячейки могут содержать не только значения, но и формулы. На этой странице описано, как можно упростить или автоматизировать работу с формулами.
Имя колонки по номеру
Для автоматизации формирования формулы удобно использовать функцию, которая возвращает имя (букву) колонки по ее номеру (наприме, для 1 — A, для 2 — B и т.д.).
// Имя колонки в Excel по номеру
private string ColumnNumberToName(int col_num)
{
// See if it's out of bounds
if (col_num < 1) return "A";
// Calculate the letters
string result = "";
while (col_num > 0)
{
// Get the least significant digit
col_num -= 1;
int digit = col_num % 26;
// Convert the digit into a letter
result = (char)((int)'A' + digit) + result;
col_num = (int)(col_num / 26);
}
return result;
}
|
Последовательность расчета формул
Если пользователи в дальнейшем будут работать с выгруженным файлом Excel, в том числе будут менять значения ячеек, то данные должны пересчитываться по формулам. В этом случае может быть важна последовательность расчета формул. Чтобы учесть эту последовательность, можно использовать следующее решение:
•в начало каждой формулы помещается цифра, определяющая очередность выполнения. Например, все формулы, начинающиеся с цифры 1, будут выполняться в первую очередь, все формулы, начинающиеся с 2 — во вторую очередь и т.д. Порядок выполнения формул в рамках одной очереди неважен.
пример процедур в отчете
// Формирование формулы Excel 1-ой очереди
private void Matrix2ResultTable_AfterData(object sender, EventArgs e)
{
int[] rowIndices = Matrix1.Data.Rows.GetTerminalIndices();
int[] columnIndices = Matrix1.Data.Columns.GetTerminalIndices();
TableResult table = sender as TableResult;
for (int i = 1; i < table.Rows.Count-1; i++)
{
string rownum = (i + 17).ToString();
string str1 = ColumnNumberToName(11 + 1)+rownum;
string str2 = ColumnNumberToName(11 + columnIndices.Length)+rownum;
string resultstr = "1=СУММ(" + str1 + ":" + str2 + ")";
table[10, i].Text = resultstr;
}
}
// Формирование формулы Excel 2-ей очереди
private void Matrix2_AfterTotals(object sender, EventArgs e)
{
int[] rowIndices = Matrix2.Data.Rows.GetTerminalIndices();
int[] columnIndices = Matrix2.Data.Columns.GetTerminalIndices();
foreach (int columnIndex in columnIndices)
{
string colnum = ColumnNumberToName(10);
string value = "2=СУММ("+colnum+"18:"+colnum+(18+rowIndices.Length-2).ToString()+")";
Matrix2.Data.SetValue(columnIndex, rowIndices.Length-1, value);
}
}
|
•для запуска очередности выполнения формул можно использовать макрос Excel. Этот макрос может быть сохранен в отдельном файле .xlam и загружаться/запускаться как AddIn.
Sub CalcFunc()
Dim Sh As Worksheet
Dim Cl As Range
Dim tmpVal As Variant
Dim Cell(1) As Variant
Dim Ar() As Variant
Dim i As Integer
Dim j As Integer
Dim isSorted As Boolean
Dim cnt As Integer
i = 0
For Each Sh In Worksheets
For Each Cl In Sh.UsedRange.Cells
If Right(Left(Cl.Value, 2), 1) = "=" Or Left(Cl.Value, 1) = "=" Then
ReDim Preserve Ar(i)
Cell(0) = Cl.Value
Cell(1) = Cl.Address
Ar(i) = Cell
i = i + 1
End If
Next Cl
Next Sh
i = 0
cnt = 0
isSorted = False
Do While Not isSorted
cnt = cnt + 1
isSorted = True
tmpVal = Ar(0)
For j = 1 To UBound(Ar)
If Left(tmpVal(0), 1) = "=" Then
If Left(Ar(i)(0), 1) <> Left(Ar(j)(0), 1) Then
isSorted = False
End If
Ar(i) = Ar(j)
Ar(j) = tmpVal
ElseIf Left(Ar(j)(0), 1) = "=" Then
tmpVal = Ar(j)
Else
If CInt(Left(tmpVal(0), 1)) > CInt(Left(Ar(j)(0), 1)) Then
Ar(i) = Ar(j)
Ar(j) = tmpVal
isSorted = False
Else
tmpVal = Ar(j)
End If
End If
i = j
Next
If cnt >= 10000 Then
MsgBox "Error While Loop"
isSorted = True
End If
Loop
For Each tmpVal In Ar
If Left(tmpVal(0), 1) = "=" Then
Range(tmpVal(1)).FormulaLocal = tmpVal(0)
Else
Range(tmpVal(1)).FormulaLocal = Right(tmpVal(0), Len(tmpVal(0)) — 1)
End If
Next tmpVal
Worksheets(1).Visible = True
Worksheets(1).Cells(1, 1).Select
End Sub
|
