public override void WriteReportToFile(DataSet reportData, string fileName, BaseReportSettings settings)
{
DataTableToExcel(reportData.Tables["Results"], fileName, settings.ReportCode);
ExcelHelper.Workbook(fileName, b => {
var ws = (MSExcel._Worksheet)b.Worksheets["rep" + settings.ReportCode.ToString()];
FormatExcelFile(ws, reportData.Tables["Results"], settings.ReportCaption, CountDownRows);
});
ProfileHelper.End();
}
public void WriteReportToFile(DataSet reportData, string fileName, BaseReportSettings settings)
{
var optimizationSettings = (OptimizationEfficiencySettings)settings;
_reportCode = optimizationSettings.ReportCode;
_reportCaption = optimizationSettings.ReportCaption;
_beginDate = optimizationSettings.BeginDate;
_endDate = optimizationSettings.EndDate;
_clientId = optimizationSettings.ClientId;
_concurents = optimizationSettings.Concurents;
_supplierName = optimizationSettings.SupplierName;
DataTableToExcel(reportData.Tables["Results"], fileName, settings.ReportCode);
FormatExcel(reportData, fileName);
}
public virtual void WriteReportToFile(DataSet reportData, string fileName, BaseReportSettings settings)
{
DataTableToExcel(reportData.Tables["Results"], fileName, settings.ReportCode);
ProfileHelper.Next("FormatExcel");
var file = fileName;
var result = reportData.Tables["Results"];
var reportId = settings.ReportCode;
var caption = settings.ReportCaption;
ExcelHelper.Workbook(file, b => {
var ws = (_Worksheet)b.Worksheets["rep" + reportId.ToString()];
FormatExcelFile(ws, result, caption, 0);
});
ProfileHelper.End();
}
public override void WriteReportToFile(DataSet reportData, string fileName, BaseReportSettings reportSettings)
{
var settings = (CostDynamicSettings)reportSettings;
var results = reportData.Tables["Results"];
results.Columns.Remove("Id");
DataTableToExcel(reportData.Tables["Results"], fileName, settings.ReportCode);
ExcelHelper.Workbook(fileName, b => {
var sheet = ExcelHelper.GetSheet(b, settings.ReportCode);
sheet.Name = reportSettings.ListName;
sheet.Activate();
var row = 0;
foreach (var filter in settings.Filters)
{
ExcelHelper.Header(sheet, row, 10, filter);
row++;
}
((Range)sheet.Cells[row + 1, 1]).Select();
var sheetRow = ((Range)sheet.Application.Selection).EntireRow;
sheetRow.Insert(XlInsertShiftDirection.xlShiftDown, Type.Missing);
((Range)sheet.Cells[row + 1, 2]).Value2 = settings.Date.ToShortDateString();
for (var i = 0; i < settings.Dates.Count; i++)
{
ExcelHelper.Merge(sheet, row, (i + 1) * 2, 2, settings.Dates[i].Label);
}
row++;
MakeHeder(row, sheet, 45);
row++;
ExcelHelper.FormatHeader(sheet, row, results);
MakeHeder(row, sheet);
var tableBegin = settings.Filters.Count + 1;
var tableHeaderSize = 2;
var count = results.Rows.Count;
var columnCount = results.Columns.Count;
var tableEnd = tableBegin + count + 1;
var range = sheet.get_Range(sheet.Cells[tableBegin, 1], sheet.Cells[tableEnd, columnCount]);
range.Borders.Weight = XlBorderWeight.xlThin;
sheet.get_Range(sheet.Cells[tableBegin + tableHeaderSize, 2], sheet.Cells[tableEnd, columnCount]).NumberFormat = "0.00%";
});
}
public override void WriteReportToFile(DataSet reportData, string fileName, BaseReportSettings settings)
{
var result = reportData.Tables["Results"];
var ppz = result.NewRow();
ppz[0] = "Отчет сформирован: " + DateTime.Now;
result.Rows.InsertAt(ppz, 0);
var reportParameters = new List <object>();
foreach (var reportParam in ReportParams)
{
var typeReportParam = reportParam.Value.GetType();
if (typeReportParam.IsGenericType)
{
if (!ParamNOVisualisation.Contains(reportParam.Key))
{
var itemList = (List <ulong>)reportParam.Value;
var namesList = (AssiciateReportParams[reportParam.Key](itemList, connection));
namesList.Sort();
var itemString = string.Join(" ,", namesList.ToArray());
if (itemString.Length > 2048)
{
itemString = itemString.Substring(0, 2047);
}
reportParameters.Add(GetDescription(reportParam.Key) + ": " + itemString);
}
}
if (typeReportParam == typeof(bool))
{
if (!ParamNOVisualisation.Contains(reportParam.Key))
{
var yesNo = (bool)reportParam.Value ? ": Да" : ": Нет";
reportParameters.Add(GetDescription(reportParam.Key) + yesNo);
}
}
if (typeReportParam == typeof(Int32))
{
if (!ParamNOVisualisation.Contains(reportParam.Key))
{
var value = Convert.ToUInt32(reportParam.Value);
if (AssiciateReportParams.ContainsKey(reportParam.Key))
{
var tempList = new List <ulong> {
value
};
var namesList = (AssiciateReportParams[reportParam.Key](tempList, connection));
if (namesList.Count > 0)
{
reportParameters.Add(GetDescription(reportParam.Key) + ": " + namesList[0]);
}
}
else
{
reportParameters.Add(GetDescription(reportParam.Key) + ": " + value);
}
}
}
}
var countDownRows = reportParameters.Count + 5;
var position = 1;
foreach (var reportParameter in reportParameters)
{
ppz = result.NewRow();
ppz[0] = reportParameter;
result.Rows.InsertAt(ppz, position);
position++;
}
var delemitRow = result.NewRow();
result.Rows.InsertAt(delemitRow, position);
delemitRow = result.NewRow();
result.Rows.InsertAt(delemitRow, position++);
DataTableToExcel(result, fileName, settings.ReportCode);
ExcelHelper.Workbook(fileName, b => {
var ws = (MSExcel._Worksheet)b.Worksheets["rep" + settings.ReportCode.ToString()];
ws.Name = _reportCaption.Substring(0, (_reportCaption.Length < MaxListName) ? _reportCaption.Length : MaxListName);
ws.Activate();
if (countDownRows > 0)
{
for (int j = 1; j < countDownRows - 1; j++)
{
for (int i = 0; i < countDownRows - 3; i++)
{
ws.Cells[1 + i, j] = ws.Cells[2 + i, j];
}
ws.Cells[countDownRows - 2, j] = "";
ws.get_Range("A" + j.ToString(), "Z" + j.ToString()).Merge();
}
}
if (countDownRows == 0)
{
countDownRows = 2;
}
for (int i = 4; i < 20; i++)
{
ws.Cells[1, i] = "";
}
for (int i = 0; i < result.Columns.Count; i++)
{
ws.Cells[countDownRows - 1, i + 1] = "";
ws.Cells[countDownRows - 1, i + 1] = result.Columns[i].Caption;
if (countDownRows != 2)
{
ws.Cells[1, 4] = "";
}
if (result.Columns[i].ExtendedProperties.ContainsKey("Width"))
{
((MSExcel.Range)ws.Columns[i + 1, Type.Missing]).ColumnWidth = ((int?)result.Columns[i].ExtendedProperties["Width"]).Value;
}
else
{
((MSExcel.Range)ws.Columns[i + 1, Type.Missing]).AutoFit();
}
if (result.Columns[i].ExtendedProperties.ContainsKey("Color"))
{
ws.get_Range(ws.Cells[countDownRows, i + 1], ws.Cells[result.Rows.Count + 1, i + 1]).Interior.Color = System.Drawing.ColorTranslator.ToOle((System.Drawing.Color)result.Columns[i].ExtendedProperties["Color"]);
}
}
//рисуем границы на всю таблицу
ws.get_Range(ws.Cells[countDownRows - 1, 1], ws.Cells[result.Rows.Count + 1, result.Columns.Count]).Borders.Weight = MSExcel.XlBorderWeight.xlThin;
//Устанавливаем шрифт листа
ws.Rows.Font.Size = 8;
ws.Rows.Font.Name = "Arial Narrow";
ws.Activate();
//Устанавливаем АвтоФильтр на все колонки
ws.Range[ws.Cells[countDownRows - 1, 1], ws.Cells[result.Rows.Count + 1, result.Columns.Count]].Select();
((MSExcel.Range)ws.Application.Selection).AutoFilter(1, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, System.Reflection.Missing.Value, true);
});
ProfileHelper.End();
}
public void WriteReportToFile(DataSet reportData, string fileName, BaseReportSettings settings)
{
DataTableToExcel(reportData.Tables["Results"], fileName, settings.ReportCode);
FormatExcel(reportData, fileName, settings as PharmacyMixedSettings);
}
public void WriteReportToFile(DataSet data, string file, BaseReportSettings settings)
{
var workbook = new Workbook();
foreach (var row in data.Tables["prices"].Rows.Cast <DataRow>())
{
var offers = data.Tables[row["PriceCode"].ToString()];
if (offers == null)
{
continue;
}
var name = row["ShortName"] + " " + row["PriceName"];
if (name.Length > 26)
{
name = name.Substring(0, 26);
}
var header = new CellStyle {
Warp = true,
Font = new Font("Arial", 11)
{
Bold = true
},
HorizontalAlignment = HorizontalAlignment.Center
};
var body = new CellStyle {
Borders = Borders.Box(BorderStyle.Thin)
};
var sheet = new Worksheet(name);
sheet.Cells[0, 0] = new Cell("Код")
{
Style = header
};
sheet.Cells.ColumnWidth[0] = 11 * 255;
sheet.Cells[0, 1] = new Cell("Код изготовителя")
{
Style = header
};
sheet.Cells.ColumnWidth[1] = 11 * 255;
sheet.Cells[0, 2] = new Cell("Наименование")
{
Style = header
};
sheet.Cells.ColumnWidth[2] = 30 * 255;
sheet.Cells[0, 3] = new Cell("Изготовитель")
{
Style = header
};
sheet.Cells.ColumnWidth[3] = 25 * 255;
sheet.Cells[0, 4] = new Cell("Цена")
{
Style = header
};
sheet.Cells.ColumnWidth[4] = (ushort)(15.5 * 255);
sheet.Cells[0, 5] = new Cell("Остаток")
{
Style = header
};
sheet.Cells.ColumnWidth[5] = 17 * 255;
sheet.Cells[0, 6] = new Cell("Срок годности")
{
Style = header
};
sheet.Cells.ColumnWidth[6] = 10 * 255;
sheet.Cells[0, 7] = new Cell("Примечание")
{
Style = header
};
sheet.Cells.ColumnWidth[7] = 20 * 255;
sheet.Cells[0, 8] = new Cell("ЖНВЛС")
{
Style = header
};
sheet.Cells.ColumnWidth[0] = 11 * 255;
sheet.Cells[0, 9] = new Cell("Лек. ассортимент")
{
Style = header
};
sheet.Cells.ColumnWidth[0] = 15 * 255;
var i = 1;
foreach (var offer in offers.Rows.Cast <DataRow>())
{
sheet.Cells[i, 0] = new Cell(offer["Code"])
{
Style = body
};
sheet.Cells[i, 1] = new Cell(offer["CodeCr"])
{
Style = body
};
sheet.Cells[i, 2] = new Cell(offer["Product"])
{
Style = body
};
sheet.Cells[i, 3] = new Cell(offer["Producer"])
{
Style = body
};
sheet.Cells[i, 4] = new Cell(offer["Cost"])
{
Style = body
};
sheet.Cells[i, 5] = new Cell(offer["Quantity"])
{
Style = body
};
sheet.Cells[i, 6] = new Cell(offer["Period"])
{
Style = body
};
sheet.Cells[i, 7] = new Cell(offer["Note"])
{
Style = body
};
sheet.Cells[i, 8] = new Cell(offer["VitallyImportant"])
{
Style = body
};
sheet.Cells[i, 9] = new Cell(offer["Pharmacie"])
{
Style = body
};
i++;
}
workbook.Worksheets.Add(sheet);
}
workbook.Save(file);
}