private void Openexcel_Click_1(object sender, EventArgs e)
{
List <int> processesbeforegen = getRunningProcesses();
if (excelac.ShowDialog() == DialogResult.OK)
{
textBox3.Text = excelac.FileName;
Excel.Application exc = new Excel.Application();
{
exc.Visible = false;
}
exc.Workbooks.Open(textBox3.Text);
Worksheet sheet = exc.Worksheets[1];
sheet.Activate();
foreach (Worksheet ws in exc.Worksheets)
{
ChartObjects chartobjects = ws.ChartObjects();
foreach (ChartObject co in chartobjects)
{
co.Select();
Excel.Chart chart = co.Chart;
chart.Export(exportpath.SelectedPath + @"\" + chart.Name + ".png", "PNG", false);
}
}
progressBar1.Value = 40;
MessageBox.Show("The graphs are imported successfully.");
List <int> processesaftergen = getRunningProcesses();
killProcesses(processesbeforegen, processesaftergen);
}
}
/// <summary>
/// geterate thumbnail for a shape or a chart
/// </summary>
/// <param name="shape"> desired shape</param>
/// <param name="path">path where the thumbnail will be created, excluding the .extension</param>
/// <returns>full path of the created thumbnail</returns>
private String GenerateThumbnail(Microsoft.Office.Interop.Excel.Chart shape, String path)
{
shape.Export(path + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);
Bitmap bmp = new Bitmap(path + DocumentService.IMAGE_EXTENSION);
int T_H, T_W;
if (bmp.Width <= bmp.Height)
{
T_W = (int)(DocumentService.THUMB_WIDTH * bmp.Width / bmp.Height);
T_H = DocumentService.THUMB_WIDTH;
}
else
{
T_H = (int)(DocumentService.THUMB_WIDTH * bmp.Height / bmp.Width);
T_W = DocumentService.THUMB_WIDTH;
}
Size s = new Size(T_W, T_H);
Bitmap bmp1 = new Bitmap(bmp, s);
bmp.Dispose();
FileInfo fi = new FileInfo(path + DocumentService.IMAGE_EXTENSION);
fi.Delete();
bmp1.Save(path + DocumentService.IMAGE_EXTENSION);
return(path + DocumentService.IMAGE_EXTENSION);
}
static void Main(string[] excel)
{
Excel.Application imgconverter = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Microsoft.Office.Interop.Excel.Application;
ConsoleColor c = Console.ForegroundColor;
Console.ForegroundColor = ConsoleColor.Red;
Console.Write("Export To: ");
Console.ForegroundColor = c;
string exportPath = Console.ReadLine();
if (exportPath == "")
{
exportPath = EXPORT_TO_DIRECTORY;
}
Excel.Workbook wb = imgconverter.ActiveWorkbook;
foreach (Excel.Worksheet ws in wb.Worksheets)
{
Excel.ChartObjects chartobjects = (Excel.ChartObjects)(ws.ChartObjects(Type.Missing));
foreach (Excel.ChartObject co in chartobjects)
{
co.Select();
Excel.Chart chart = co.Chart;
chart.Export(exportPath + chart.Name + ".png", "PNG", false);
}
}
Process.Start(exportPath);
}
private static void ReportExcelToImage(String filePath, String[] columnList, String title, ChartType chartType = ChartType.COLUMN)
{
FileInfo file = new FileInfo(filePath);
String directory = "";
if (Constant.ANALYZE_CHART_DIR.Equals(""))
{
directory = file.DirectoryName;
}
else
{
directory = Constant.ANALYZE_CHART_DIR;
}
String fileName = file.Name.Substring(0, file.Name.IndexOf('.'));
String imagePath = directory + @"\" + fileName + "-" + title + ".jpg";
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(filePath);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 1000, 850);
Excel.Chart chartPage = myChart.Chart;
//non-empty count of csv file
int count = Convert.ToInt32(xlApp.WorksheetFunction.CountA(xlWorkSheet.get_Range("A:A")));
//non-empty count of header
int headerCount = Convert.ToInt32(xlApp.WorksheetFunction.CountA(xlWorkSheet.Rows[1]));
string chartString = "A1:A" + count;
foreach (var column in columnList)
{
int columnIndex = GetColumnIndex(xlWorkSheet, headerCount, column);
chartString += "," + GetString(columnIndex) + "1:" + GetString(columnIndex) + count;
}
chartRange = xlWorkSheet.get_Range(chartString);
chartPage.SetSourceData(chartRange, misValue);
//chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
chartPage.ChartType = GetChartType(chartType);
chartPage.Axes(Excel.XlAxisType.xlValue);
//export chart as picture file
chartPage.Export(imagePath, "JPG", misValue);
xlWorkBook.Close(false, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
public void click_exportChart(Office.IRibbonControl control)
{
// Get Active Elements
Excel.Chart chart_active = GetActiveChart();
Excel.Workbook workbook_active = GetActiveWorkbook();
if (chart_active != null && workbook_active != null)
{
// Save File Dialog
SaveFileDialog dialog_saveFile = new SaveFileDialog();
dialog_saveFile.Filter = "JPEG Image|*.jpg|Bitmap Image|*.bmp|GIF Image|*.gif|PNG Image|*.png|JPE Image|*.jpe";
dialog_saveFile.Title = "Export Chart As...";
// Set Default File Name
dialog_saveFile.FileName = chart_active.ChartTitle.Text;
// Set Default Directory
SetDefaultDirectory(dialog_saveFile, workbook_active);
// Show Save File Dialog
dialog_saveFile.ShowDialog();
// Export Chart
if (!String.IsNullOrEmpty(dialog_saveFile.FileName))
{
chart_active.Export(dialog_saveFile.FileName);
}
}
}
private void ExportChart(Excel.Chart chart)
{
chart.Export($@"{destinationFolder}\{chartNumber} - {chart.ChartTitle.Text}.png", "PNG");
chartNumber++;
chart.Delete();
ChartCreated?.Invoke(this, $"{chart.ChartTitle.Text} created");
}
public void ExportCharts(string item, string folder, string format)
{
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)_xlWorkBook.Worksheets.Item[item];
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
foreach (Excel.ChartObject xlChart in xlCharts)
{
Excel.Chart chartPage = xlChart.Chart;
chartPage.Export(Path.Combine(folder, $"{chartPage.Name}.{format}"), format, _misValue);
}
}
public void createTempChart()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook
.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 2] = "a";
xlWorkSheet.Cells[1, 3] = "b";
Excel.Application xlApp1 = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp1.Workbooks.Open
(@"E:\temp.csv");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet
.ChartObjects(Type.Missing);
Excel.ChartObject myChart =
(Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "C6");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
// Export chart as picture file
chartPage.Export(@"E:\temp_data.bmp", "BMP",
misValue);
xlWorkBook.SaveAs("temp.csv",
Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue,
misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
DeallocateObject(xlWorkSheet);
DeallocateObject(xlWorkBook);
DeallocateObject(xlApp);
DeallocateObject(xlApp1);
}
private void button14_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = true;
Workbook wb = excel.Workbooks.Add();
Worksheet sheet = wb.ActiveSheet;
ListObject tab1 = sheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, sheet.get_Range("A1:D4"));
PublishObject po = wb.PublishObjects.Add(XlSourceType.xlSourceRange, "e:\\1.htm", "Sheet1", "A1:D4", XlHtmlType.xlHtmlStatic, "wkbk_0009", "");
Microsoft.Office.Interop.Excel.Shape shape = sheet.Shapes.AddChart(Microsoft.Office.Interop.Excel.XlChartType.xlPie);
Microsoft.Office.Interop.Excel.Chart chart = shape.Chart;// (Microsoft.Office.Interop.Excel.Chart)
chart.Export("e:\\11.png");
//chart.ExportAsFixedFormat(XlFixedFormatType.xlTypeXPS, "e:\\111.xps");
po.Publish(true);
po.AutoRepublish = false;
wb.Close(false);
excel.Quit();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = true;
Excel.Workbook wb = excelApp.Workbooks.Add();
Excel.Worksheet sheet = wb.ActiveSheet;
//add data
sheet.Cells[1, 1] = "";
sheet.Cells[1, 2] = "Student1";
sheet.Cells[1, 3] = "Student2";
sheet.Cells[1, 4] = "Student3";
sheet.Cells[2, 1] = "Term1";
sheet.Cells[2, 2] = "80";
sheet.Cells[2, 3] = "65";
sheet.Cells[2, 4] = "45";
sheet.Cells[3, 1] = "Term2";
sheet.Cells[3, 2] = "78";
sheet.Cells[3, 3] = "72";
sheet.Cells[3, 4] = "60";
sheet.Cells[4, 1] = "Term3";
sheet.Cells[4, 2] = "82";
sheet.Cells[4, 3] = "80";
sheet.Cells[4, 4] = "65";
sheet.Cells[5, 1] = "Term4";
sheet.Cells[5, 2] = "75";
sheet.Cells[5, 3] = "82";
sheet.Cells[5, 4] = "68";
Excel.Range chartRange = sheet.get_Range("A1", "d5");
Excel.ChartObjects xlCharts = (Excel.ChartObjects)sheet.ChartObjects();
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartPage.ChartWizard(chartRange, Excel.XlChartType.xlColumnClustered, Title: "Diagram title");
chartPage.Export(Application.StartupPath + @"./excel_chart_export.png", "png");
}
private void button3_Click(object sender, EventArgs e) //Refresh/ get charts
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "d5");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
//export chart as picture file
chartPage.Export(@"C:\Dumpster\testcsv.bmp", "BMP", misValue);
// pictureBox1.Image = new Bitmap(@"C:\Dumpster\testcsv.bmp");
xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\\csharp-Excel.xls");
}
//// 關閉Excel Message
// Microsoft.Office.Interop.Excel.Application.DisplayAlerts = false;
// Microsoft.Office.Interop.Excel.Application.Visible = false; // 設為true,則一開始就會顯現Excel檔.
// //ExlBook = ExlApp.Workbooks.Add(Server.MapPath(".") + "\\Tool_Prod_sample.xls");
// ExlBook = ExlApp.Workbooks.Add("c:\TAIWAN_BANK_OutSite_Salary_FA_20160111.xls");
protected void Page_Load(object sender, EventArgs e)
{
// xl.Application ExlApp ;
// ExlApp = new xl.ApplicationClass();
// object missValue = System.Reflection.Missing.Value;
// string path=@"c:\\TAIWAN_BANK_OutSite_Salary_FA_20160111.xls";
// Workbook w = ExlApp.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//// (path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)w.Sheets["Sheet1"];
// //ws.Protect(Contents: false);
// Range r = ws.get_Range("B2","H20");
// r.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap);
// Bitmap image = new Bitmap(Clipboard.GetImage());
// image.Save(@"C:\abc\image.png");
xl.Application xlApp;
xl.Workbook xlWorkBook;
xl.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new xl.Application();
string path = @"c:\\abcd.xls";
xlWorkBook = xlApp.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (xl.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xl.Range xlRange;
xlRange = xlWorkSheet.get_Range("A1", "d5");
xlRange.CopyPicture(Microsoft.Office.Interop.Excel.XlPictureAppearance.xlScreen, Microsoft.Office.Interop.Excel.XlCopyPictureFormat.xlPicture);
xl.ChartObjects xlCharts = (xl.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
xl.ChartObject myChart = (xl.ChartObject)xlCharts.Add(10, 80, 300, 250);
xl.Chart chartPage = myChart.Chart;
xl.Range chartRange;
chartRange = xlWorkSheet.get_Range("A1", "d5");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = xl.XlChartType.xlColumnClustered;
//export chart as picture file
//chartPage.Paste();
chartPage.Export(@"C:\excel_chart_export.JPG", "JPG", misValue);
xlCharts.Delete();
//xlWorkBook.SaveAs(@"C:\excel_chart_export.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
// Generates an excel spreadsheet with graphs of each summary element by depth and saves each graph as a PNG file.
public static void CreateGTGraphs(string domainName, string timeStamp, int depth, List <List <Tuple <String, String> > > summaries)
{
// The top level log directory.
string directory = Parser.GetTopDirectory() + @"GameTrees\" + domainName + @"\" + timeStamp + @"\";
// The graph image directory.
string imageDir = directory + @"graphs\";
// Check if the image directory exists.
if (!File.Exists(imageDir))
{
// If not, create it.
Directory.CreateDirectory(imageDir);
}
// This is needed for a lot of the Excel initialization tasks.
object misValue = System.Reflection.Missing.Value;
// Create an Excel application object.
Excel.Application xl = new Excel.Application();
// Create an Excel work sheet variable.
Excel.Worksheet xlWorkSheet;
// Create an Excel work books variable and point it at the application object's work books..
Excel.Workbooks xlWorkBooks = xl.Workbooks;
// Make the Excel application visible? Not sure, it's magic.
xl.Visible = true;
// Open the summary CSV file in the Excel work book.
xlWorkBooks.OpenText(directory + "summary.csv", misValue, misValue, Excel.XlTextParsingType.xlDelimited,
Excel.XlTextQualifier.xlTextQualifierNone, misValue, misValue,
misValue, misValue, misValue, misValue, misValue, misValue, misValue,
misValue, misValue, misValue, misValue);
// Grab the work sheet that represents the CSV file.
xlWorkSheet = (Excel.Worksheet)xlWorkBooks[1].Worksheets.get_Item(1);
// Loop through every summary element, excluding the depth count which should be first.
for (int summary = 1; summary < summaries[0].Count; summary++)
{
// Create a new Excel chart holder in the work sheet.
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
// Create a new Excel chart and position it below the table and other charts.
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, (15 * summaries[0].Count) + (220 * (summary - 1)), 360, 210);
// Select the chart object's chart. Don't ask me, this Excel interface is strange.
Excel.Chart chartPage = myChart.Chart;
// Make the chart object active.
myChart.Select();
// Set the chart's style. 227 should be a white background with a blue line.
chartPage.ChartStyle = 227;
// Set the type of chart. We are using a line chart.
chartPage.ChartType = Excel.XlChartType.xlLine;
// Turn the legend off.
chartPage.HasLegend = false;
// Create a new series collection to hold the series that will contain the chart data. Again, Excel's interface is dumb.
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection();
// Create a new series.
Excel.Series series1 = seriesCollection.NewSeries();
// Select the chart's X values. These should be the depth counts.
series1.XValues = xlWorkSheet.Range["summary!$A$2:$A$" + depth];
// Select the chart's Y values. These should be the current summary element data.
series1.Values = xlWorkSheet.Range["summary!$" + ToLetter(summary) + "$2:$" + ToLetter(summary) + "$" + depth];
// Name the chart according to the current summary element.
series1.Name = summaries[0][summary].First;
// Export the current chart as a PNG image.
chartPage.Export(imageDir + series1.Name + ".png", "PNG", false);
}
// Save the current work book as an XLS file.
xlWorkBooks[1].SaveAs(directory + "graphsummary-" + timeStamp + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
// Close the current work book.
xlWorkBooks[1].Close(true, misValue, misValue);
// Quit the Excel application.
xl.Quit();
// Do some garbage collection? Not sure, all examples I saw had this.
releaseObject(xlWorkSheet);
releaseObject(xlWorkBooks);
releaseObject(xl);
}
public static void CreateWord()
{
//杀掉winword.exe进程，加快执行速度，防止打开副本等Bug
killWinWordProcess();
//生成每个解决方案的图片
if (HeatSourceLayoutApp.createScreen() == false)
{
return;
}
//当前表格数目
int currentTableNum = 0;
var dialog = new SaveFileDialog("选择文件存储位置", "燃气热源方案比选", "docx", "SaveFile", SaveFileDialog.SaveFileDialogFlags.DefaultIsFolder);
System.Windows.Forms.DialogResult result = dialog.ShowDialog();
if (result != System.Windows.Forms.DialogResult.OK)
{
return;
}
object savePath = dialog.Filename;
var wordApp = new Microsoft.Office.Interop.Word.Application();
#if DEBUG
object templatePath = HeatSourceLayoutApp.CurrentDirectory + @"/Template.docx";
#else
object templatePath = HeatSourceLayoutApp.CurrentDirectory + @"/Resource/Template.docx";
#endif
//object savaPath = System.IO.Directory.GetCurrentDirectory() + @"./燃气热源方案比选.docx";
object miss = System.Reflection.Missing.Value;
Document doc = wordApp.Documents.Open(ref templatePath, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss);
//解决方案数目solNum
//每个解决方案有1 + 热源数*3 + 1 个表格
int solNum = HeatSourceLayoutApp.solutions.Count;
//基本信息表
Microsoft.Office.Interop.Word.Table infoTable;
//热源能耗表
Microsoft.Office.Interop.Word.Table hsEnergyTable;
//热源运行费用表
Microsoft.Office.Interop.Word.Table hsOperationTable;
//热源初投资（造价）表
Microsoft.Office.Interop.Word.Table hsInvestTable;
//工程总表
Microsoft.Office.Interop.Word.Table totalTable;
//根据书签定位到待插入方案的位置
object oStart = "SolForInsert";
Range range = doc.Bookmarks.get_Item(ref oStart).Range;
#if DEBUG
string heatPart = HeatSourceLayoutApp.CurrentDirectory + @"\heatPart.docx";
string startPart = HeatSourceLayoutApp.CurrentDirectory + @"\startPart.docx";
string endPart = HeatSourceLayoutApp.CurrentDirectory + @"\endPart.docx";
object heatFile = HeatSourceLayoutApp.CurrentDirectory + @"\heatPart.docx";
object startFile = HeatSourceLayoutApp.CurrentDirectory + @"\startPart.docx";
object endFile = HeatSourceLayoutApp.CurrentDirectory + @"\endPart.docx";
#else
string heatPart = HeatSourceLayoutApp.CurrentDirectory + @"\Resource\heatPart.docx";
string startPart = HeatSourceLayoutApp.CurrentDirectory + @"\Resource\startPart.docx";
string endPart = HeatSourceLayoutApp.CurrentDirectory + @"\Resource\endPart.docx";
object heatFile = HeatSourceLayoutApp.CurrentDirectory + @"\Resource\heatPart.docx";
object startFile = HeatSourceLayoutApp.CurrentDirectory + @"\Resource\startPart.docx";
object endFile = HeatSourceLayoutApp.CurrentDirectory + @"\Resource\endPart.docx";
#endif
//首先根据模板文件Template.docx，插入表格
//insertFile()是倒着插入
for (int sl = solNum - 1; sl >= 0; sl--)
{
// insert a new line
var pText = range.Paragraphs.Add();
pText.Range.Text = String.Format("\n");
pText.Range.InsertParagraphAfter();
//插入解决方案的图片
string imagePath = HeatSourceLayoutApp.CurrentDirectory + "\\tmp\\" + sl + ".jpg";
object linktofile = false;
object savewithdocument = true;
Microsoft.Office.Interop.Word.InlineShape shape = wordApp.ActiveDocument.InlineShapes.AddPicture(imagePath,
ref linktofile, ref savewithdocument, range);
shape.Range.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;
//倒序插入，先插入末尾部分
Document endPartFile = wordApp.Documents.Open(ref endFile, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss);
object oEnd = "EndPartPosition";
object endPartIndex = "EndPartIndex";
Range endRange = endPartFile.Bookmarks.get_Item(ref oEnd).Range;
Range endIndexRange = endPartFile.Bookmarks.get_Item(ref endPartIndex).Range;
endIndexRange.Text = (sl + 1).ToString();
endRange.Text = (sl + 1).ToString();
range.InsertFile(endPart, ref miss, ref miss, ref miss);
//不保存直接关闭
object isSave = false;
endPartFile.Close(ref isSave, ref miss, ref miss);
currentTableNum++;
//根据当前解决方案内的热源数目，插入热源部分
int heatSourceNum = HeatSourceLayoutApp.solutions[sl].HeatProducers.Count;
for (int h = heatSourceNum; h > 0; h--)
{
Document heatPartFile = wordApp.Documents.Open(ref heatFile, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss);
object solutionIndex = "SolutionIndex";
object heatProducerIndex = "HeatProducerIndex";
object heatProducerPosition = "HeatProducerPosition";
Range solRange = heatPartFile.Bookmarks.get_Item(ref solutionIndex).Range;
Range heatIndexRange = heatPartFile.Bookmarks.get_Item(ref heatProducerIndex).Range;
Range heatPositionRange = heatPartFile.Bookmarks.get_Item(ref heatProducerPosition).Range;
solRange.Text = (sl + 1).ToString();
heatIndexRange.Text = h.ToString();
heatPositionRange.Text = h.ToString();
range.InsertFile(heatPart, ref miss, ref miss, ref miss);
heatPartFile.Close(ref isSave, ref miss, ref miss);
currentTableNum += 3;
}
//最后插入开头部分
Document startPartFile = wordApp.Documents.Open(ref startFile, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss);
object oStartPart = "StartPartPosition";
object oStartPart2 = "StartPartPosition_2";
Range startRange = startPartFile.Bookmarks.get_Item(ref oStartPart).Range;
Range startRange_2 = startPartFile.Bookmarks.get_Item(ref oStartPart2).Range;
startRange.Text = (sl + 1).ToString();
startRange_2.Text = (sl + 1).ToString();
range.InsertFile(startPart, ref miss, ref miss, ref miss);
startPartFile.Close(ref isSave, ref miss, ref miss);
currentTableNum++;
}
////填数据
////当前解决方案的下标是sl
for (int sl = 0; sl < solNum; sl++)
{
//之前解决方案内热源数目
int lastHsNum = 0;
for (int k = 0; k < sl; k++)
{
lastHsNum += HeatSourceLayoutApp.solutions[k].HeatProducers.Count;
}
//之前共有表格数
int tableNum = 3 * lastHsNum + 2 * sl;
//第一部分，基本信息表 6*2
//报System.Runtime.InteropServices.COMException (0x800706BA): RPC 服务器不可用。
infoTable = doc.Tables[tableNum + 1];
//填充地点信息
infoTable.Cell(1, 2).Range.Text = HeatSourceLayoutApp.globalProperty.CityName;
//填充地区类型
string typeString = Constants.LocationType[HeatSourceLayoutApp.globalProperty.LocationType];
infoTable.Cell(2, 2).Range.Text = typeString;
infoTable.Cell(2, 2).Range.Bold = 1;
//填充气象参数
//室外温度 t_(a.h) 室外均温 t_a 供暖天数 D
double IndoorTemperature = HeatSourceLayoutApp.globalProperty.IndoorTemperature;
double OutDoorTemp = HeatSourceLayoutApp.globalProperty.OutDoorTemp;
double OutAverageTemp = HeatSourceLayoutApp.globalProperty.OutAverageTemp;
double HeatingDays = HeatSourceLayoutApp.globalProperty.HeatingDays;
infoTable.Cell(3, 2).Range.Text = HeatingDays.ToString();
infoTable.Cell(4, 2).Range.Text = IndoorTemperature.ToString();
infoTable.Cell(5, 2).Range.Text = OutDoorTemp.ToString();
infoTable.Cell(6, 2).Range.Text = OutAverageTemp.ToString();
infoTable.Cell(3, 2).Range.Bold = 1;
infoTable.Cell(4, 2).Range.Bold = 1;
infoTable.Cell(5, 2).Range.Bold = 1;
infoTable.Cell(6, 2).Range.Bold = 1;
//第二部分，热源对应的三个表
//当前解决方案内热源数目
int hsNum = HeatSourceLayoutApp.solutions[sl].HeatProducers.Count;
//当前解决方案供热总面积 全年总耗热量 总运行费用 总造价
double totalArea = 0;
double totalHeat = 0;
for (int hs = 0; hs < hsNum; hs++)
{
//1 - 能耗计算
//根据热源包含的楼房数目添加行数
hsEnergyTable = doc.Tables[tableNum + 1 + 1 + 3 * hs];
int directBuildingNum = 0;
int slaveBuildingNum = 0;
//记录热力站中的buildings
List <Building> substationBuildings = new List <Building>();
if (HeatSourceLayoutApp.solutions[sl].HeatProducers.ElementAt(hs).Value.OwnSlaveDistrict != null)
{
foreach (var sub in HeatSourceLayoutApp.solutions[sl].HeatProducers.ElementAt(hs).Value.OwnSlaveDistrict.SubStations)
{
if (sub.OwnMasterDistrict != null)
{
foreach (var b in sub.OwnMasterDistrict.Buildings)
{
substationBuildings.Add(b);
}
}
}
}
slaveBuildingNum = substationBuildings.Count;
//如果不含有OwnSlaveDistrict,报NullRefException
try
{
directBuildingNum = HeatSourceLayoutApp.solutions[sl].HeatProducers.ElementAt(hs).Value.OwnMasterDistrict.Buildings.Count;
}
catch (Exception ex)
{
ex.ToString();
}
//填充热源中楼房信息,从第2行开始，依次为
//楼号-建筑类型-面积-供暖方式-采暖热指标-采暖热负荷-节能措施-直接全年耗热量
int totalBuildingNum = directBuildingNum + slaveBuildingNum;
int lineIndex = 2;
for (int b = 0; b < directBuildingNum; b++)
{
Building building = HeatSourceLayoutApp.solutions[sl].HeatProducers.ElementAt(hs).Value.OwnMasterDistrict.Buildings[b];
hsEnergyTable.Rows.Add(ref miss);
hsEnergyTable.Cell(lineIndex, 1).Range.Text = (b + 1).ToString();
//建筑类型
string typeStr = Constants.BuildingType[building.BuildingType];
hsEnergyTable.Cell(lineIndex, 2).Range.Text = typeStr;
hsEnergyTable.Cell(lineIndex, 3).Range.Text = building.Area.ToString();
//供暖方式
string styleStr = Constants.HeatStyle[building.HeatStyle];
hsEnergyTable.Cell(lineIndex, 4).Range.Text = styleStr;
hsEnergyTable.Cell(lineIndex, 5).Range.Text = building.HeatIndex.ToString();
hsEnergyTable.Cell(lineIndex, 6).Range.Text = building.HeatLoad.ToString();
//节能措施
string eneStyleStr = Constants.SaveStyle[building.EnergySavingStyle];
hsEnergyTable.Cell(lineIndex, 7).Range.Text = eneStyleStr;
hsEnergyTable.Cell(lineIndex, 8).Range.Text = building.YearHeat.ToString();
lineIndex++;
}
//如果热源中包含热力站，把热力站中的楼房也加进去
for (int b = 0; b < slaveBuildingNum; b++)
{
Building building = substationBuildings[b];
hsEnergyTable.Rows.Add(ref miss);
hsEnergyTable.Cell(lineIndex, 1).Range.Text = (b + 1).ToString();
//建筑类型
string typeStr = Constants.BuildingType[building.BuildingType];
hsEnergyTable.Cell(lineIndex, 2).Range.Text = typeStr;
hsEnergyTable.Cell(lineIndex, 3).Range.Text = building.Area.ToString();
//供暖方式
string styleStr = Constants.HeatStyle[building.HeatStyle];
hsEnergyTable.Cell(lineIndex, 4).Range.Text = styleStr;
hsEnergyTable.Cell(lineIndex, 5).Range.Text = building.HeatIndex.ToString();
hsEnergyTable.Cell(lineIndex, 6).Range.Text = building.HeatLoad.ToString();
//节能措施
string eneStyleStr = Constants.SaveStyle[building.EnergySavingStyle];
hsEnergyTable.Cell(lineIndex, 7).Range.Text = eneStyleStr;
hsEnergyTable.Cell(lineIndex, 8).Range.Text = building.YearHeat.ToString();
lineIndex++;
}
//2 - 运行费用 2*8
//燃料耗量-燃气单价-燃料耗费-水泵全年运行能耗-水泵运行耗电-电费单价-运行电费-总运行费用
hsOperationTable = doc.Tables[tableNum + 1 + 2 + 3 * hs];
//燃料耗量--采暖总耗热量？？？
HeatProducer heatProducer = HeatSourceLayoutApp.solutions[sl].HeatProducers.ElementAt(hs).Value;
//更新解决方案的工程总表信息
totalArea += heatProducer.TotalArea;
totalHeat += heatProducer.TotalLoad;
hsOperationTable.Cell(2, 1).Range.Text = heatProducer.TotalLoad.ToString();
hsOperationTable.Cell(2, 2).Range.Text = HeatSourceLayoutApp.globalProperty.GasPrice.ToString();
hsOperationTable.Cell(2, 3).Range.Text = heatProducer.GetGasHeatingCost().ToString("0.0");
hsOperationTable.Cell(2, 4).Range.Text = heatProducer.totalWaterPumpEnergyConsumption.ToString("0.0");
hsOperationTable.Cell(2, 5).Range.Text = HeatSourceLayoutApp.globalProperty.ElecPrice.ToString("0.0");
hsOperationTable.Cell(2, 6).Range.Text = heatProducer.GetWaterPumpOperationCost().ToString("0.0");
hsOperationTable.Cell(2, 7).Range.Text = (heatProducer.GetGasHeatingCost() + heatProducer.GetWaterPumpOperationCost()).ToString("0.0");
//3 - 初投资费用（造价）
//锅炉造价-换热器造价-水泵造价-管线造价-总造价
hsInvestTable = doc.Tables[tableNum + 1 + 3 + 3 * hs];
hsInvestTable.Cell(2, 1).Range.Text = heatProducer.GetBoildersCost().ToString("0.0");
hsInvestTable.Cell(2, 2).Range.Text = heatProducer.GetHeatExchangerCost().ToString("0.0");
hsInvestTable.Cell(2, 3).Range.Text = heatProducer.GetWaterPumpCost().ToString("0.0");
hsInvestTable.Cell(2, 4).Range.Text = heatProducer.GetPipeLineCost().ToString("0.0");
hsInvestTable.Cell(2, 5).Range.Text = heatProducer.GetTotalCost().ToString("0.0");
}
//当前方案的工程总表, 2*4
//建筑总面积-全年总耗热量-总运行费用-总造价
//之前共有表格数
int tableN = tableNum + 1 + 3 * hsNum;
totalTable = doc.Tables[tableN + 1];
totalTable.Cell(2, 1).Range.Text = totalArea.ToString();
totalTable.Cell(2, 2).Range.Text = totalHeat.ToString("0.0");
double totalFee = HeatSourceLayoutApp.solutions[sl].GetTotalFee();
totalTable.Cell(2, 3).Range.Text = totalFee.ToString("0.0");
//总造价
totalTable.Cell(2, 4).Range.Text = HeatSourceLayoutApp.solutions[sl].GetTotalCost().ToString("0.0");
}
//最终的方案比较，根据解决方案数目动态增加列数
//int currentTableNum = doc.Tables.Count; //不能用
//方案比较表 8 * (solNUm + 1)
Microsoft.Office.Interop.Word.Table solTable = doc.Tables[currentTableNum + 1];
for (int s = 1; s <= solNum; s++)
{
//为每个解决方案增加一列
solTable.Columns.Add(ref miss);
solTable.Cell(1, s + 1).Range.Text = "方案" + s;
//建筑总面积
solTable.Cell(2, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].TotalArea.ToString("0.0");
//全年耗热量
solTable.Cell(3, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].TotalLoad.ToString("0.0");
//燃料费用
solTable.Cell(4, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].GetGasHeatingCost().ToString("0.0");
//水泵全年运行能耗
solTable.Cell(5, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].GetTotalWaterPumpEnergyConsumption().ToString("0.0");
//运行电费
solTable.Cell(6, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].GetWaterPumpCost().ToString("0.0");
//总运行费用
solTable.Cell(7, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].GetTotalFee().ToString("0.0");
//总造价（初投资）
solTable.Cell(8, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].GetTotalCost().ToString("0.0");
}
//生成总方案对比图片
int attrNum = 4;
Excel.Application excel = new Excel.Application();
//excel.Visible = true;
object misValue = System.Reflection.Missing.Value;
Excel.Workbook xlWorkBook = excel.Workbooks.Add(misValue);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//fill data
xlWorkSheet.Cells[1, 1] = "";
for (int i = 1; i <= solNum; i++)
{
xlWorkSheet.Cells[1, i + 1] = "方案" + i.ToString();
}
xlWorkSheet.Cells[2, 1] = "全年能耗（GJ）";
xlWorkSheet.Cells[3, 1] = "水泵功耗（KW∙h）";
xlWorkSheet.Cells[4, 1] = "运行费用（万元）";
xlWorkSheet.Cells[5, 1] = "总造价（万元）";
for (int s = 1; s <= solNum; s++)
{
xlWorkSheet.Cells[2, s + 1] = HeatSourceLayoutApp.solutions[s - 1].TotalLoad.ToString("0.0");
xlWorkSheet.Cells[3, s + 1] = HeatSourceLayoutApp.solutions[s - 1].GetTotalWaterPumpEnergyConsumption().ToString("0.0");
double totalFee = HeatSourceLayoutApp.solutions[s - 1].GetTotalFee() / 10000.0; // 万元
xlWorkSheet.Cells[4, s + 1] = totalFee.ToString("0.0");
double totalCost = HeatSourceLayoutApp.solutions[s - 1].GetTotalCost() / 10000.0; // 万元
xlWorkSheet.Cells[5, s + 1] = totalCost.ToString("0.0");
}
// generate one chart for every attributes
string[] imageNames = new string[4];
for (int i = 2; i <= attrNum + 1; ++i)
{
// create chart
Excel.Range chartRange;
string r1 = "A" + (char)('0' + i);
string r2 = new string((char)('A' + solNum), 1) + (char)('0' + i);
chartRange = xlWorkSheet.get_Range(r1, r2);
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 600, 450);
Excel.Chart chartPage = myChart.Chart;
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
// save chart into image
string path = Directory.GetCurrentDirectory();
string imageName = path + "../../../chart" + i + ".bmp";
imageNames[i - 2] = imageName;
chartPage.Export(imageName, "BMP", misValue);
}
for (int i = 0; i < attrNum; ++i)
{
// append chart images
string fileName = imageNames[i];
object linkToFile = false;
object saveWithDocument = true;
object oEndOfDoc = "\\endofdoc";
Range pictureRange = doc.Bookmarks.get_Item(ref oEndOfDoc).Range;
//pictureRange.InsertAfter("总解决方案对比图");
doc.InlineShapes.AddPicture(fileName, linkToFile, saveWithDocument, pictureRange);
}
// show Word
wordApp.Visible = true;
// save Word
doc.SaveAs(ref savePath, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss, ref miss, ref miss, ref miss,
ref miss);
//关闭doc,wordApp对象
//((_Document)doc).Close();
//((_Application)wordApp).Quit();
// close and release
xlWorkBook.Close(false, misValue, misValue);
excel.Quit();
//releaseObject(xlWorkSheet);
//releaseObject(xlWorkBook);
//releaseObject(excel);
//Console.WriteLine("Excel file created , you can find the file: " + excelName);
}
public PlotGraph(double[] x, double[] y, double k, double b)
{
string path = GetTemplate();
var n = x.Length.ToString();
object misValue = Missing.Value;
var graph = new Excel.Application()
{
Visible = true
};
var workbook = graph.Workbooks.Add(misValue);
var worksheet = (Excel.Worksheet)workbook.Worksheets.Item[1];
worksheet.Cells[1, 1] = "";
worksheet.Cells[1, 2] = "";
worksheet.Cells[1, 3] = "";
worksheet.Cells[1, 4] = "";
for (int i = 1; i < 5; i++)
{
for (int j = 1; j <= x.Length; j++)
{
switch (i)
{
case 1:
worksheet.Cells[j, i] = x[j - 1];
break;
case 2:
worksheet.Cells[j, i] = y[j - 1];
break;
case 3:
worksheet.Cells[j, i] = x[j - 1];
break;
case 4:
worksheet.Cells[j, i] = k * x[j - 1] + b;
break;
}
}
}
Excel.Range range;
var excelCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
var chart = excelCharts.Add(10, 80, 500, 300);
var chartPage = chart.Chart;
chartPage.ApplyChartTemplate(path); //Используем шаблон
//Коллекция данных
Excel.SeriesCollection seriesCollection = chart.Chart.SeriesCollection();
//Данные выборки
Excel.Series defaultSeries = seriesCollection.NewSeries();
defaultSeries.ChartType = Excel.XlChartType.xlXYScatter;
defaultSeries.XValues = worksheet.Range["A1", "A" + n];
defaultSeries.Values = worksheet.Range["B1", "B" + n];
defaultSeries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;
//Данные полученных значений
var plotedSeries = seriesCollection.NewSeries();
plotedSeries.XValues = worksheet.Range["C1", "C" + n];
plotedSeries.Values = worksheet.Range["D1", "D" + n];
plotedSeries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;
chartPage.HasLegend = true;
chartPage.Legend.LegendEntries(1).Delete();
chartPage.Legend.LegendEntries(1).Delete();
var counter = 1;
foreach (Excel.Series series in chartPage.SeriesCollection())
{
series.Name = counter++ == 3 ? "Данные выборки" : "Функция регрессии";
}
chartPage.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
foreach (Excel.ChartObject co in excelCharts)
{
co.Select();
Excel.Chart thisChart = (Excel.Chart)co.Chart;
thisChart.Export(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + @"\chart.png", "PNG", false);
}
workbook.Close(false, misValue, misValue);
graph.Quit();
}
private void button6_Click(object sender, EventArgs e)
{
string outputPath1 = @"C:\Users\ReaLBERG\Desktop\3 курс\АИС\Отчеты\Ценабонусы" + Path.GetRandomFileName() + ".xlsx";
KPgamenotebookContext db2 = new KPgamenotebookContext();
int num2 = db2.Model.Count();
var models = db2.Model;
Model[] gameNotebooks2 = new Model[num2];
int p = 1;
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workBook;
Excel.Worksheet workSheet;
workBook = excelApp.Workbooks.Add();
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
int[] vs = new int[num2];
int[] vs2 = new int[num2];
int k = 0;
foreach (var t in models)
{
int value;
int value1;
int.TryParse(string.Join("", t.Price.Where(c => char.IsDigit(c))), out value);
int.TryParse(string.Join("", t.Bonuses.Where(c => char.IsDigit(c))), out value1);
vs[k] = value;
vs2[k] = value1;
k++;
}
int temp;
for (int i = 0; i < vs.Length - 1; i++)
{
for (int j = i + 1; j < vs.Length; j++)
{
if (vs[i] > vs[j])
{
temp = vs[i];
vs[i] = vs[j];
vs[j] = temp;
temp = vs2[i];
vs2[i] = vs2[j];
vs2[j] = temp;
}
}
}
for (p = 1; p < 24; p++)
{
workSheet.Cells[1, p] = (vs[p - 1]);
workSheet.Cells[2, p] = (vs2[p - 1]);
}
Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet.ChartObjects();
Excel.ChartObject chartObj = chartObjs.Add(10, 50, 500, 500);
Excel.Chart xlChart = chartObj.Chart;
xlChart.ChartType = Excel.XlChartType.xlAreaStacked;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)xlChart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.NewSeries();
series.XValues = workSheet.get_Range("A1", "Q1");
series.Values = workSheet.get_Range("A2", "Q2");
xlChart.HasTitle = true;
xlChart.ChartTitle.Text = "Зависимость бонусов от цены";
xlChart.HasLegend = false;
excelApp.Visible = true;
excelApp.UserControl = true;
workSheet.SaveAs(outputPath1);
object misValue = System.Reflection.Missing.Value;
xlChart.Export("C:\\Users\\ReaLBERG\\Desktop\\3 курс\\АИС\\Graf.bmp", "BMP", misValue);
excelApp.Quit();
Word cOMFormatter = new Word(@"C:\Users\ReaLBERG\Desktop\3 курс\АИС\Отчеты\lb9tt.doc");
KPgamenotebookContext db = new KPgamenotebookContext();
Console.WriteLine("Пожалуйста, введите имя: ");
string name = Console.ReadLine();
cOMFormatter.Replace("{Имя}", name);
int num = db.Model.Count();
string[] start = new string[3] {
"ID", "Название", "Цена"
};
var gamenotebook = db.Model;
Model[] gameNotebooks = new Model[num];
int y = 0;
foreach (var t in gamenotebook)
{
gameNotebooks[y] = t;
y++;
}
cOMFormatter.TableCreate(num + 1, 3, start, gameNotebooks);
cOMFormatter.Close();
}
/// <summary>
/// parse an excel document and build a kinesis document model
/// </summary>
/// <param name="path">full path of the excel document</param>
/// <returns>equivalent kinesis document model</returns>
public Document ParseNewDocumentCharts(String path, ProcessingProgress pp, Document document)
{
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.OverallOperationName = "All Document Charts";
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
//directory where all the data will be saved
String folderName = document.Location;
String documentPath = System.IO.Path.Combine(DocumentService.TEMP_DIRECTORY, folderName);
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.CurrentOperationName = "Opening MS Office";
pp.CurrentOperationTotalElements = 1;
pp.CurrentOperationElement = 0;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
//open the given excel document
Workbook workbook = excelApplication.Workbooks.Open(path, ReadOnly: true);
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.CurrentOperationElement = 1;
pp.OverallOperationTotalElements = EvaluateWorkbook(workbook, pp);
pp.OverallOperationElement = 0;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
//for every sheet
for (int i = 1; i <= workbook.Sheets.Count; i++)
{
Worksheet worksheet = workbook.Sheets[i];
//create a new page
KineSis.ContentManagement.Model.Page page = document.Pages[i - 1];
//check if chart generation is wanted
if (DocumentService.CHART_HORIZONTAL_FACES > 0)
{
//get charts
ChartObjects chartObjects = worksheet.ChartObjects(Type.Missing);
//create directory for charts
String chartPath = System.IO.Path.Combine(documentPath, "charts");
System.IO.Directory.CreateDirectory(chartPath);
//for every chart
for (int j = 1; j <= chartObjects.Count; j++)
{
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.CurrentOperationName = "Page " + i + " / Chart " + j + " of " + chartObjects.Count;
pp.CurrentOperationTotalElements = EvaluateChart(chartObjects.Item(j).Chart);
pp.CurrentOperationElement = 0;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
KineSis.ContentManagement.Model.Chart mChart = new KineSis.ContentManagement.Model.Chart();
//current chart
Microsoft.Office.Interop.Excel.Chart chart = chartObjects.Item(j).Chart;
mChart.SetThumbnailUrl(GenerateThumbnail(chart, chartPath + DD + i + _ + j + "_thumb"));
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.OverallOperationElement++;
pp.CurrentOperationElement++;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
if (DocumentService.FORCE_CHART_SIZE)
{
chart.ChartArea.Height = ((float)DocumentService.CHART_WIDTH * chart.ChartArea.Height) / chart.ChartArea.Width;
chart.ChartArea.Width = DocumentService.CHART_WIDTH;
}
int chartType = GetChartType(chart);
//start from 0 point
chart.Rotation = 0;
int horizontalAngle = 0;
//depending on how many horizontal faces are required, calculate the angle between them
if (DocumentService.CHART_HORIZONTAL_FACES > 0)
{
horizontalAngle = 360 / DocumentService.CHART_HORIZONTAL_FACES;
}
int verticalAngle = 0;
//depending on how many vertical faces are required for a horizontal face, celaculate the angle between them, excluding the vertical face at 90 degrees
if (DocumentService.CHART_VERTICAL_FACES > 0)
{
verticalAngle = 90 / (DocumentService.CHART_VERTICAL_FACES + 1);
}
if (chart.HasTitle)
{
mChart.Title = chart.ChartTitle.Caption;
}
else
{
mChart.Title = chart.Name;
}
//does not support rotation (it's plain)
if (chartType == 0)
{
//if horizontal faces number is 0, then no chart will be outputed
if (DocumentService.CHART_HORIZONTAL_FACES > 0)
{
ChartHorizontalView hView = new ChartHorizontalView();
//draw chart face as image
chart.Export(chartPath + DD + i + _ + j + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);
//add to hView
hView.ImageUrl = chartPath + DD + i + _ + j + DocumentService.IMAGE_EXTENSION;
//add to views
mChart.Views.Add(hView);
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.OverallOperationElement++;
pp.CurrentOperationElement++;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
}
}
else
{
//for every horizontal face
for (int k = 0; k < DocumentService.CHART_HORIZONTAL_FACES; k++)
{
ChartHorizontalView hView = new ChartHorizontalView();
//reset elevation
chart.Elevation = 0;
//export face as image
chart.Export(chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);
//set bitmap to view
hView.ImageUrl = chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.OverallOperationElement++;
pp.CurrentOperationElement++;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
//for every vertical face
for (int l = 0; l < DocumentService.CHART_VERTICAL_FACES; l++)
{
ChartVerticalView vView = new ChartVerticalView();
//increse elevation
chart.Elevation += verticalAngle;
//export face as image
chart.Export(chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);
//set bitmap to view
vView.ImageUrl = chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION;
//add vertical view to horizontal UP list
hView.Up.Add(vView);
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.OverallOperationElement++;
pp.CurrentOperationElement++;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
}
//some chart types, like 3D pie, does not support elevation less than 0
if (SupportsNegativeElevation(chart))
{
//reset elevation
chart.Elevation = 0;
//for every vertical face
for (int m = 0; m < DocumentService.CHART_VERTICAL_FACES; m++)
{
ChartVerticalView vView = new ChartVerticalView();
//decrease elevation
chart.Elevation -= verticalAngle;
//export face as image
chart.Export(chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);
//set bitmap to vertical view
vView.ImageUrl = chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION;
//add vertical view to horizontal view DOWN list
hView.Down.Add(vView);
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.OverallOperationElement++;
pp.CurrentOperationElement++;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
}
}
//increase horizontal angle in order to get the next horizontal view
chart.Rotation += horizontalAngle;
//add horizontal view to the chat's views list
mChart.Views.Add(hView);
}
}
//add chart to page
page.Charts.Add(mChart);
}
}
}
//close workbook without saving any possible changes (this way the "Are you sure?" or "Save changes?" dialogs will be supressed)
workbook.Close(SaveChanges: false);
CloseOfficeApplication();
//return the built document
return(document);
}
private void graph(bool threeD)
{
loadSteps(threeD);
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
int max = (int)((end - start) / interval);
int ymax = (int)((yend - ystart) / yinterval);
int index = 1;
progressBar1.Maximum = max;
for (int i = 1; i <= max; i++)
{
progressBar1.Value = i;
progressBar1.Refresh();
double x = i * interval + start;
for (int j = 1; j <= ymax; j++)
{
double y = j * yinterval + ystart;
string xlEquation = "=";
foreach (char c in T_Equation.Text)
{
Application.DoEvents();
if (c == 'X' || c == 'x')
{
xlEquation += x;
}
else if (threeD && (c == 'Y' || c == 'y'))
{
xlEquation += y;
}
else
{
xlEquation += c;
}
}
++index;
try
{
if (!threeD)
{
xlWorkSheet.Cells[index, 1] = x.ToString();
xlWorkSheet.Cells[index, 2] = xlEquation;
}
else
{
xlWorkSheet.Cells[1, j + 1] = y.ToString();
xlWorkSheet.Cells[i + 1, 1] = x.ToString();
xlWorkSheet.Cells[i + 1, j + 1] = xlEquation;
}
}
catch (Exception ex)
{
MessageBox.Show("Invalid equation.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
Excel.Range chartYRange, chartXRange, chartZRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(0, 0, 465, 330);
Excel.Chart chartPage = myChart.Chart;
int last = max * ymax;
chartXRange = xlWorkSheet.get_Range("A1", "A" + last);
chartYRange = xlWorkSheet.get_Range("B1", "B" + last);
if (!threeD)
{
chartPage.SetSourceData(chartYRange, misValue);
}
else
{
try
{
char endC = System.Convert.ToChar(Convert.ToInt32('B') + ymax - 1);
chartZRange = xlWorkSheet.get_Range("A1", endC + "" + max);
chartPage.SetSourceData(chartZRange, misValue);
}
catch (Exception ex) {
MessageBox.Show("Chart y-range too large.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
chartPage.ChartType = (threeD) ? Excel.XlChartType.xlSurface : Excel.XlChartType.xlLine;
chartPage.Legend.Clear();
if (!threeD)
{
chartPage.SeriesCollection(1).XValues = chartXRange;
}
Excel.Axis yaxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
yaxis.HasTitle = true;
yaxis.AxisTitle.Text = "Y";
Excel.Axis xaxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xaxis.HasTitle = true;
xaxis.AxisTitle.Text = "X";
if (threeD)
{
Excel.Axis zAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
zAxis.HasTitle = true;
zAxis.AxisTitle.Text = "Z";
}
//export chart as picture file
if (pictureBox1.Image != null)
{
pictureBox1.Image.Dispose();
pictureBox1.Image = null;
}
chartPage.Export(AppDomain.CurrentDomain.BaseDirectory + "test.bmp", "BMP", misValue);
//load picture to picturebox
pictureBox1.Image = new Bitmap(AppDomain.CurrentDomain.BaseDirectory + "test.bmp");
xlWorkBook.Close(saveXL, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
releaseObject(chartPage);
}
private void GenerateReportTopGenres(Dictionary <string, double> dictionary)
{
excel.Application excelApp = new excel.Application();
excel.Workbook workbook;
excel.Worksheet workSheet;
workbook = excelApp.Workbooks.Add();
//workSheet = (excel.Worksheet)workbook.Worksheets.get_Item(1);
workSheet = workbook.ActiveSheet;
int i = 1;
foreach (var word in dictionary.OrderByDescending(q => q.Value))
{
workSheet.Cells[1, i] = word.Key;
workSheet.Cells[2, i] = word.Value;
i++;
}
excel.Range Erange = workSheet.Range["B3"];
Erange.Formula = "=SUM(A2:J2)";
Erange.FormulaHidden = false;
excel.Borders border = Erange.Borders;
border.LineStyle = excel.XlLineStyle.xlContinuous;
excel.ChartObjects chObs = (excel.ChartObjects)workSheet.ChartObjects();
excel.ChartObject chOb = chObs.Add(5, 50, 300, 300);
excel.Chart xlchart = chOb.Chart;
excel.Range Erange2 = workSheet.Range["A1:J1"];
excel.Range Erange3 = workSheet.Range["A3:J1"];
xlchart.ChartType = excel.XlChartType.xlColumnClustered;
excel.SeriesCollection seriesCollection = (excel.SeriesCollection)xlchart.SeriesCollection(Type.Missing);
excel.Series series = seriesCollection.NewSeries();
//series.XValues = workSheet.Range["A1:J1"];
string[] matrix = new string[10];
for (int j = 0; j < 10; j++)
{
matrix[j] = (string)(workSheet.Cells[1, j + 1] as excel.Range).Value;
}
//series.XValues = workSheet.Range[workSheet.Cells[1, 3]];
series.XValues = matrix;
series.Values = workSheet.get_Range("A2", "J2");
xlchart.HasTitle = true;
xlchart.ChartTitle.Text = "Жанры и их рейтинги";
xlchart.HasLegend = true;
series.Name = "Жанры";
excelApp.Visible = true;
excelApp.UserControl = true;
string outputPath = @"C:\Users\acer\Desktop\Учеба\АИС\Отчет по рейтингу жанров (" + Guid.NewGuid().ToString() + ").xlsx";
workbook.SaveAs(outputPath);
object misValue = System.Reflection.Missing.Value;
xlchart.Export("C:\\Users\\acer\\Desktop\\Учеба\\АИС\\Graf.bmp", "BMP", misValue);
string path = @"C:\Users\acer\Desktop\Учеба\АИС\Отчет по рейтингу жанров.doc";
WordReportTopBookOfGenre wordReport = new WordReportTopBookOfGenre(path);
wordReport.GenerateReportTopGenres(dictionary);
excelApp.Quit();
}
public async Task <byte[]> GenerateChartImageFromXlsxFile <TListEntity>(string excelChartTemplateName, string outPutFolder, IEnumerable <TListEntity> inputItems, int seriesCount, int xAxisDataCount)
{
if (inputItems != null && inputItems.Count() > 0)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
outPutFolder = System.Configuration.ConfigurationManager.AppSettings["ChartOutputFolder"].ToString(); //"C:\\ReportsGenerated\\Chart\\OutPuts";
excelChartTemplateName = System.Configuration.ConfigurationManager.AppSettings["ChartTemplate"].ToString(); //"C:\\ReportsGenerated\\Chart\\InPuts\\FinalTemplate_Test_good_series_xaxis_1.xlsx"; //
Console.WriteLine(excelChartTemplateName);
Console.WriteLine(outPutFolder);
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
//xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkBook = xlApp.Workbooks.Open(excelChartTemplateName);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
try
{
string[] properties = new string[] { "Name", "ValuePrev1", "ValuePrev2", "ValuePrev3", "ValuePrev4", "ValuePrev5", "ValuePrev6" };
List <TListEntity> items = new List <TListEntity>();
foreach (var item in inputItems)
{
items.Add(item);
}
Excel.Range chartRangeXaxis;
Excel.Range chartRange;
var rangeToValue = "";
var xAxisRangeToValue = "";
int i = 0;
var h = 0;
int[] alphabetNumArray = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26 };
int i_clear = 0;
var h_clear = 0;
//clearing existing values
for (; i_clear < 4; i_clear++) //rows clearing
{
for (; h_clear < 4; h_clear++) //columns clearing
{
xlWorkSheet.Cells[(i_clear + 1), alphabetNumArray[h_clear]] = "";
}
}
for (i = 0; i < items.Count(); i++)
{
for (h = 0; h < xAxisDataCount + 1; h++)
{
string propertyName = properties[h];
var property = items[i].GetType().GetProperty(propertyName);
if (property != null)
{
var value = property.GetValue(items[i], null);
if (value != null)//required to check, for not allowing empty values
{
xlWorkSheet.Cells[(i + 1), alphabetNumArray[h]] = value;
//xlWorkSheet.Cells["A2"] = value;//this type was not works on microsoft.office.interop.excel
//xlWorkSheet.Cells[1,2] = value;//this is correct one
}
//else
// xlWorkSheet.Cells[(i + 1), alphabetNumArray[h]] = "";
}
}
}
string[] alphabetArray = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
rangeToValue = alphabetArray[xAxisDataCount] + i;
xAxisRangeToValue = alphabetArray[xAxisDataCount] + 1;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
//Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);//for new one
Excel.ChartObject chartObject = (Excel.ChartObject)xlCharts.Item(1);
Excel.Chart chart = chartObject.Chart;
chartRangeXaxis = xlWorkSheet.get_Range("B1", xAxisRangeToValue);
chartRange = xlWorkSheet.get_Range("A2", rangeToValue);
//chartPage.SetSourceData(chartRangeXaxis, Excel.XlRowCol.xlRows);
chart.SetSourceData(chartRange, Excel.XlRowCol.xlRows);//sucess
//chartPage.ChartType = Excel.XlChartType.xlLineMarkers;//if creating new chart uncomment this one
Excel.Axis xAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis.HasTitle = false;
//xAxis.AxisTitle.Caption = "Time";
xAxis.CategoryNames = (Excel.Range)xlWorkSheet.get_Range("B1", xAxisRangeToValue);
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("B2", rangeToValue);
formatRange.NumberFormat = "#,##0.00";
//chartPage.ChartArea.Width = 200;
//chartPage.ChartArea.Height = 100;
// chartPage.CategoryLabelLevel = Excel.XlCategoryLabelLevel.xlCategoryLabelLevelCustom;
//not working properly
//Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection();
//Excel.Series series = seriesCollection.NewSeries();
//series.XValues = chartRangeXaxis;
//have to set for data labels number format as 0.00
//var series = chartObject.Chart.SeriesCollection() as Excel.SeriesCollection;
//foreach (var ser in series)
//{
// var DataLabels = ((Excel.Series)ser).DataLabels(1);
// DataLabels.
//}
var imageFileFullName = outPutFolder + @"\ChartImage_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".png";
chart.Export(imageFileFullName, "PNG", false);
try
{
//xlWorkBook.SaveAs(outPutFolder + @"\Excel_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".xlsx", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.SaveAs(outPutFolder + @"\Excel_" + DateTime.Now.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond.ToString(), Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);
//xlWorkBook.SaveAs(outPutFolder + @"\Excel_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".xlsx", Excel.XlFileFormat.xlXMLSpreadsheet, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
await this.releaseObject(xlWorkSheet);
await this.releaseObject(xlWorkBook);
await this.releaseObject(xlApp);
}
catch (Exception ex)
{
Console.WriteLine("Error closing Excel object" + ex.Message);
Console.WriteLine(ex.StackTrace);
}
finally
{
if (xlWorkSheet != null)
{
Marshal.ReleaseComObject(xlWorkSheet);
}
if (xlWorkBook != null)
{
Marshal.ReleaseComObject(xlWorkBook);
}
if (xlApp != null)
{
Marshal.ReleaseComObject(xlApp);
}
}
try
{
Console.WriteLine("Reading image");
byte[] byteArray = File.ReadAllBytes(imageFileFullName);
Console.WriteLine("Reading image 1");
System.IO.DirectoryInfo di = new DirectoryInfo(outPutFolder);
foreach (FileInfo file in di.GetFiles())
{
file.Delete();
}
return(byteArray);
}
catch (Exception ex)
{
Console.WriteLine("Failed to delete " + ex.Message);
}
return(null);
}
catch (Exception ex)
{
await this.releaseObject(xlWorkSheet);
await this.releaseObject(xlWorkBook);
await this.releaseObject(xlApp);
}
}
return(null);
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
xlWorkSheet.Cells[1, 1] = "";
xlWorkSheet.Cells[1, 2] = "Student1";
xlWorkSheet.Cells[1, 3] = "Student2";
xlWorkSheet.Cells[1, 4] = "Student3";
xlWorkSheet.Cells[2, 1] = "Term1";
xlWorkSheet.Cells[2, 2] = "80";
xlWorkSheet.Cells[2, 3] = "65";
xlWorkSheet.Cells[2, 4] = "45";
xlWorkSheet.Cells[3, 1] = "Term2";
xlWorkSheet.Cells[3, 2] = "78";
xlWorkSheet.Cells[3, 3] = "72";
xlWorkSheet.Cells[3, 4] = "60";
xlWorkSheet.Cells[4, 1] = "Term3";
xlWorkSheet.Cells[4, 2] = "82";
xlWorkSheet.Cells[4, 3] = "80";
xlWorkSheet.Cells[4, 4] = "65";
xlWorkSheet.Cells[5, 1] = "Term4";
xlWorkSheet.Cells[5, 2] = "75";
xlWorkSheet.Cells[5, 3] = "82";
xlWorkSheet.Cells[5, 4] = "68";
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "C12");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlConeCol; //xlCylinderCol;//xlLine;//xlColumnClustered;
//export chart as picture file
chartPage.Export(@"H:\img\excel_chart_export.png", "PNG", misValue);
xlWorkBook.SaveAs(@"H:\img\csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
//xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Open(fileUrl);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
//Left, Top, Width, Height
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(0, 0, 1000, 600);
Excel.Chart chartPage = myChart.Chart;
int lastRow = xlWorkSheet.UsedRange.Rows.Count;
chartRange = xlWorkSheet.get_Range("B1", "D" + lastRow);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers;
Excel.Axis horizAxis = chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
horizAxis.MaximumScaleIsAuto = false;
horizAxis.MaximumScale = lastRow;
horizAxis.MinimumScaleIsAuto = false;
horizAxis.MinimumScale = 0;
horizAxis.HasTitle = false;
//horizAxis.AxisTitle.Text = "across the bottom";
Excel.Axis vertAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
vertAxis.HasMajorGridlines = false; // change this to whatever you wish
vertAxis.HasTitle = true;
vertAxis.AxisTitle.Text = "Volts";
vertAxis.MaximumScaleIsAuto = true;
//vertAxis.MaximumScale = 4; // you can pick this based on your input
vertAxis.MinimumScaleIsAuto = true;
// vertAxis.MinimumScale = -4;
//chartPage.ApplyChartTemplate("c:\\ctmp.crtx");
chartPage.Export(fileDir + "/Chart1.PNG", "PNG", false);
Image image = Image.FromFile(fileDir + "/Chart1.PNG");
pictureBox1.Image = image;
pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage;
//xlWorkBook.SaveAs(fileUrlNoExt, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
private void btnChart_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
xlWorkSheet.Cells[1, 2] = "Overview";
xlWorkSheet.Cells[1, 3] = "Time";
xlWorkSheet.Cells[2, 1] = "Planned \n" + toTime(totalPlanned).ToString();
xlWorkSheet.Cells[2, 2] = totalPlanned;
xlWorkSheet.Cells[3, 1] = "Not Planned \n" + toTime(totalNotPlanned).ToString();
xlWorkSheet.Cells[3, 2] = totalNotPlanned;
xlWorkSheet.Cells[4, 1] = "Auto Mode \n" + toTime(totalAutoMode).ToString();
xlWorkSheet.Cells[4, 2] = totalAutoMode;
xlWorkSheet.Cells[4, 3] = toTime(totalAutoMode).ToString();
//xlWorkBook.SaveAs("csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "c4");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlPie;
//chartPage.SeriesCollection(2).Format.Fill.ForeColor.RGB = System.Drawing.Color.Red.ToArgb();
//chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
// if (!File.Exists(@"C:\excel_chart_export.bmp"))
//{
chartPage.Export(@"C:\excel_chart_export.bmp", "BMP", misValue);
//}
//export chart as picture file
//xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(false, misValue, misValue);
xlApp.Quit();
this.Hide();
Image img;
using (var bmpTemp = new Bitmap(@"C:\excel_chart_export.bmp"))
{
img = new Bitmap(bmpTemp);
}
using (Form form = new Form())
{
form.StartPosition = FormStartPosition.CenterScreen;
form.Size = new Size(510, 460);
PictureBox pb = new PictureBox();
pb.Dock = DockStyle.Fill;
pb.Image = img;
form.Controls.Add(pb);
form.ShowDialog();
}
this.Show();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
releaseObject(chartPage);
File.Delete(@"C:\excel_chart_export.bmp");
}
private static void ExcelGraphGeneration()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook
.Worksheets.get_Item(1);
// Add data columns
xlWorkSheet.Cells[1, 1] = "SL";
xlWorkSheet.Cells[1, 2] = "Name";
xlWorkSheet.Cells[1, 3] = "CTC";
xlWorkSheet.Cells[1, 4] = "DA";
xlWorkSheet.Cells[1, 5] = "HRA";
xlWorkSheet.Cells[1, 6] = "Conveyance";
xlWorkSheet.Cells[1, 7] = "Medical Expenses";
xlWorkSheet.Cells[1, 8] = "Special";
xlWorkSheet.Cells[1, 9] = "Bonus";
xlWorkSheet.Cells[1, 10] = "TA";
xlWorkSheet.Cells[1, 11] = "TOTAL";
xlWorkSheet.Cells[1, 11] = "Contribution to PF";
xlWorkSheet.Cells[1, 12] = "Profession Tax";
xlWorkSheet.Cells[1, 13] = "TDS";
xlWorkSheet.Cells[1, 14] = "Salary Advance";
xlWorkSheet.Cells[1, 15] = "TOTAL";
xlWorkSheet.Cells[1, 16] = "NET PAY";
Excel.Application xlApp1 = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp1.Workbooks.Open
(@"C:\Users\v-mapall\source\repos\ExcelCustomApps\AppServicesForExcel\Sample Data\Sample Data2.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
//for (int i = 1; i <= rowCount; i++)
for (int i = 1; i <= 2; i++)
{
for (int j = 1; j <= colCount; j++)
{
Console.WriteLine(xlRange.Cells[i, j].Value2.ToString());
xlWorkSheet.Cells[i, j] = xlRange.Cells[i, j]
.Value2.ToString();
}
}
//Console.ReadLine();
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)
xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)
xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "R22");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
// Export chart as picture file
chartPage.Export(@"C:\Users\v-mapall\source\repos\ExcelCustomApps\AppServicesForExcel\Sample Data\EmployeeExportData.pdf",
"PDF", misValue);
xlWorkBook.SaveAs("EmployeeExportData.xls",
Excel.XlFileFormat.xlWorkbookNormal, misValue,
misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue,
misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
DeallocateObject(xlWorkSheet);
DeallocateObject(xlWorkBook);
DeallocateObject(xlApp);
DeallocateObject(xlApp1);
}
static void Main(string[] args)
{
// set english culture (for english function names and . decimal)
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");
// list seperator is now , Trying to change it to ; doesnt work :(
// System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator = ";";
// show manual
bool showMan = false;
// check pipe
String pipedText = "";
bool isKeyAvailable;
bool piped = false;
try { isKeyAvailable = System.Console.KeyAvailable; }
catch { pipedText = System.Console.In.ReadToEnd(); piped = true; }
// if no args or pipe, show manual
if (piped == false && args.Length == 0)
{
showMan = true;
}
// set default values
string infile = "";
string paste = "";
List <string> macro = new List <string>();
int[] cellA = new int[2] {
0, 1
};
int[] cellB = new int[2] {
0, 1
};
int[] outCellA = new int[2] {
0, 1
};
int[] outCellB = new int[2] {
0, 0
};
string sheet = "";
string active = "";
// warnings are off by default, since excel will warn about ANYTHING, which interupts the program and leads to errors.
// f.eks. log charts will make the program fail, when they are given blank data in the step before new data is provided.
bool warning = false;
// save on exit
bool save = true;
// convert error codes to text in output
bool outErr = true;
// paste as text
bool asText = false;
// blehh..
string errLine = "--------------------------------------------------------------------------------";
// tab as default output space character
string cellSpacer = "\t";
// all charts that should be saved
List <string> charts = new List <string>();
// check input arguments
int iarg = 0;
for (int i = 0; i < args.Length; i++)
{
if (args[i].StartsWith("-"))
{
// show manual
if (args[i] == "-help" || args[i] == "--help" || args[i] == "-?")
{
showMan = true;
}
// specify macro to run
if (args[i] == "-m")
{
try {
macro.Add(args[i + 1]);
i++;
}
catch {
Error("No macro name given for -m.", 1);
}
}
// paste input as text?
if (args[i] == "-t")
{
asText = true;
}
// dont save
if (args[i] == "-n")
{
save = false;
}
// hide warnings
if (args[i] == "-w")
{
warning = true;
}
// set space character
if (args[i] == "-b")
{
cellSpacer = " ";
if (args.Length > i + 1)
{
if (args[i + 1].Length == 1)
{
cellSpacer = args[i + 1];
i++;
}
}
}
// set paste sheet
if (args[i] == "-p")
{
try
{
active = args[i + 1];
i++;
}
catch
{
Error("No paste name given for -p.", 1);
}
}
// set output sheet
if (args[i] == "-s")
{
try
{
sheet = args[i + 1];
i++;
}
catch
{
Error("No sheet name given for -s.", 1);
}
}
// blank errors
if (args[i] == "-#")
{
outErr = false;
}
}
else
{
// excel file
if (iarg == 0)
{
infile = args[i];
}
// paste file
else if (iarg == 1 && !piped)
{
paste = args[i];
if (paste == "~" || paste == "")
{
paste = "";
iarg++; iarg++;
}
}
// input cell ref
else if (iarg == 2)
{
string[] cellArr = args[i].Split(':');
if (cellArr.Length == 1)
{
cellA = ExcelCellRef(cellArr[0]);
}
else
{
cellA = ExcelCellRef(cellArr[0]);
cellB = ExcelCellRef(cellArr[1]);
iarg++;
}
}
else if (iarg == 3)
{
cellB = ExcelCellRef(args[i]);
}
// output cell ref
else if (iarg == 4)
{
string[] cellArr = args[i].Split(':');
if (cellArr.Length == 1)
{
outCellA = ExcelCellRef(cellArr[0]);
}
else
{
outCellA = ExcelCellRef(cellArr[0]);
outCellB = ExcelCellRef(cellArr[1]);
iarg++;
}
}
else if (iarg == 5)
{
outCellB = ExcelCellRef(args[i]);
}
// output charts
else if (iarg > 5)
{
charts.Add(args[i]);
}
iarg++;
}
}
// Print header
if (showMan)
{
Print(@"Usage: excel [OPTIONS] ExcelFile PasteFile Cell1 Cell2 OutCell1 OutCell2 [Chart1 [Chart2 ..]]
or: excel =FORMULA
Opens ExcelFile and places the contents of PasteFile from position given.
Position is the range between Cell1 and Cell2. Unused cells are cleared.
Echos all filled rows in the range between OutCell1 and OutCell2.
Any charts named will be saved to <ExcelFile_ChartN>.png.
'Sheet.ChartN' can be used if multiple charts has the same name.
-p PasteSheet Select the sheet that should be pasted to.
-s OutSheet Select the sheet that should be outputted.
-m Macro Run macro after paste. If -m is used multiples
times, more than 1 macro can be executed.
-n Do not save workbook
-w display Excel dialogs. Default is to surpress.
-# Replace errors with blanks in output
-b [CHAR] Set cell-spacing character in output to
'space' or 'CHAR' (default is 'tab')
-t Insert PasteFile as text instead of numbers
If '~' is specified as PasteFile no file is loaded and Cell1 and Cell2
should not be specified.
Version 1.0. Report bugs to <*****@*****.**>");
Environment.Exit(0);
}
if (infile.StartsWith("="))
{
string result = "";
try
{
result = ExcelMath.Calc(infile);
}
catch (Exception ex) {
Console.Error.WriteLine("Error: " + ex.Message);
#if DEBUG
Console.ReadKey();
#endif
Environment.Exit(1);
}
Print(result);
#if DEBUG
Console.ReadKey();
#endif
Environment.Exit(0);
}
// open file
if (piped == false && infile == "")
{
Error("No file given.", 1);
}
System.IO.TextReader stream = new StringReader(pipedText);
if (!piped && paste.Length > 0)
{
try { stream = new StreamReader(paste); }
catch (Exception e) { Error("Unable to open file: " + paste + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
}
// number of lines and columns
int N = 0;
int C = 0;
// input data, as numbers and text. only one will be used
double[,] cells = null;
string[,] sCells = null;
// if pasted text
if (paste.Length > 0)
{
String line;
List <string[]> strings = new List <string[]>();
// trim all lines and split between words
while ((line = stream.ReadLine()) != null)
{
line = line.Replace(",", " ");
line = line.Replace("\t", " ");
line = line.Trim();
line = System.Text.RegularExpressions.Regex.Replace(line, @"\s+", " ");
if (asText)
{
//if (line.Length > 0)
strings.Add(line.Split(' '));
}
else if (line.Length > 0 && !line.StartsWith("#"))
{
strings.Add(line.Split(' '));
}
}
// set number of rows and columns
N = strings.Count();
C = 0;
foreach (string[] str in strings)
{
if (str.Length > C)
{
C = str.Length;
}
}
// convert input data to a format the Excel-interop understands: var[,]
if (asText)
{
sCells = new string[N, C];
}
else
{
cells = new double[N, C];
}
// parse all cells and add to array
for (int i = 0; i < N; i++)
{
for (int j = 0; j < strings[i].Length; j++)
{
try
{
if (asText)
{
sCells[i, j] = strings[i][j];
}
else
{
cells[i, j] = double.Parse(strings[i][j]);
}
}
catch (Exception e) { Error("Unable to parse number in paste file, line " + (i + 1) + ", column " + (j + 1) + ":\n" + strings[i][j] + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
}
}
}
Excel.Range startCell;
Excel.Range endCell;
try
{
// open excel app
oXL = new Excel.Application();
if (!warning)
{
oXL.DisplayAlerts = false;
}
try
{
// try to open the selected excel file
// we turn of errors, since excel prompts for macro-enabled files and other things
oXL.DisplayAlerts = false;
// we need the absolute file path, since excel defaults to the user home dir, not the current working dir :S
oWB = oXL.Workbooks.Open(Path.GetFullPath(infile));
// turn back on warnings if wanted
if (warning)
{
oXL.DisplayAlerts = true;
}
}
catch (Exception e) { throw new System.Exception("Unable to open file: " + Path.GetFullPath(infile) + "\n\n" + errLine + "\n\n" + e.ToString()); }
// set the active sheet
if (active.Length > 0)
{
try
{
oSheet = (Excel._Worksheet)oWB.Sheets[active];
}
catch (Exception e) { throw new System.Exception("Unable to select worksheet: " + active + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
else
{
oSheet = (Excel._Worksheet)oWB.Worksheets[1];
}
// insert data
if (paste.Length > 0)
{
// if only columns are specified, find the amount of rows used
if (cellA[0] == 0 && cellB[0] == 0)
{
string cell = GetExcelColumnName(cellA[1]) + ":" + GetExcelColumnName(cellB[1]);
Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
cellA[0] = 1;
cellB[0] = r.Rows.Count;
}
// select and paste values
try
{
startCell = (Excel.Range)oSheet.Cells[cellA[0], cellA[1]];
endCell = (Excel.Range)oSheet.Cells[cellB[0], cellB[1]];
oSheet.get_Range(startCell, endCell).Value = null;
endCell = (Excel.Range)oSheet.Cells[cellA[0] + N - 1, cellA[1] + C - 1];
if (asText)
{
oSheet.get_Range(startCell, endCell).Value2 = sCells;
}
else
{
oSheet.get_Range(startCell, endCell).Value2 = cells;
}
}
catch (Exception e)
{
string inputCell = GetExcelColumnName(cellA[1]) + cellA[0] + ":" + GetExcelColumnName(cellB[1]) + cellB[0];
throw new System.Exception("Unable to select input cells:\n\n " + inputCell + "\n\n" + errLine + "\n\n" + e.ToString());
}
}
// run macro
for (int i = 0; i < macro.Count; i++)
{
try
{
oXL.Run(macro[i]);
}
catch (Exception e) { throw new System.Exception("Unable to run macro: " + macro[i] + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
// force workbook refresh
oXL.Calculate();
// go to result sheet
if (sheet.Length > 0)
{
try
{
oSheet = (Excel._Worksheet)oWB.Sheets[sheet];
}
catch (Exception e) { throw new System.Exception("Unable to select output sheet:" + sheet + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
// save charts
foreach (Excel.Worksheet cSheet in oWB.Worksheets)
{
// loop trough all charts
Excel.ChartObjects xlCharts = (Excel.ChartObjects)cSheet.ChartObjects(Type.Missing);
for (int i = 1; i <= xlCharts.Count; i++)
{
Excel.ChartObject oChart = (Excel.ChartObject)xlCharts.Item(i);
Excel.Chart chart = oChart.Chart;
string chartName = "";
if (charts.Contains(cSheet.Name + "." + oChart.Name))
{
chartName = cSheet.Name + "." + oChart.Name;
}
else if (charts.Contains(oChart.Name))
{
chartName = oChart.Name;
}
// if chart is specified for output, save it
if (chartName.Length > 0)
{
int id = charts.FindIndex(s => s == chartName);
charts.RemoveAt(id);
try
{
// we need full path name again.. excel defaults to user home dir...
string saveas = Path.GetFullPath(infile);
saveas = Path.GetDirectoryName(saveas) + "\\" + Path.GetFileNameWithoutExtension(saveas);
saveas = saveas + "_" + chartName + ".png";
chart.Export(saveas, "PNG");
}
catch (Exception e) { throw new System.Exception("Unable to save chart '" + chartName + "':\n\n" + errLine + "\n\n" + e.ToString()); }
}
}
}
// if any charts was not found; throw an error.
if (charts.Count > 0)
{
string list = "";
foreach (string s in charts)
{
list += s + ", ";
}
throw new Exception("Unable to find chart(s): " + list);
}
// if only columns are specified, find amount of rows to use
if (outCellA[0] == 0 && outCellB[0] == 0)
{
if (outCellB[1] == 0)
{
outCellB[1] = oSheet.UsedRange.Columns.Count;
}
string cell = GetExcelColumnName(outCellA[1]) + ":" + GetExcelColumnName(outCellB[1]);
Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
outCellA[0] = 1;
outCellB[0] = r.Rows.Count;
}
// select the output cell range
try
{
startCell = (Excel.Range)oSheet.Cells[outCellA[0], outCellA[1]];
endCell = (Excel.Range)oSheet.Cells[outCellB[0], outCellB[1]];
}
catch (Exception e)
{
string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
throw new System.Exception("Unable to select output cells:\n " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
}
// get output from selected cells
object[,] arr = null;
try
{
Excel.Range r = (Excel.Range)oSheet.get_Range(startCell, endCell);
// if only 1 cell is selected, excel will return an object instead of object array!
if (r.Cells.Count == 1)
{
arr = new object[2, 2];
arr[1, 1] = r.Cells.Value2;
}
else
{
arr = r.Cells.Value2 as object[, ];
}
}
catch (Exception e)
{
string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
throw new System.Exception("Invalid OutCells given. Unable to retrieve data:\n " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
}
List <string> results = new List <string>();
int last = 0;
N = outCellB[0] - outCellA[0] + 1;
C = outCellB[1] - outCellA[1] + 1;
// loop trough output rows
for (int i = 1; i <= N; i++)
{
// loop trough output columns
string s = "";
for (int j = 1; j <= C; j++)
{
// check if cell contains an error
if (arr[i, j] is Int32)
{
if (outErr)
{
int eCode = (int)arr[i, j];
string e = "";
if (eCode == -2146826281)
{
e = "#DIV/0!";
}
else if (eCode == -2146826246)
{
e = "#N/A";
}
else if (eCode == -2146826259)
{
e = "#NAME?";
}
else if (eCode == -2146826288)
{
e = "#NULL!";
}
else if (eCode == -2146826252)
{
e = "#NUM!";
}
else if (eCode == -2146826265)
{
e = "#REF!";
}
else if (eCode == -2146826273)
{
e = "#VALUE!";
}
// no more error codes exists (?) as of 2013.. But to be sure / support future ones:
else
{
e = "#ERR" + eCode.ToString();
}
s = s + e + " " + cellSpacer;
}
else
{
s = s + " " + cellSpacer;
}
}
else if (arr[i, j] != null)
{
s = s + arr[i, j].ToString() + cellSpacer;
}
else
{
s = s + " " + cellSpacer;
}
}
// remove cellspacer from last column
if (C > 0)
{
results.Add(s.Remove(s.Length - 1).TrimEnd());
}
// record last row column with content
if (s.Replace(cellSpacer, " ").TrimEnd().Length > 0)
{
last = results.Count();
}
}
// write output to console
for (int i = 0; i < last; i++)
{
Console.WriteLine(results[i]);
}
// save file
if (save)
{
// if macros are enabled, excel would prompt about saving
oXL.DisplayAlerts = false;
oWB.Save();
}
}
// catch any exception
catch (Exception theException)
{
Error(errLine + "\n Error: " + theException.Message, 1);
}
finally
{
// clean up and exit
CleanUp();
}
#if DEBUG
Console.ReadKey();
#endif
}
public void ExportChartsAsCombined(string item, string imageFile, int imagesPerRow, int splitBoardWidth)
{
if (string.IsNullOrEmpty(item) || string.IsNullOrEmpty(imageFile))
{
throw new ArgumentNullException($"The parameter item or imageFile is null");
}
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)_xlWorkBook.Worksheets.Item[item];
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
IList <string> chartFiles = new List <string>();
string folder = Path.GetDirectoryName(imageFile);
string format = Path.GetExtension(imageFile).TrimStart('.');
foreach (Excel.ChartObject xlChart in xlCharts)
{
Excel.Chart chartPage = xlChart.Chart;
var chartFile = Path.Combine(folder, $"{chartPage.Name}.{format}");
chartFiles.Add(chartFile);
chartPage.Export(chartFile, format, _misValue);
}
var chartImage = chartFiles.FirstOrDefault();
if (chartImage == null)
{
throw new ArgumentNullException($"No chart found");
}
var rows = (int)Math.Ceiling(chartFiles.Count / (decimal)imagesPerRow);
var imageBitmap = new Bitmap(chartImage);
var combinedImageWidth = imageBitmap.Width * imagesPerRow + (imagesPerRow - 1) * splitBoardWidth;
var combinedImageHeight = imageBitmap.Height * rows + (imagesPerRow - 1) * splitBoardWidth;
var combinedImage = new Bitmap(combinedImageWidth, combinedImageHeight);
// copy images row by row
int combinedImageY = 0;
for (int row = 0; row < rows; row++)
{
// copy images one after one in one row
for (int colomn = 0; colomn < imagesPerRow; colomn++)
{
int imageIndex = colomn + row * imagesPerRow;
var image = new Bitmap(chartFiles[imageIndex]);
var combinedImageX = (imageBitmap.Width + splitBoardWidth) * colomn;
for (int y = 0; y < image.Height; y++)
{
// copy image
for (int x = 0; x < image.Width; x++)
{
combinedImage.SetPixel(combinedImageX + x, combinedImageY + y, image.GetPixel(x, y));
}
// draw vertical boarder
for (int i = 0; i < splitBoardWidth; i++)
{
var boarderX = combinedImageX + imageBitmap.Width + i;
if (boarderX < combinedImageWidth)
{
combinedImage.SetPixel(boarderX, combinedImageY + y, Color.Black);
}
}
}
}
combinedImageY += imageBitmap.Height;
// draw horizon boarder
{
if (combinedImageY < combinedImageHeight)
{
for (int x = 0; x < combinedImageWidth; x++)
{
combinedImage.SetPixel(x, combinedImageY, Color.Black);
}
combinedImageY++;
}
}
}
combinedImage.Save(imageFile);
}
