/// <summary>
/// Loads a CSV file into a range starting from the top left cell.
/// </summary>
/// <param name="TextFile">The Textfile</param>
/// <param name="Format">Information how to load the text</param>
/// <param name="TableStyle">Create a table with this style</param>
/// <param name="FirstRowIsHeader">Use the first row as header</param>
/// <returns></returns>
public async Task <ExcelRangeBase> LoadFromTextAsync(FileInfo TextFile, ExcelTextFormat Format, TableStyles TableStyle, bool FirstRowIsHeader)
{
var fs = new FileStream(TextFile.FullName, FileMode.Open, FileAccess.Read);
var sr = new StreamReader(fs, Format.Encoding);
return(LoadFromText(await sr.ReadToEndAsync().ConfigureAwait(false), Format, TableStyle, FirstRowIsHeader));
}
/// <summary>
/// Loads a CSV file into a range starting from the top left cell.
/// </summary>
/// <param name="TextFile">The Textfile</param>
/// <param name="Format">Information how to load the text</param>
/// <param name="TableStyle">Create a table with this style</param>
/// <param name="FirstRowIsHeader">Use the first row as header</param>
/// <returns></returns>
public ExcelRangeBase LoadFromText(FileInfo TextFile, ExcelTextFormat Format, TableStyles TableStyle, bool FirstRowIsHeader)
{
if (TextFile.Exists == false)
{
throw (new ArgumentException($"File does not exist {TextFile.FullName}"));
}
return(LoadFromText(File.ReadAllText(TextFile.FullName, Format.Encoding), Format, TableStyle, FirstRowIsHeader));
}
/// <summary>
/// Loads a CSV text into a range starting from the top left cell.
/// </summary>
/// <param name="Text">The Text</param>
/// <param name="Format">Information how to load the text</param>
/// <param name="TableStyle">Create a table with this style</param>
/// <param name="FirstRowIsHeader">Use the first row as header</param>
/// <returns></returns>
public ExcelRangeBase LoadFromText(string Text, ExcelTextFormat Format, TableStyles TableStyle, bool FirstRowIsHeader)
{
var r = LoadFromText(Text, Format);
var tbl = _worksheet.Tables.Add(r, "");
tbl.ShowHeader = FirstRowIsHeader;
tbl.TableStyle = TableStyle;
return(r);
}
private string[] GetLines(string text, ExcelTextFormat Format)
{
if (Format.EOL == null || Format.EOL.Length == 0)
{
return new string[] { text }
}
;
var eol = Format.EOL;
var list = new List <string>();
var inTQ = false;
var prevLineStart = 0;
for (int i = 0; i < text.Length; i++)
{
if (text[i] == Format.TextQualifier)
{
inTQ = !inTQ;
}
else if (!inTQ)
{
if (IsEOL(text, i, eol))
{
var s = text.Substring(prevLineStart, i - prevLineStart);
if (eol == "\n" && s.EndsWith("\r"))
{
s = s.Substring(0, s.Length - 1); //If EOL char is lf and last chart cr then we remove the trailing cr.
}
if (eol == "\r" && s.StartsWith("\n"))
{
s = s.Substring(1); //If EOL char is cr and last chart lf then we remove the heading lf.
}
list.Add(s);
i += eol.Length - 1;
prevLineStart = i + 1;
}
}
}
if (inTQ)
{
throw (new ArgumentException(string.Format("Text delimiter is not closed in line : {0}", list.Count)));
}
if (prevLineStart >= Format.EOL.Length && IsEOL(text, prevLineStart - Format.EOL.Length, Format.EOL))
{
//list.Add(text.Substring(prevLineStart- Format.EOL.Length, Format.EOL.Length));
list.Add("");
}
else
{
list.Add(text.Substring(prevLineStart));
}
return(list.ToArray());
}
/// <summary>
/// Loads a CSV file into a range starting from the top left cell.
/// </summary>
/// <param name="TextFile">The Textfile</param>
/// <param name="Format">Information how to load the text</param>
/// <param name="TableStyle">Create a table with this style</param>
/// <param name="FirstRowIsHeader">Use the first row as header</param>
/// <returns></returns>
public async Task <ExcelRangeBase> LoadFromTextAsync(FileInfo TextFile, ExcelTextFormat Format, TableStyles TableStyle, bool FirstRowIsHeader)
{
if (TextFile.Exists == false)
{
throw (new ArgumentException($"File does not exist {TextFile.FullName}"));
}
var fs = new FileStream(TextFile.FullName, FileMode.Open, FileAccess.Read);
var sr = new StreamReader(fs, Format.Encoding);
return(LoadFromText(await sr.ReadToEndAsync().ConfigureAwait(false), Format, TableStyle, FirstRowIsHeader));
}
/// <summary>
/// Loads a CSV text into a range starting from the top left cell.
/// </summary>
/// <param name="Text">The Text</param>
/// <param name="Format">Information how to load the text</param>
/// <returns>The range containing the data</returns>
public ExcelRangeBase LoadFromText(string Text, ExcelTextFormat Format)
{
if (string.IsNullOrEmpty(Text))
{
var r = _worksheet.Cells[_fromRow, _fromCol];
r.Value = "";
return(r);
}
var parameters = new LoadFromTextParams
{
Format = Format
};
var func = new LoadFromText(this, Text, parameters);
return(func.Load());
}
private string[] GetLines(string text, ExcelTextFormat Format)
{
if (Format.EOL == null || Format.EOL.Length == 0)
{
return new string[] { text }
}
;
var eol = Format.EOL;
var list = new List <string>();
var inTQ = false;
var prevLineStart = 0;
for (int i = 0; i < text.Length; i++)
{
if (text[i] == Format.TextQualifier)
{
inTQ = !inTQ;
}
else if (!inTQ)
{
if (IsEOL(text, i, eol))
{
list.Add(text.Substring(prevLineStart, i - prevLineStart));
i += eol.Length - 1;
prevLineStart = i + 1;
}
}
}
if (inTQ)
{
throw (new ArgumentException(string.Format("Text delimiter is not closed in line : {0}", list.Count)));
}
if (prevLineStart >= Format.EOL.Length && IsEOL(text, prevLineStart - Format.EOL.Length, Format.EOL))
{
//list.Add(text.Substring(prevLineStart- Format.EOL.Length, Format.EOL.Length));
list.Add("");
}
else
{
list.Add(text.Substring(prevLineStart));
}
return(list.ToArray());
}
public void ConvertCsvToExcel_EEPlus()
{
using (TemporaryFile csvFile = new TemporaryFile(".csv"))
using (TemporaryFile xlsxFile = new TemporaryFile(".xlsx"))
{
StringBuilder content = new StringBuilder();
content.AppendLine("param1\tparam2\tstatus");
content.AppendLine("0.5\t10\tpassed");
content.AppendLine("10\t20\tfail");
File.AppendAllText(csvFile.FileName, content.ToString());
Console.WriteLine("created file {0}", csvFile.FileName);
var format = new ExcelTextFormat {Delimiter = '\t', EOL = "\r"};
// format.TextQualifier = '"';
FileInfo excelFileInfo = new FileInfo(xlsxFile.FileName);
using (ExcelPackage package = new ExcelPackage(excelFileInfo))
{
const string WORKSHEETS_NAME = "test";
const bool FIRST_ROW_IS_HEADER = false;
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(WORKSHEETS_NAME);
worksheet.Cells["A1"].LoadFromText(new FileInfo(csvFile.FileName), format);
//worksheet.Cells["A1"].LoadFromText(new FileInfo(csvFile.FileName), format, OfficeOpenXml.Table.TableStyles.None, FIRST_ROW_IS_HEADER);
for (int i = 0; i < 3; i++)
{
ExcelRange cell = worksheet.Cells[i+1, 2];
int cellValue;
if (Int32.TryParse(cell.Text, out cellValue) && cellValue>10)
{
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Green);
}
}
package.Save();
}
Console.WriteLine("created file {0}", xlsxFile.FileName);
}
}
private static void CreateExcelFile(string excelFile, string sheetName, string csvFile, string columnName)
{
File.Delete(excelFile);
FileInfo excelFileInfo = new FileInfo(excelFile);
using (ExcelPackage package = new ExcelPackage(excelFileInfo))
{
ExcelWorksheets workSheets = package.Workbook.Worksheets;
ExcelWorksheet dataWorkSheet = workSheets.Add(sheetName);
var format = new ExcelTextFormat { Delimiter = '\t', EOL = "\r" };
dataWorkSheet.Cells["A1"].LoadFromText(new FileInfo(csvFile), format);
int rowsCount = dataWorkSheet.Dimension.End.Row - 1;
ExcelColumn preColumn = dataWorkSheet.Column(2);
preColumn.Width = 2;
ExcelColumn postColumn = dataWorkSheet.Column(17);
postColumn.Width = 2;
for (int row = 0; row < rowsCount; row++)
{
SetColor(dataWorkSheet.Cells[row + 1, 2], EMPTY_COLUMN_COLOR);
SetColor(dataWorkSheet.Cells[row + 1, 17], EMPTY_COLUMN_COLOR);
}
ExcelWorksheet chartsWorksheet = workSheets.Add("Charts");
ExcelChart chart = chartsWorksheet.Drawings.AddChart("StdDev", eChartType.ColumnClustered);
chart.Title.Text = "StdDev";
chart.SetPosition(1, 0, 1, 0);
chart.SetSize(800, 300);
string yName = String.Format("'" + dataWorkSheet.Name + "'" + "!{0}2:{0}{1}", columnName, rowsCount);
var series = chart.Series.Add(yName, "");
series.Header = "StdDev / Configuration";
package.Save();
}
}
/// <summary>
/// Добавить лист с метриками
/// </summary>
/// <param name="sheetName">Имя создаваемого листа.</param>
/// <param name="inputStream">Поток, содержащий данные для содаваемого листа.</param>
/// <param name="delimiter">Разделитель значений в строках потока.</param>
/// <returns>Ссылка на созданный лист.</returns>
public ExcelWorksheet AddMetricWorksheet(string sheetName, StreamReader inputStream, string fromTime, string toTime, char delimiter = '\t')
{
var newSheet = _excelFile.Workbook.Worksheets.Add(sheetName);
newSheet.Cells.Style.Font.Size = 11;
newSheet.Cells.Style.Font.Name = "Calibri";
var format = new ExcelTextFormat();
format.Delimiter = delimiter;
format.Culture = new CultureInfo("ru-RU");
format.Culture.NumberFormat.NumberDecimalSeparator = ",";
int row = 1;
newSheet.Cells["A" + row].LoadFromText(inputStream.ReadLine(), format);
row++;
int next = 0;
if (sheetName.Contains("sys_info"))
{
while (!inputStream.EndOfStream)
{
string buff = inputStream.ReadLine();
if ((row % 3) == 0)
{
InfoList.Add(buff);
//MessageBox.Show(InfoList[next].ToString());
next++;
}
newSheet.Cells["A" + row].LoadFromText(buff, format);
row++;
}
}
else
{
newSheet.Column(1).StyleName = "MyTime";
while (!inputStream.EndOfStream)
{
newSheet.Cells["A" + row].LoadFromText(inputStream.ReadLine(), format);
if (!((row > 1) && (DateTime.Parse(newSheet.Cells["A" + row].Text) >= (DateTime.Parse(fromTime)) &&
(DateTime.Parse(newSheet.Cells["A" + row].Text) <= (DateTime.Parse(toTime))))))
{
newSheet.DeleteRow(row, 1);
}
else row++;
}
}
if (sheetName.Contains("disk"))
{
for (int i = 2; i < newSheet.Dimension.End.Row; i++)
{
DiskList.Add(newSheet.Cells["B" + i].Value);
}
DiskList = RemoveDuplicate(DiskList);
}
if (sheetName.Contains("cpu_a"))
{
for (int i = 2; i < newSheet.Dimension.End.Row; i++)
{
Cores.Add((newSheet.Cells["B" + i].Value).ToString());
// MessageBox.Show((newSheet.Cells["B" + i].Value).ToString());
}
Cores = RemoveDuplicate(Cores);
for (int i = 3; i < 7; i++)
{
CPUzagolov.Add((newSheet.Cells[1, i].Value).ToString());
//MessageBox.Show((newSheet.Cells[1, i].Value).ToString());
}
}
if (newSheet.Dimension != null)
{
newSheet.Cells[newSheet.Dimension.Address].AutoFitColumns();
}
return newSheet;
}
internal static void Convert(string CsvPath, string CsvPlayerPath, string ExcelPath, int FileLength, ManualResetEvent DoneEvent)
{
Console.Title = "A-Maze-ing simulator - Converting";
string SheetName = "Simulator Results";
string PlayerSheetName = "Player Results";
var format = new ExcelTextFormat();
format.Delimiter = ';';
format.EOL = "\r";
try {
using (ExcelPackage pack = new ExcelPackage(new FileInfo(ExcelPath))) {
using (ExcelWorksheet Sheet = pack.Workbook.Worksheets.Add(SheetName)) {
// Setting Column widths
Sheet.Column(1).Width = GetTrueColumnWidth(6.4);
Sheet.Column(2).Width = GetTrueColumnWidth(10.4);
Sheet.Column(3).Width = GetTrueColumnWidth(12);
Sheet.Column(4).Width = GetTrueColumnWidth(13);
Sheet.Column(5).Width = GetTrueColumnWidth(12);
Sheet.Column(6).Width = GetTrueColumnWidth(12);
Sheet.Column(7).Width = GetTrueColumnWidth(16.5);
Sheet.Column(8).Width = GetTrueColumnWidth(17);
// Merge cells [IMPORTANT] - Cells have to be merged before import or the file will be "corrupted / damage".
Merge(new[] { 8 }, new[] { 1, 2, 5, 8, 11, 18 }, Sheet);
Merge(new[] { 2, 2, 2, 2 }, new[] { 3, 4 }, Sheet);
Merge(new[] { 2, 2, 2, }, new[] { 6, 7 }, Sheet);
Merge(new[] { 2, 1, 1, 1, 1, 2 }, new[] { 9, 10 }, Sheet);
using (StreamReader reader = new StreamReader(CsvPath)) {
int Row = 1;
string Line;
while ((Line = reader.ReadLine()) != null) {
Sheet.Cells[Row, 1].LoadFromText(Line, format);
float perc = ((float)Row / (FileLength * 2)) * 100;
string Title = string.Format("A-Maze-ing simulator - Converting - {0:#}%", perc);
if (Console.Title != Title)
Console.Title = Title;
Row++;
}
}
Sheet.DeleteRow(1);
CreateExcelTemplate(Sheet, PlayerSheetName);
using (ExcelWorksheet PlayerSheet = pack.Workbook.Worksheets.Add(PlayerSheetName)) {
using (StreamReader reader = new StreamReader(CsvPlayerPath)) {
int Row = 1;
string Line;
while ((Line = reader.ReadLine()) != null) {
PlayerSheet.Cells[Row, 1].LoadFromText(Line, format);
float perc = ((float)(Row + FileLength) / (FileLength * 2)) * 100;
string Title = string.Format("A-Maze-ing simulator - Converting - {0:#}%", perc);
if (Console.Title != Title)
Console.Title = Title;
Row++;
}
}
PlayerSheet.DeleteRow(1);
pack.Save();
}
}
}
}
catch {
File.SetAttributes(CsvPath, FileAttributes.Normal);
File.SetAttributes(CsvPlayerPath, FileAttributes.Normal);
throw;
}
File.SetAttributes(CsvPath, FileAttributes.Normal);
File.SetAttributes(CsvPlayerPath, FileAttributes.Normal);
Console.Title = "A-Maze-ing simulator";
DoneEvent.Set();
HandleInput.PrintColor("Excel converter finished styling the CSV Sheet.", ConsoleColor.Green);
}
/// <summary>
/// Loads a CSV file into a range starting from the top left cell.
/// </summary>
/// <param name="TextFile">The Textfile</param>
/// <param name="Format">Information how to load the text</param>
/// <returns></returns>
public ExcelRangeBase LoadFromText(FileInfo TextFile, ExcelTextFormat Format)
{
return(LoadFromText(File.ReadAllText(TextFile.FullName, Format.Encoding), Format));
}
/// <summary>
/// Loads a CSV file into a range starting from the top left cell.
/// </summary>
/// <param name="TextFile">The Textfile</param>
/// <param name="Format">Information how to load the text</param>
/// <param name="TableStyle">Create a table with this style</param>
/// <param name="FirstRowIsHeader">Use the first row as header</param>
/// <returns></returns>
public ExcelRangeBase LoadFromText(FileInfo TextFile, ExcelTextFormat Format, TableStyles TableStyle, bool FirstRowIsHeader)
{
return(LoadFromText(File.ReadAllText(TextFile.FullName, Format.Encoding), Format, TableStyle, FirstRowIsHeader));
}
public ExcelWorksheet AddCPUTable(string sheetName, int minutes, int interval, string serv,char delimiter = '\t')
{
_excelFile.Workbook.Worksheets.Add(sheetName);
var newSheet = _excelFile.Workbook.Worksheets[_excelFile.Workbook.Worksheets.Count];
var format = new ExcelTextFormat();
format.Delimiter = delimiter;
format.Culture = new CultureInfo("ru-RU");
format.Culture.NumberFormat.NumberDecimalSeparator = ",";
newSheet.Cells["A:R"].Style.Numberformat.Format = "General";
newSheet.Cells["A2"].Value = "Time";
newSheet.Cells["B2"].Value = "Absol. time";
newSheet.Column(1).StyleName = "MyTime";
newSheet.Column(2).StyleName = "AbsTime";
newSheet.Cells.Style.Font.Name = "Calibri";
newSheet.Cells.Style.Font.Size = 8;
newSheet.DefaultColWidth = 50;
///fdfdfdf
int column;
int row;
int indexCPU = 0;
int indexCPUA = 0;
try
{
if (sheetName == "CPU_All")
{
for (int page = 1; page < _excelFile.Workbook.Worksheets.Count; page++)
if (_excelFile.Workbook.Worksheets[page].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[page].Name.Contains("cpu_"))
{
row = 0;
indexCPUA = page;
int n = Cores.Count + 2;
for (column = 2; column < n; column++)
{
newSheet.Cells[2, column + 1].Value = "Core #" + Convert.ToString(column - 2);
for (row = 3; row < 5 + interval; row++)
{
newSheet.Cells[row, column + 1].Formula = _excelFile.Workbook.Worksheets[indexCPUA].Cells[row, column].FullAddress;
}
}
using (ExcelRange col = newSheet.Cells[1, 1, newSheet.Dimension.End.Row, n])
{
var border = col.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
}
}
}
else if (sheetName == "CPU")
{
for (int page = 1; page < _excelFile.Workbook.Worksheets.Count; page++)
{
column = 6;
row = 0;
if (_excelFile.Workbook.Worksheets[page].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[page].Name.Contains("cpu2"))
{
indexCPU = page;
//string header = dictExcel.search(_excelFile.Workbook.Worksheets[index].Name);
for (column = 3; column < 7; column++)
for (row = 2; row < 5 + interval; row++)
{
newSheet.Cells[row, column].Formula = _excelFile.Workbook.Worksheets[indexCPU].Cells[row, column - 1].FullAddress;
}
for (int i = 0; i < 4; i++)
{
newSheet.Cells[2, i + 3].Value = CPUzagolov[i];
}
newSheet.Cells[3, column + 1].Formula = newSheet.Cells["F"+newSheet.Dimension.End.Row].FullAddress;// String.Format("AVERAGE({0}:{1})", _excelFile.Workbook.Worksheets[indexCPU].Cells[3 + minutes, 3].Address, _excelFile.Workbook.Worksheets[indexCPU].Cells[row - 1, 3].Address);
//column++;
using (ExcelRange col = newSheet.Cells["H2:H3"])
{
var border = col.Style.Border;
newSheet.Cells["H2"].Value = "AVERAGE";
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
}
using (ExcelRange col = newSheet.Cells["A1:F" + newSheet.Dimension.End.Row])
{
var border = col.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
}
newSheet.Cells["F2"].Value = serv;
}
}
}
if (indexCPUA == 0) //отрезок определяющий "Absol. time"
{
for (row = 3; row < newSheet.Dimension.End.Row + 1; row++)
{
newSheet.Cells[row, 1].Formula = _excelFile.Workbook.Worksheets[indexCPU].Cells[row, 1].FullAddress;
if (row != newSheet.Dimension.End.Row)
newSheet.Cells[row, 2].Formula = newSheet.Cells[row, 1].Address + "-" + newSheet.Cells[3, 1].Address;
}
}
else
{
for (row = 3; row < newSheet.Dimension.End.Row + 1; row++)
{
newSheet.Cells[row, 1].Formula = _excelFile.Workbook.Worksheets[indexCPUA].Cells[row, 1].FullAddress;
if (row != newSheet.Dimension.End.Row)
newSheet.Cells[row, 2].Formula = newSheet.Cells[row, 1].Address + "-" + newSheet.Cells[3, 1].Address;
}
}
if (indexCPUA == 0) //отрезок кода рисующий график
{
var loadChart = newSheet.Drawings.AddChart("CPU-Load", eChartType.Line);
loadChart.Title.Text = serv + " CPU Load";
loadChart.Title.Font.Size = 12;
loadChart.SetPosition(200, 300);
loadChart.Legend.Position = eLegendPosition.Bottom;
loadChart.Legend.Add();
loadChart.SetSize(1000, 600);
loadChart.YAxis.Format = "#,##0";
loadChart.YAxis.MaxValue = 100;
loadChart.YAxis.MinValue = 0;
loadChart.YAxis.Title.Text = "Средняя нагрузка на CPU, %";
loadChart.XAxis.Title.Text = "Время теста, мин";
loadChart.YAxis.Title.Font.Bold = true;
loadChart.YAxis.Title.Font.Size = 10;
loadChart.XAxis.Title.Font.Bold = true;
loadChart.XAxis.Title.Font.Size = 10;
for (int i = 3; i < 4; i++)
{
var serie = loadChart.Series.Add(ExcelRange.GetAddress(3, i + 3, newSheet.Dimension.End.Row - 1, i + 3), ExcelRange.GetAddress(3, 2, newSheet.Dimension.End.Row - 1, 2));
serie.Header = newSheet.Cells[2, i + 3].Text;
}
var OstChart = newSheet.Drawings.AddChart("CPU-use", eChartType.AreaStacked);
OstChart.Title.Text = serv + " CPU usage";
OstChart.Title.Font.Size = 12;
OstChart.SetPosition(1000, 300);
OstChart.Legend.Position = eLegendPosition.Bottom;
OstChart.Legend.Add();
OstChart.SetSize(1000, 600);
OstChart.YAxis.Format = "#,##0";
OstChart.YAxis.MinValue = 0;
OstChart.YAxis.Title.Text = "Средняя нагрузка на CPU, %";
OstChart.XAxis.Title.Text = "Время теста, мин";
OstChart.YAxis.Title.Font.Bold = true;
OstChart.YAxis.Title.Font.Size = 10;
OstChart.XAxis.Title.Font.Bold = true;
OstChart.XAxis.Title.Font.Size = 10;
for (int i = 0; i < 3; i++)
{
var serie = OstChart.Series.Add(ExcelRange.GetAddress(3, i + 3, newSheet.Dimension.End.Row - 1, i + 3), ExcelRange.GetAddress(3, 2, newSheet.Dimension.End.Row - 1, 2));
serie.Header = newSheet.Cells[2, i + 3].Text;
}
}
else
{
var chart = newSheet.Drawings.AddChart("CPU_ALL", eChartType.AreaStacked);
chart.Title.Text = serv + " CPU usage cores";
chart.Title.Font.Size = 12;
chart.SetPosition(200, 300);
chart.Legend.Position = eLegendPosition.Bottom;
chart.Legend.Add();
chart.SetSize(1000, 600);
chart.YAxis.Format = "#,##0";
chart.YAxis.MaxValue = 100 * Cores.Count;
chart.YAxis.Title.Text = "Средняя нагрузка на CPU, %";
chart.XAxis.Title.Text = "Время теста, мин";
chart.YAxis.Title.Font.Bold = true;
chart.YAxis.Title.Font.Size = 10;
chart.XAxis.Title.Font.Bold = true;
chart.XAxis.Title.Font.Size = 10;
for (int i = 0; i < Cores.Count; i++)
{
var serie = chart.Series.Add(ExcelRange.GetAddress(3, i + 3, newSheet.Dimension.End.Row - 1, i + 3), ExcelRange.GetAddress(3, 2, newSheet.Dimension.End.Row - 1, 2));
serie.Header = Convert.ToString(newSheet.Cells[2, i + 3].Value);
}
}
}
catch (Exception ex)
{
Popup.ShowException(ex);
}
return newSheet;
}
public ExcelWorksheet AddPivotTables(string sheetName,ExcelRangeBase source, char delimiter = '\t')
{
///ProgressForm FormProgr = new ProgressForm(); FormProgr.Stat = sheetName;
var newSheet = _excelFile.Workbook.Worksheets.Add(sheetName);
newSheet.Cells.Style.Font.Size = 11;
newSheet.Cells.Style.Font.Name = "Calibri";
var format = new ExcelTextFormat();
format.Delimiter = delimiter;
format.Culture = new CultureInfo("ru-RU");
format.Culture.NumberFormat.NumberDecimalSeparator = ",";
var pivotTable = newSheet.PivotTables.Add(newSheet.Cells["A1"], source, "Pivot "+sheetName);
pivotTable.RowFields.Add(pivotTable.Fields[0]);
if (_excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("cpu2"))
{
var usrField = pivotTable.DataFields.Add(pivotTable.Fields[1]);
usrField.Name = source.Worksheet.Cells[1, 2].Text;
usrField.Function = DataFieldFunctions.Average;
var sysField = pivotTable.DataFields.Add(pivotTable.Fields[2]);
sysField.Name = source.Worksheet.Cells[1, 3].Text;
sysField.Function = DataFieldFunctions.Average;
var wioField = pivotTable.DataFields.Add(pivotTable.Fields[3]);
wioField.Name = source.Worksheet.Cells[1, 4].Text;
wioField.Function = DataFieldFunctions.Average;
var loadField = pivotTable.DataFields.Add(pivotTable.Fields[4]);
loadField.Name = source.Worksheet.Cells[1, 5].Text;
loadField.Function = DataFieldFunctions.Average;
}
if (_excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("mem"))
{
var dataField = pivotTable.DataFields.Add(pivotTable.Fields[3]);
dataField.Name = source.Worksheet.Cells[1, 4].Text;
dataField.Function = DataFieldFunctions.Average;
}
if (_excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("disk_io"))
{
var dataName = pivotTable.ColumnFields.Add(pivotTable.Fields[1]);
dataName.Name = source.Worksheet.Cells[1, 2].Text;
/*/pivotTable.Fields[1].Sort = eSortType.Ascending;
if (_excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("db"))
{*/
var dataField2 = pivotTable.DataFields.Add(pivotTable.Fields[6]);
dataField2.Name = source.Worksheet.Cells[1, 7].Text;
dataField2.Function = DataFieldFunctions.Average;
/*}
else
{
var dataField2 = pivotTable.DataFields.Add(pivotTable.Fields[4]);
dataField2.Name = source.Worksheet.Cells[1, 5].Text;
dataField2.Function = DataFieldFunctions.Average;
}*/
}
if (_excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("sar-disk"))
{
var dataName = pivotTable.ColumnFields.Add(pivotTable.Fields[1]);
dataName.Name = source.Worksheet.Cells[1, 2].Text;
//pivotTable.Fields[1].Sort = eSortType.Ascending;
var dataField = pivotTable.DataFields.Add(pivotTable.Fields[2]);
dataField.Name = source.Worksheet.Cells[1, 3].Text;
dataField.Function = DataFieldFunctions.Average;
}
if (_excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("cpu_"))
{
var dataName = pivotTable.ColumnFields.Add(pivotTable.Fields[1]);
dataName.Name = source.Worksheet.Cells[1, 2].Text;
pivotTable.Fields[1].Sort = eSortType.Ascending;
var dataField = pivotTable.DataFields.Add(pivotTable.Fields[5]);
dataField.Name = source.Worksheet.Cells[1, 6].Text;
dataField.Function = DataFieldFunctions.Average;
}
pivotTable.DataOnRows = false;
return newSheet;
}
private static void LoadFile1(ExcelPackage package)
{
//Create the Worksheet
var sheet = package.Workbook.Worksheets.Add("Csv1");
//Create the format object to describe the text file
var format = new ExcelTextFormat();
format.TextQualifier = '"';
format.SkipLinesBeginning = 2;
format.SkipLinesEnd = 1;
//Now read the file into the sheet. Start from cell A1. Create a table with style 27. First row contains the header.
Console.WriteLine("Load the text file...");
var range = sheet.Cells["A1"].LoadFromText(new FileInfo("..\\..\\csv\\Sample9-1.txt"), format, TableStyles.Medium27, true);
Console.WriteLine("Format the table...");
//Tables don't support custom styling at this stage(you can of course format the cells), but we can create a Namedstyle for a column...
var dateStyle = package.Workbook.Styles.CreateNamedStyle("TableDate");
dateStyle.Style.Numberformat.Format = "YYYY-MM";
var numStyle = package.Workbook.Styles.CreateNamedStyle("TableNumber");
numStyle.Style.Numberformat.Format = "#,##0.0";
//Now format the table...
var tbl = sheet.Tables[0];
tbl.ShowTotal = true;
tbl.Columns[0].TotalsRowLabel = "Total";
tbl.Columns[0].DataCellStyleName = "TableDate";
tbl.Columns[1].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[1].DataCellStyleName = "TableNumber";
tbl.Columns[2].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[2].DataCellStyleName = "TableNumber";
tbl.Columns[3].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[3].DataCellStyleName = "TableNumber";
tbl.Columns[4].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[4].DataCellStyleName = "TableNumber";
tbl.Columns[5].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[5].DataCellStyleName = "TableNumber";
tbl.Columns[6].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[6].DataCellStyleName = "TableNumber";
//Set the width...
sheet.Column(1).Width = 10;
sheet.Column(2).Width = 12;
sheet.Column(3).Width = 12;
sheet.Column(4).Width = 12;
sheet.Column(5).Width = 16;
sheet.Column(6).Width = 16;
sheet.Column(7).Width = 12;
Console.WriteLine("Create the chart...");
//Now add a stacked areachart...
var chart = sheet.Drawings.AddChart("chart1", eChartType.AreaStacked);
chart.SetPosition(0, 630);
chart.SetSize(800, 600);
//Create one series for each column...
for (int col = 1; col < 7; col++)
{
var ser = chart.Series.Add(range.Offset(1, col, range.End.Row - 1, 1), range.Offset(1, 0, range.End.Row - 1, 1));
ser.HeaderAddress = range.Offset(0, col, 1, 1);
}
//Set the style to 27.
chart.Style = eChartStyle.Style27;
sheet.View.ShowGridLines = false;
}
/// <summary>
/// Loads a CSV text into a range starting from the top left cell.
/// </summary>
/// <param name="Text">The Text</param>
/// <param name="Format">Information how to load the text</param>
/// <returns>The range containing the data</returns>
public ExcelRangeBase LoadFromText(string Text, ExcelTextFormat Format)
{
if (string.IsNullOrEmpty(Text))
{
var r = _worksheet.Cells[_fromRow, _fromCol];
r.Value = "";
return(r);
}
if (Format == null)
{
Format = new ExcelTextFormat();
}
string[] lines;
if (Format.TextQualifier == 0)
{
lines = Regex.Split(Text, Format.EOL);
}
else
{
lines = GetLines(Text, Format);
}
int row = 0;
int col = 0;
int maxCol = col;
int lineNo = 1;
//var values = new List<object>[lines.Length];
foreach (string line in lines)
{
var items = new List <object>();
//values[row] = items;
if (lineNo > Format.SkipLinesBeginning && lineNo <= lines.Length - Format.SkipLinesEnd)
{
col = 0;
string v = "";
bool isText = false, isQualifier = false;
int QCount = 0;
int lineQCount = 0;
foreach (char c in line)
{
if (Format.TextQualifier != 0 && c == Format.TextQualifier)
{
if (!isText && v != "")
{
throw (new Exception(string.Format("Invalid Text Qualifier in line : {0}", line)));
}
isQualifier = !isQualifier;
QCount += 1;
lineQCount++;
isText = true;
}
else
{
if (QCount > 1 && !string.IsNullOrEmpty(v))
{
v += new string(Format.TextQualifier, QCount / 2);
}
else if (QCount > 2 && string.IsNullOrEmpty(v))
{
v += new string(Format.TextQualifier, (QCount - 1) / 2);
}
if (isQualifier)
{
v += c;
}
else
{
if (c == Format.Delimiter)
{
items.Add(ConvertData(Format, v, col, isText));
v = "";
isText = false;
col++;
}
else
{
if (QCount % 2 == 1)
{
throw (new Exception(string.Format("Text delimiter is not closed in line : {0}", line)));
}
v += c;
}
}
QCount = 0;
}
}
if (QCount > 1 && (v != "" && QCount == 2))
{
v += new string(Format.TextQualifier, QCount / 2);
}
if (lineQCount % 2 == 1)
{
throw (new Exception(string.Format("Text delimiter is not closed in line : {0}", line)));
}
items.Add(ConvertData(Format, v, col, isText));
_worksheet._values.SetValueRow_Value(_fromRow + row, _fromCol, items);
if (col > maxCol)
{
maxCol = col;
}
row++;
}
lineNo++;
}
return(_worksheet.Cells[_fromRow, _fromCol, _fromRow + row - 1, _fromCol + maxCol]);
}
private static void LoadFile2(ExcelPackage package)
{
//Create the Worksheet
var sheet = package.Workbook.Worksheets.Add("Csv2");
//Create the format object to describe the text file
var format = new ExcelTextFormat();
format.Delimiter='\t'; //Tab
format.SkipLinesBeginning = 1;
CultureInfo ci = new CultureInfo("sv-SE"); //Use your choice of Culture
ci.NumberFormat.NumberDecimalSeparator = ","; //Decimal is comma
format.Culture = ci;
//Now read the file into the sheet.
Console.WriteLine("Load the text file...");
var range = sheet.Cells["A1"].LoadFromText(new FileInfo("..\\..\\csv\\Sample9-2.txt"), format);
//Add a formula
range.Offset(1, range.End.Column, range.End.Row - range.Start.Row, 1).FormulaR1C1 = "RC[-1]-RC[-2]";
//Add a table...
var tbl = sheet.Tables.Add(range.Offset(0,0,range.End.Row-range.Start.Row+1, range.End.Column-range.Start.Column+2),"Table");
tbl.ShowTotal = true;
tbl.Columns[0].TotalsRowLabel = "Total";
tbl.Columns[1].TotalsRowFormula = "COUNT(3,[Product])"; //Add a custom formula
tbl.Columns[2].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[3].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[4].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[5].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[5].Name = "Profit";
tbl.TableStyle = TableStyles.Medium10;
sheet.Column(1).Width = 10;
sheet.Column(2).Width = 12;
sheet.Column(3).Width = 16;
sheet.Column(4).Width = 16;
sheet.Column(5).Width = 12;
//Add a chart with two charttypes (Column and Line) and a secondary axis...
var chart = sheet.Drawings.AddChart("chart2", eChartType.ColumnStacked);
chart.SetPosition(0, 540);
chart.SetSize(800, 600);
var serie1= chart.Series.Add(range.Offset(1, 3, range.End.Row - 1, 1), range.Offset(1, 1, range.End.Row - 1, 1));
serie1.Header = "Purchase Price";
var serie2 = chart.Series.Add(range.Offset(1, 5, range.End.Row - 1, 1), range.Offset(1, 1, range.End.Row - 1, 1));
serie2.Header = "Profit";
//Add a Line series
var chartType2 = chart.PlotArea.ChartTypes.Add(eChartType.LineStacked);
chartType2.UseSecondaryAxis = true;
var serie3 = chartType2.Series.Add(range.Offset(1, 2, range.End.Row - 1, 1), range.Offset(1, 0, range.End.Row - 1, 1));
serie3.Header = "Items in stock";
//By default the secondary XAxis is not visible, but we want to show it...
chartType2.XAxis.Deleted = false;
chartType2.XAxis.TickLabelPosition = eTickLabelPosition.High;
//Set the max value for the Y axis...
chartType2.YAxis.MaxValue = 50;
chart.Style = eChartStyle.Style26;
sheet.View.ShowGridLines = false;
}
public ExcelWorksheet AddMainSheet(string sheetName, int effort,string fromTime, string toTime, string serv, char delimiter = '\t')
{
int[] indicators = { 1050, 106, 300, 46, 176, 100, 90, 90, 10, 10, 300, 0, 138 };
//progr.Stat = sheetName;
var newSheet = _excelFile.Workbook.Worksheets.Add(sheetName);
newSheet.Cells.Style.Font.Size = 11;
newSheet.Cells.Style.Font.Name = "Calibri";
var format = new ExcelTextFormat();
format.Delimiter = delimiter;
format.Culture = new CultureInfo("ru-RU");
format.Culture.NumberFormat.NumberDecimalSeparator = ",";
using (ExcelRange col = newSheet.Cells["A1:B4,D6:D8,D10:D13,D15:D19,D21"])
{
var border = col.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//col.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
}
//MessageBox.Show(InfoList.Count.ToString());
int cout = 5;
if (InfoList.Count < 5)
for (int i = InfoList.Count; i < cout; i++)
InfoList.Add("0");
//левый верхний блок
newSheet.Cells["A1"].Value = "Дата теста";
newSheet.Cells["A2"].Value = "Профиль";
newSheet.Cells["B1"].Value = fromTime.Substring(0,(fromTime.IndexOf(' ')));
newSheet.Cells["B2"].Value = effort + "%";
newSheet.Cells["A3"].Value = "Время начала";
newSheet.Cells["B3"].Value = fromTime.Substring((fromTime.IndexOf(' ')) + 1);
newSheet.Cells["B4"].Value = toTime.Substring((toTime.IndexOf(' ')) + 1);
newSheet.Cells["B3,B4"].Style.Numberformat.Format = "##:##:##00:00:00";
newSheet.Cells["A4"].Value = "Время завершения";
//Блок тестируемая среда
newSheet.Cells["D6"].Value = "Тестируемая среда " + serv;
newSheet.Cells["D6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
newSheet.Cells[7,3].Value = "Имя сервера";
newSheet.Cells["D7"].Value = InfoList[1];
newSheet.Cells[8,3].Value = "Операционная система";
newSheet.Cells["D8"].Value = InfoList[0];
newSheet.Cells["C7:C8"].StyleName = "MyBlock";
//Блок Процессор
newSheet.Cells["D10"].Value = "Процессор";
newSheet.Cells["D10"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
newSheet.Cells[11,3].Value = "Общее количество активных ядер";
if (InfoList[4].ToString().Contains("---"))
InfoList[4] = Cores.Count;
newSheet.Cells["D11"].Value = InfoList[4];
newSheet.Cells[12,3].Value = "Модель процессора";
newSheet.Cells["D12"].Value = InfoList[3];
newSheet.Cells[13,3].Value = "Средняя загрузка, %";
newSheet.Cells["D13"].Formula = _excelFile.Workbook.Worksheets["CPU"].Cells["H3"].FullAddress;
newSheet.Cells["C11:C13"].StyleName = "MyBlock";
//Блок Память
newSheet.Cells["D15"].Value = "Оперативная память";
newSheet.Cells["D15"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
newSheet.Cells[16,3].Value = "Общее количество памяти, Mb";
/*if (!(InfoList[2].ToString() == "0"))
newSheet.Cells["D16"].Value = doMemTot(InfoList[2].ToString());
else*/
newSheet.Cells["D16"].Value = InfoList[2];
newSheet.Cells[17,3].Value = "Средняя загрузка памяти, %";
newSheet.Cells["D17"].Formula = _excelFile.Workbook.Worksheets["Mem"].Cells["E3"].FullAddress; ;
newSheet.Cells[18,3].Value = "Свободная память, Мb";
newSheet.Cells["D18"].Formula = newSheet.Cells["D16"].Address + "*" + newSheet.Cells["D19"].Address + "/100";
newSheet.Cells["D18"].Style.Numberformat.Format = "###000";
newSheet.Cells[19,3].Value = "Свободная память, %";
newSheet.Cells["D19"].Formula = "100-" + newSheet.Cells["D17"].Address;
newSheet.Cells["D13,D17,D19"].Style.Numberformat.Format = "#,##0.00";
newSheet.Cells["C16:C19"].StyleName = "MyBlock";
//Блок диски
newSheet.Cells["D21"].Value = "Диски";
newSheet.Cells["D21"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
for (int i = 0; i < DiskList.Count; i++)
{
newSheet.Cells["C" + (22 + i)].Value = i + 1;
newSheet.Cells["D" + (22 + i)].Value = DiskList[i];
}
newSheet.Cells["C22:C" + newSheet.Dimension.End.Row].StyleName = "MyBlock";
newSheet.Cells["C22:C" + newSheet.Dimension.End.Row].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
using (ExcelRange col = newSheet.Cells["D22:D" + newSheet.Dimension.End.Row])
{
var border = col.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//col.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
}
newSheet.Cells["F6"].Hyperlink = new ExcelHyperLink(_excelFile.Workbook.Worksheets["CPU_All"].Name + "!A1", "График загрузки по всем ядрам");
newSheet.Cells["F10"].Hyperlink = new ExcelHyperLink(_excelFile.Workbook.Worksheets["CPU"].Name + "!A1", "Данные по процессору");
newSheet.Cells["F15"].Hyperlink = new ExcelHyperLink(_excelFile.Workbook.Worksheets["Mem"].Name + "!A1", "Данные по памяти");
newSheet.Cells["F21"].Hyperlink = new ExcelHyperLink(_excelFile.Workbook.Worksheets["Disk_use"].Name + "!A1", "Данные по дискам (нагрузка)");
newSheet.Cells["F22"].Hyperlink = new ExcelHyperLink(_excelFile.Workbook.Worksheets["Disk_io"].Name + "!A1", "Данные по дискам (время отклика)");
newSheet.Cells["F6,F10,F15,F22,F21"].StyleName = "MyHLink";
if (newSheet.Dimension != null)
{
newSheet.Cells[newSheet.Dimension.Address].AutoFitColumns();
}
_excelFile.Workbook.Worksheets.MoveToStart(_excelFile.Workbook.Worksheets.Count);
return newSheet;
}
public ExcelWorksheet AddMemTable(string sheetName,int minutes, int interval, string serv, char delimiter = '\t')
{
//ProgressForm FormProgr = new ProgressForm(); FormProgr.Stat = sheetName;
_excelFile.Workbook.Worksheets.Add(sheetName);
var newSheet = _excelFile.Workbook.Worksheets[_excelFile.Workbook.Worksheets.Count];
//newSheet.DefaultColWidth = 50;
var format = new ExcelTextFormat();
format.Delimiter = delimiter;
format.Culture = new CultureInfo("ru-RU");
format.Culture.NumberFormat.NumberDecimalSeparator = ",";
try
{
newSheet.Cells["A2"].Value = "Time";
newSheet.Cells["B2"].Value = "Absol. time";
newSheet.Cells["C2"].Value = serv;
int index = 0;
int column = 3;
for (int page = 1; page < _excelFile.Workbook.Worksheets.Count; page++)
{
int row = 0;
if (_excelFile.Workbook.Worksheets[page].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[page].Name.Contains("mem"))
{
index = page;
string header = dictExcel.search(_excelFile.Workbook.Worksheets[page].Name);
// newSheet.Cells[1, column + 9].Value = newSheet.Cells[1, column].Value = header.Split('_')[0];
//newSheet.Cells[2, column + 9].Value = newSheet.Cells[2, column].Value = header.Split('_')[1];
for (row = 3; row < 4 + interval; row++)
{
if (_excelFile.Workbook.Worksheets[page].Name.Contains("app"))
newSheet.Cells[row, column].Formula = _excelFile.Workbook.Worksheets[page].Cells[row, getFieldNum(_excelFile.Workbook.Worksheets[page].PivotTables[0], "%")].FullAddress;
else
newSheet.Cells[row, column].Formula = _excelFile.Workbook.Worksheets[page].Cells[row, getFieldNum(_excelFile.Workbook.Worksheets[page].PivotTables[0], "%")].FullAddress;
}
newSheet.Cells[row, column].Formula = newSheet.Cells[3, column + 2].Formula = String.Format("AVERAGE({0}:{1})", _excelFile.Workbook.Worksheets[page].Cells[3 + minutes, column].Address, _excelFile.Workbook.Worksheets[page].Cells[row - 1, column].Address);
column++;
}
}
for (int row = 3; row < newSheet.Dimension.End.Row + 1; row++)
{
newSheet.Cells[row, 1].Formula = _excelFile.Workbook.Worksheets[index].Cells[row, 1].FullAddress;
if (row != newSheet.Dimension.End.Row)
newSheet.Cells[row, 2].Formula = newSheet.Cells[row, 1].Address + "-" + newSheet.Cells[3, 1].Address;
}
newSheet.Column(1).StyleName = "MyTime";
newSheet.Column(2).StyleName = "AbsTime";
newSheet.Cells.Style.Font.Name = "Calibri";
newSheet.Cells.Style.Font.Size = 8;
newSheet.Cells["C:X"].Style.Numberformat.Format = "#,##0.00";
newSheet.Cells["E2"].Value = "AVERAGE";
using (ExcelRange col = newSheet.Cells["E2:E3,A1:C"+newSheet.Dimension.End.Row])
{
var border = col.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
}
var chart = newSheet.Drawings.AddChart("Memory", eChartType.Line);
chart.Title.Text = serv + " Memory usage";
chart.Title.Font.Size = 12;
chart.SetPosition(200, 300);
chart.Legend.Position = eLegendPosition.Bottom;
chart.Legend.Add();
chart.SetSize(1000, 600);
chart.YAxis.MaxValue = 100;
chart.YAxis.Format = "000";
chart.YAxis.MinValue = 0;
chart.YAxis.Title.Text = "Средняя загрузка по памяти, %";
chart.XAxis.Title.Text = "Время теста, мин";
chart.YAxis.Title.Font.Bold = true;
chart.YAxis.Title.Font.Size = 10;
chart.XAxis.Title.Font.Bold = true;
chart.XAxis.Title.Font.Size = 10;
for (int i = 0; i < 1; i++)
{
var serie = chart.Series.Add(ExcelRange.GetAddress(3, i + 3, newSheet.Dimension.End.Row - 1, i + 3), ExcelRange.GetAddress(3, 2, newSheet.Dimension.End.Row - 1, 2));
serie.Header = newSheet.Cells[2, i + 3].Text;
}
}
catch (Exception ex)
{
Popup.ShowException(ex);
}
if (newSheet.Dimension != null)
{
newSheet.Cells[newSheet.Dimension.Address].AutoFitColumns();
}
return newSheet;
}
public ExcelWorksheet AddDiskTable(string sheetName, int minutes, int interval, string serv, char delimiter = '\t')
{
//ProgressForm FormProgr = new ProgressForm(); FormProgr.Stat = sheetName;
_excelFile.Workbook.Worksheets.Add(sheetName);
var newSheet = _excelFile.Workbook.Worksheets[_excelFile.Workbook.Worksheets.Count];
var format = new ExcelTextFormat();
format.Delimiter = delimiter;
format.Culture = new CultureInfo("ru-RU");
format.Culture.NumberFormat.NumberDecimalSeparator = ",";
int column = 3;
try
{
newSheet.Cells["A2"].Value = "Time";
newSheet.Cells["B2"].Value = "Absol. time";
int index = 0;
int indexA = 0;
int row = 0;
if (sheetName == "Disk_io")
{
for (int page = 1; page < _excelFile.Workbook.Worksheets.Count; page++)
{
row = 0;
if (_excelFile.Workbook.Worksheets[page].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[page].Name.Contains("disk_io"))
{
index = page;
int n = DiskList.Count + 2;/*
if (_excelFile.Workbook.Worksheets[page].Name.Contains("app"))
n = 4;
else n = 15;*/
for (column = 2; column < n; column++)
{
newSheet.Cells[2, column + 1].Value = DiskList[column-2];
for (row = 3; row < 5 + interval; row++)
{
newSheet.Cells[row, column + 1].Formula = _excelFile.Workbook.Worksheets[index].Cells[row, column].FullAddress;
}
}
for (row = 3; row < newSheet.Dimension.End.Row + 1; row++)
{
newSheet.Cells[row, 1].Formula = _excelFile.Workbook.Worksheets[index].Cells[row, 1].FullAddress;
if (row != newSheet.Dimension.End.Row)
newSheet.Cells[row, 2].Formula = newSheet.Cells[row, 1].Address + "-" + newSheet.Cells[3, 1].Address;
}
column = n + 1;
newSheet.Cells[2, column].Value = "20 ms";
for (row = 3; row < 4 + interval; row++)
{
newSheet.Cells[row, column].Value = 20;
}
}
}
} else if (sheetName == "Disk_use")
{
for (int page = 1; page < _excelFile.Workbook.Worksheets.Count; page++)
{
row = 0;
if (_excelFile.Workbook.Worksheets[page].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[page].Name.Contains("sar-disk"))
{
indexA = page;
int n = DiskList.Count + 2;/*
if (_excelFile.Workbook.Worksheets[page].Name.Contains("app"))
n = 4;
else n = 15;*/
for (column = 2; column < n; column++)
{
newSheet.Cells[2, column + 1].Value = DiskList[column - 2];
for (row = 3; row < 5 + interval; row++)
{
newSheet.Cells[row, column + 1].Formula = _excelFile.Workbook.Worksheets[indexA].Cells[row, column].FullAddress;
}
}
for (row = 3; row < newSheet.Dimension.End.Row + 1; row++)
{
newSheet.Cells[row, 1].Formula = _excelFile.Workbook.Worksheets[indexA].Cells[row, 1].FullAddress;
if (row != newSheet.Dimension.End.Row)
newSheet.Cells[row, 2].Formula = newSheet.Cells[row, 1].Address + "-" + newSheet.Cells[3, 1].Address;
}
}
}
}
newSheet.Column(1).StyleName = "MyTime";
newSheet.Column(2).StyleName = "AbsTime";
newSheet.Cells.Style.Font.Name = "Calibri";
newSheet.Cells.Style.Font.Size = 8;
newSheet.Cells["C:X"].Style.Numberformat.Format = "#,##0.00";
if (indexA == 0) //отрезок кода рисующий график
{
var chart = newSheet.Drawings.AddChart("Disk_io", eChartType.Line);
chart.Title.Text = serv + " Disk iotime";
chart.Title.Font.Size = 12;
chart.SetPosition(200, 300);
chart.Legend.Position = eLegendPosition.Bottom;
chart.Legend.Add();
chart.SetSize(1000, 600);
chart.YAxis.Format = "000";
chart.YAxis.Title.Text = "Время отклика дисков, мс";
chart.XAxis.Title.Text = "Время теста, мин";
chart.YAxis.Title.Font.Bold = true;
chart.YAxis.Title.Font.Size = 10;
chart.XAxis.Title.Font.Bold = true;
chart.XAxis.Title.Font.Size = 10;
int n = DiskList.Count;
using (ExcelRange col = newSheet.Cells[1, 1, newSheet.Dimension.End.Row, n + 3])
{
var border = col.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
}
for (int i = 0; i < n + 1; i++)
{
var serie = chart.Series.Add(ExcelRange.GetAddress(3, i + 3, newSheet.Dimension.End.Row - 1, i + 3), ExcelRange.GetAddress(3, 2, newSheet.Dimension.End.Row - 1, 2));
serie.Header = newSheet.Cells[2, i + 3].Text;
if (i==n+1) chart.Border.LineStyle = eLineStyle.Dash;
}
}
else
{
var chart = newSheet.Drawings.AddChart("Disk_use", eChartType.Line);
chart.Title.Text = serv + " Disk usage";
chart.Title.Font.Size = 12;
chart.SetPosition(200, 300);
chart.Legend.Position = eLegendPosition.Bottom;
chart.Legend.Add();
chart.SetSize(1000, 600);
chart.YAxis.Format = "#0";
chart.YAxis.MaxValue = 100;
chart.YAxis.MinValue = 0;
chart.YAxis.Title.Text = "Средняя нагрузка на диски, %";
chart.XAxis.Title.Text = "Время теста, мин";
chart.YAxis.Title.Font.Bold = true;
chart.YAxis.Title.Font.Size = 10;
chart.XAxis.Title.Font.Bold = true;
chart.XAxis.Title.Font.Size = 10;
int n = DiskList.Count;
using (ExcelRange col = newSheet.Cells[1, 1, newSheet.Dimension.End.Row, n + 2])
{
var border = col.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
}
for (int i = 0; i < n; i++)
{
var serie = chart.Series.Add(ExcelRange.GetAddress(2, i + 3, newSheet.Dimension.End.Row - 1, i + 3), ExcelRange.GetAddress(3, 2, newSheet.Dimension.End.Row - 1, 2));
serie.Header = newSheet.Cells[2, i + 3].Value.ToString();
}
}
}
catch (Exception ex)
{
Popup.ShowException(ex);
}
if (newSheet.Dimension != null)
{
newSheet.Cells[newSheet.Dimension.Address].AutoFitColumns();
}
return newSheet;
}
/// <summary>
/// Добавить лист с Aggregate Report, используя поток для получения данных.
/// </summary>
/// <param name="sheetName">Имя создаваемого листа.</param>
/// <param name="inputStream">Поток, содержащий данные для содаваемого листа.</param>
/// <param name="delimiter">Разделитель значений в строках потока.</param>
/// <returns>Ссылка на созданный лист.</returns>
public ExcelWorksheet AddAggregateReportWorksheet(string sheetName, StreamReader inputStream,
char delimiter = '\t')
{
var newSheet = _excelFile.Workbook.Worksheets.Add(sheetName);
newSheet.Cells.Style.Font.Size = 11;
newSheet.Cells.Style.Font.Name = "Calibri";
newSheet.View.FreezePanes(2, 1);
var format = new ExcelTextFormat();
format.Delimiter = delimiter;
format.Culture = new CultureInfo("ru-RU");
format.Culture.NumberFormat.NumberDecimalSeparator = ",";
var row = 1;
try
{
while (!inputStream.EndOfStream)
newSheet.Cells["A" + row++].LoadFromText(inputStream.ReadLine(), format);
}
catch (ObjectDisposedException ode)
{
Popup.ShowException(ode);
}
catch (OutOfMemoryException oome)
{
Popup.ShowException(oome);
}
catch (IOException ioe)
{
Popup.ShowException(ioe);
}
newSheet.Column(8).StyleName = stylePercent.Name;
if (newSheet.Dimension != null)
{
newSheet.Cells[newSheet.Dimension.Address].AutoFitColumns();
var border = newSheet.Cells[newSheet.Dimension.Address].Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
}
return newSheet;
}
