/// <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; }