public void ReportToExcel()
{
int RowIndex = 1;
int TopRowIndex = RowIndex;
int BottomRowIndex = ReportTable.Rows.Count + 1;
bool NeedBorder = false;
bool NeedPaint = false;
if (Ex != null)
{
Ex.Dispose();
Ex = null;
}
Ex = new Excel();
Ex.NewDocument(1);
//Ex.SetMargins(1, 20, 0, 50, 0);
//Ex.WriteCell(1, "Клиент\\№ кухни", RowIndex, 1, 11, true);
//Ex.WriteCell(1, "Наименование", RowIndex, 2, 11, true);
//Ex.WriteCell(1, "Кол-во", RowIndex, 3, 11, true);
//Ex.WriteCell(1, "Единицы\r\nизмерения", RowIndex, 4, 11, true);
//Ex.SetWrapText(1, RowIndex, 4, true);
//Ex.WriteCell(1, "Цена", RowIndex, 5, 11, true);
//Ex.WriteCell(1, "Стоимость", RowIndex, 6, 11, true);
//RowIndex++;
for (int i = 0; i < ReportTable.Rows.Count; i++)
{
string doc = ReportTable.Rows[i][1].ToString();
doc = ReportTable.Rows[i][5].ToString();
NeedPaint = false;
if (
(ReportTable.Rows[i][1].ToString().Length > 4 &&
ReportTable.Rows[i][1].ToString().Substring(0, 4) == "отгр")
||
(ReportTable.Rows[i][5].ToString().Length > 0 &&
ReportTable.Rows[i][5 - 1].ToString().Length == 0)
)
{
NeedBorder = false;
}
else
{
NeedBorder = true;
}
if (ReportTable.Rows[i][6] != DBNull.Value && Convert.ToBoolean(ReportTable.Rows[i][6]))
{
NeedPaint = true;
NeedBorder = true;
}
for (int j = 0; j < ReportTable.Columns.Count; j++)
{
if (ReportTable.Columns[j].ColumnName == "IsError" || ReportTable.Columns[j].ColumnName == "IsSample")
{
continue;
}
if (NeedBorder)
{
Ex.SetBorderStyle(1, RowIndex, j + 1, true, true, true, true, Excel.LineStyle.xlContinuous, Excel.BorderWeight.xlThin);
}
doc = ReportTable.Rows[i][j].ToString();
bool IsSample = false;
if (ReportTable.Rows[i]["IsSample"] != DBNull.Value)
{
IsSample = Convert.ToBoolean(ReportTable.Rows[i]["IsSample"]);
}
if (j == 0)
{
if (IsSample)
{
if (ReportTable.Rows[i][1].ToString().Length > 4 && ReportTable.Rows[i][1].ToString().Substring(0, 4) == "отгр")
{
Ex.WriteCell(1, ReportTable.Rows[i][j].ToString(), RowIndex, j + 1, 11, true, Excel.Color.Red);
}
else
{
Ex.WriteCell(1, ReportTable.Rows[i][j].ToString(), RowIndex, j + 1, 11, false, Excel.Color.Red);
}
if (NeedPaint)
{
Ex.SetColor(1, RowIndex, j + 1, Excel.Color.Tan);
}
}
else
{
if (ReportTable.Rows[i][1].ToString().Length > 4 && ReportTable.Rows[i][1].ToString().Substring(0, 4) == "отгр")
{
Ex.WriteCell(1, ReportTable.Rows[i][j].ToString(), RowIndex, j + 1, 11, true);
}
else
{
Ex.WriteCell(1, ReportTable.Rows[i][j].ToString(), RowIndex, j + 1, 11, false);
}
if (NeedPaint)
{
Ex.SetColor(1, RowIndex, j + 1, Excel.Color.Tan);
}
}
continue;
}
if (j == 1 && ReportTable.Rows[i][j].ToString() == "декор")
{
if (IsSample)
{
Ex.WriteCell(1, ReportTable.Rows[i][j].ToString(), RowIndex, j + 1, 11, false, Excel.Color.Red);
}
else
{
Ex.WriteCell(1, ReportTable.Rows[i][j].ToString(), RowIndex, j + 1, 11, false);
}
continue;
}
if (j == 1 && ReportTable.Rows[i][j].ToString().Length > 4)
{
if (ReportTable.Rows[i][j].ToString().Substring(0, 4) == "отгр")
{
if (IsSample)
{
Ex.WriteCell(1, ReportTable.Rows[i][j].ToString(), RowIndex, j + 1, 11, false, Excel.Color.Red);
}
else
{
Ex.WriteCell(1, ReportTable.Rows[i][j].ToString(), RowIndex, j + 1, 11, false);
}
continue;
}
}
doc = ReportTable.Rows[i][j - 1].ToString();
doc = ReportTable.Rows[i][j].ToString();
if (j == 5 && ReportTable.Rows[i][j].ToString().Length > 0 && ReportTable.Rows[i][j - 1].ToString().Length == 0)
{
if (IsSample)
{
Ex.WriteCell(1, Convert.ToDecimal(ReportTable.Rows[i][j]), RowIndex, j + 1, 11, true, Excel.Color.Red);
}
else
{
Ex.WriteCell(1, Convert.ToDecimal(ReportTable.Rows[i][j]), RowIndex, j + 1, 11, true);
}
if (NeedPaint)
{
Ex.SetColor(1, RowIndex, j + 1, Excel.Color.Tan);
}
//RowIndex++;
continue;
}
Type t = ReportTable.Rows[i][j].GetType();
if (t.Name == "Decimal")
{
if (IsSample)
{
Ex.WriteCell(1, Convert.ToDecimal(ReportTable.Rows[i][j]), RowIndex, j + 1, 11, false, Excel.Color.Red);
}
else
{
Ex.WriteCell(1, Convert.ToDecimal(ReportTable.Rows[i][j]), RowIndex, j + 1, 11, false);
}
}
if (t.Name == "Int32")
{
if (IsSample)
{
Ex.WriteCell(1, ReportTable.Rows[i][j], RowIndex, j + 1, 11, false, Excel.Color.Red);
}
else
{
Ex.WriteCell(1, ReportTable.Rows[i][j], RowIndex, j + 1, 11, false);
}
}
if (t.Name == "String")
{
if (!NeedPaint)
{
if (decimal.TryParse(ReportTable.Rows[i][j].ToString(), out decimal d))
{
if (IsSample)
{
Ex.WriteCell(1, d, RowIndex, j + 1, 11, false, Excel.Color.Red);
}
else
{
Ex.WriteCell(1, d, RowIndex, j + 1, 11, false);
}
}
else
{
if (IsSample)
{
Ex.WriteCell(1, ReportTable.Rows[i][j], RowIndex, j + 1, 11, false, Excel.Color.Red);
}
else
{
Ex.WriteCell(1, ReportTable.Rows[i][j], RowIndex, j + 1, 11, false);
}
}
}
else
{
if (IsSample)
{
Ex.WriteCell(1, ReportTable.Rows[i][j], RowIndex, j + 1, 11, false, Excel.Color.Red);
}
else
{
Ex.WriteCell(1, ReportTable.Rows[i][j], RowIndex, j + 1, 11, false);
}
}
}
if (NeedPaint)
{
Ex.SetColor(1, RowIndex, j + 1, Excel.Color.Tan);
}
}
RowIndex++;
}
BottomRowIndex++;
Ex.AutoFit(1, 1, 1, ReportTable.Rows.Count, ReportTable.Columns.Count);
Ex.SetColumnWidth(1, 3, 9);
Ex.SetColumnWidth(1, 4, 9);
Ex.SetColumnWidth(1, 5, 9);
Ex.SetColumnWidth(1, 6, 9);
//Ex.SetRowHeight(1, 1, 34);
//for (int i = 1; i <= ReportTable.Columns.Count; i++)
//{
// Ex.SetHorisontalAlignment(1, TopRowIndex, i, Excel.AlignHorizontal.xlCenter);
// Ex.SetVerticalAlignment(1, TopRowIndex, i, Excel.AlignVertical.xlCenter);
// Ex.SetBorderStyle(1, TopRowIndex, i, true, true, true, true, Excel.LineStyle.xlContinuous, Excel.BorderWeight.xlMedium);
//}
//for (int i = 1; i <= ReportTable.Columns.Count; i++)
//{
// Ex.SetBorderStyle(1, TopRowIndex, i, false, false, true, false, Excel.LineStyle.xlContinuous, Excel.BorderWeight.xlMedium);
//}
//for (int i = 1; i <= ReportTable.Columns.Count; i++)
//{
// Ex.SetBorderStyle(1, BottomRowIndex, i, false, false, false, true, Excel.LineStyle.xlContinuous, Excel.BorderWeight.xlMedium);
//}
//for (int i = TopRowIndex; i <= BottomRowIndex; i++)
//{
// Ex.SetBorderStyle(1, i, 1, true, false, false, false, Excel.LineStyle.xlContinuous, Excel.BorderWeight.xlMedium);
//}
//for (int i = TopRowIndex; i <= BottomRowIndex; i++)
//{
// Ex.SetBorderStyle(1, i, ReportTable.Columns.Count, false, true, false, false, Excel.LineStyle.xlContinuous, Excel.BorderWeight.xlMedium);
//}
Ex.Visible = true;
}