private void button16_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application eApp = new Microsoft.Office.Interop.Excel.Application();
eApp.Visible = true;
Microsoft.Office.Interop.Excel.Workbook workBook = eApp.Workbooks.Open(@"e:/2.xlsx");
Worksheet sheet = workBook.ActiveSheet;
PivotTable pt = sheet.PivotTables()[1];
Microsoft.Office.Interop.Excel.Range range = pt.TableRange2;
range.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlPicture);
Worksheet newSheet = workBook.Sheets.Add();
//newSheet.Paste();
//Microsoft.Office.Interop.Excel.Shape s = newSheet.Shapes.Item(1);
//s.Chart.Export(@"e:\2.png", "PNG");
ChartObjects objs = newSheet.ChartObjects();
ChartObject chartobj = objs.Add(0, 0, range.Width, range.Height);
//if (chartobj.Chart != null)
chartobj.ProtectChartObject = false;
chartobj.Chart.Paste();
chartobj.Chart.Export(@"e:\1.png", "PNG");
chartobj.Delete();
eApp.Quit();
}
static void Main()
{
Console.WriteLine(loc);
Console.Write("\nchecking authorization level");//quickly check what the user's level is
/*
* CHANGE THIS TO A LOGIN \/
*/
string Authorization = "admin";//checks login
/*
* CHANGE THIS TO A LOG IN /\
*/
Console.Write("begining check process");//
string chartName = "chart";
Console.Write("Attempting to open file\n");//"please enter a name for the file:");
Console.WriteLine("Please enter file name: ");
//string file = Console.ReadLine();
string fileName = Console.ReadLine(); //user enters file name
string worksheetName = "Shipment - ByCell per week";
Application xlApp = new Application();//creates a new reference to excel
if (xlApp == null)
{
Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
return;
}
Workbook wb = null;//creates a wb reference
while (wb == null)
{
if (!File.Exists(loc + fileName + ".xlsm"))
{
Console.Write("File doesn't exist. please try again\n");
Console.WriteLine("Please enter file name: ");
fileName = Console.ReadLine();
}
else
{
Console.Write("File exists! opening file");
if (Authorization.CompareTo("admin") != 0)
{
xlApp.DisplayAlerts = false;
}
else
{
}
wb = xlApp.Workbooks.Open(loc + fileName + ".xlsm", Type.Missing, false);
}
}
xlApp.Visible = true;
Boolean found = false;
Worksheet ws = null;
//xlApp.DisplayAlerts = false;
if (wb != null)
{
foreach (Worksheet sheet in wb.Sheets)
{
// Check the name of the current sheet
if (sheet.Name == "Shipment - ByCell per week")
{
found = true;
Console.Write("worksheet was found\n");
break; // Exit the loop now
}
}
//ws = (Worksheet)wb.Worksheets[worksheetName];
}
else
{
ws = null;
}
if (found)
{
// Reference it by name
ws = wb.Sheets[worksheetName];
chart(ws, "C1", "N2", chartName, "1");
}
ws = wb.Sheets[2]; //sets ws to the second sheet as the wb.sheets[worksheetName] code was not working at the time
xlApp.ActiveWindow.Zoom = 50; //sets the zoom of the page out so you can see the chart as it generates
xlApp.DisplayAlerts = true; //allows excel to show any errors or alerts
if (ws == null)
{
Console.WriteLine("\nWorksheet could not be created. Check that your office installation and project references are correct.");
}
chart(ws, "C1", "Z2", chartName, "1");
bool deleted = false;
/*AFTER CREATING AND SAVING THE CHART THE CHART IS DELETED
* The reasoning behind this decision is so that if charts are generated constantly the file size will
* continue to grow and take up more space than needed, this way the chart is saved and stored as an immage.
* the immages can then be pulled to show a history of the past week.
*/
try
{
ChartObject chartPageDelete = ws.ChartObjects("Chart 1");
chartPageDelete.Delete();
deleted = true;
}
catch
{
Console.WriteLine("\nChart with this name could not be found");
//throw new Exception("Chart with this name could not be found");
}
finally
{
Console.WriteLine("\nthe chart was " + (deleted ? "deleted" : "not deleted"));
}
xlApp.DisplayAlerts = false;
//saving the file
Console.WriteLine("would you like to save? (Y or N)");
string userIn = Console.ReadLine();
if (userIn.Equals("Y") || userIn.Equals("y"))
{
save(xlApp, wb, fileName);
}
else
{
wb.Close(); //closes the workbook
}
Marshal.ReleaseComObject(xlApp); //releases all Com objects
xlApp.Quit(); //closes this instance of excel
}