Exporting excel chart as image
I have an Excel file which has a charts, these charts represent data in columns, in my program I change these data in columns and chart changes also, after that I export these charts in .png files, but there I met an exception HRESULT: 0x80030020 (STG_E_SHAREVIOLATION)
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using RTO.Models;
using Novacode;
using System.Drawing;
using Word = Microsoft.Office.Interop.Word;
using System.Reflection;
using CommonLib.SharedModels;
namespace RTO
{
class Program
{
public static void ReportRTO(RtoCommonData cmnData, List<Antenna> antennas)
{
Novacode.Image imageh, imagev, image1, image2;
Picture pictureh, picturev, picture1, picture2;
Paragraph pimg;
var exApp = new Excel.Application();
exApp.ScreenUpdating = false;
var exBook = exApp.Workbooks.Open(fileLeaf);
var exSheet = exBook.Worksheets[1] as Excel.Worksheet;
Excel.Range r1 = exSheet.get_Range("A1", "A360");
Excel.Range r2 = exSheet.get_Range("B1", "B360");
double[,] d1 = new double[360, 1];
double[,] d2 = new double[360, 1];
int w = 1;
var application = new Excel.Application();
application.ScreenUpdating = false;
var workbook = application.Workbooks.Open(fileExcel);
var worksheet = workbook.Worksheets[1] as Excel.Worksheet;
Excel.Range rng1 = worksheet.get_Range("A1", "A361");
Excel.Range rng2 = worksheet.get_Range("B1", "B361");
Excel.Range rng3 = worksheet.get_Range("C1", "C361");
Excel.Range rng4 = worksheet.get_Range("D1", "D361");
double[,] data1 = new double[361, 1];
double[,] data2 = new double[361, 1];
double[,] data3 = new double[361, 1];
double[,] data4 = new double[361, 1];
int flnmadd = 1;
for (int i = 0; i < antennas.Count; i++)
{
//Save chart as image
w = 1;
foreach (Excel.Worksheet ws in exBook.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 + @"\leaf" + w + ".png", "PNG", false);
w++;
}
}
//Insert image to doc
image1 = doc.AddImage(leafimg1);
picture1 = image1.CreatePicture();
picture1.Width = 310;
picture1.Height = 310;
image2 = doc.AddImage(leafimg2);
picture2 = image2.CreatePicture();
picture2.Width = 310;
picture2.Height = 310;
pimg = doc.InsertParagraph();
pimg.AppendPicture(picture1);
pimg.AppendPicture(picture2);
for (int j = 0; j < boztrows; j++)
{
data1[j, 0] = sumbozres[i].Rxhor[j];
data2[j, 0] = sumbozres[i].Rzhor[j];
data3[j, 0] = sumbozres[i].Rxver[j];
data4[j, 0] = sumbozres[i].Rzver[j];
}
data1[boztrows, 0] = data1[0, 0];
data2[boztrows, 0] = data2[0, 0];
data3[boztrows, 0] = data3[0, 0];
data4[boztrows, 0] = data4[0, 0];
rng1.Value = data1;
rng2.Value = data2;
rng3.Value = data3;
rng4.Value = data4;
//Save chart as image
flnmadd = 1;
foreach (Excel.Worksheet ws in workbook.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 + @"\charthv" + flnmadd + ".png", "PNG", false);
flnmadd++;
}
}
//Insert image to doc
if (antennas[i].Type == "БС")
{
imageh = doc.AddImage(charthimg);
pictureh = imageh.CreatePicture();
pictureh.Width = 624;
pictureh.Height = 357;
imagev = doc.AddImage(chartvimg);
picturev = imagev.CreatePicture();
picturev.Width = 624;
picturev.Height = 156;
pimg = doc.InsertParagraph();
pimg.AppendPicture(pictureh);
pimg = doc.InsertParagraph();
pimg.AppendPicture(picturev);
}
else if (antennas[i].Type == "РРС")
{
imageh = doc.AddImage(rrsimg);
pictureh = imageh.CreatePicture();
pictureh.Width = 624;
pictureh.Height = 156;
pimg = doc.InsertParagraph();
pimg.AppendPicture(pictureh);
}
trsprev += trs;
freqs = "";
pows = "";
koefgs = "";
koefgrazs = "";
poteri = "";
poteriraz = "";
freqAvg = 0;
}
exBook.Save();
exBook.Close();
exApp.Workbooks.Close();
exApp.Quit();
workbook.Save();
workbook.Close();
application.Workbooks.Close();
application.Quit();
}
}
}
Rustem Madigasymov • edited May 22, 2017 by Rustem Madigasymov2 Answers
Could be that your program has two instances of the same file. Another thing can be save the file before you're trying to save the picture.
Erik Emilio Flores DelfinAs pointed out in the question comments HRESULT: 0x80030020 (STG_E_SHAREVIOLATION) is "Access denied because another caller has the file open and locked" more information here. The file was simply still open/in use, and can be resolved by first deleting the old files.
There are some options, and it depends on how you intend to use the program. Adding a try/catch statement will keep the program from crashing. Besides that I don't see any particular best practice, it depends on usage. In my opinion it is entierly reasonable for the program to exit if it fails to save.
In the interest of offering a solution that you can adjust to your liking: first a method for saving charts that returns true if successful (not pretty but does the job):
using System.IO;
private static bool SaveExcelChartAsPNG(ChartObject co,
string path, string filename)
{
try
{
string filenamePNG = Path.ChangeExtension(filename, "png");
string fullFilenamePNG = Path.Combine(path, filenamePNG);
co.Select();
co.Chart.Export(fullFilenamePNG, "PNG", false);
}
catch
{
// Save was not successful
return false;
}
return true;
}
This solution will exit on unsuccessful save:
foreach (var co in chartObjects)
{
if (!SaveExcelChartAsPNG(exportPath, @"\leaf" + w + ".png"))
Application.Exit();
}
A longer example that will retry saving 10 times by incrementing the 'w' parameter, then try a random filename. If that doesnt work the program will exit.
//Save chart as image
w = 1;
foreach (var ws in exBook.Worksheets)
{
var chartObjects = (Excel.ChartObjects)(ws.ChartObjects(Type.Missing));
foreach (var co in chartObjects)
{
int retry = 0;
bool successfulSave = false;
while (!successfulSave && retry < 10) // retry by incerementing w parameter 10 times)
{
successfulSave = SaveExcelChartAsPNG(exportPath, @"\leaf" + w + ".png"))
retry++;
w++;
}
if (!successfulSave)
{
// Try again with random filename, otherwise exit
string filename = Path.GetRandomFileName();
if (!SaveExcelChartAsPNG(exportPath, filename))
{
// Save still not successful, exit
Application.Exit();
}
}
}
}
And a remark regarding the above code: (First off it's flawed, because if you already generate 10 charts 11 times you will always first generate charts 0-99 then you will end up with 10 charts with completely random names. In which case you might just want to generate random names to begin with.) In most cases it is not good to catch all exceptions and return true/false. Future problems can arise when some other Exception is raised that is not related to filenames. Both the user and programmer will be left oblivious to what happened. It is better to demand filenames that can be used, perhaps make 'w' or the output filenames an input parameter to the program to offer some flexibility.
One last option could be to instead create a new random output directory to guarantee that it is empty and output the prefered filenames there. Also by using Path.GetRandomFileName() that has the benefit over Path.GetTempFileName() of not creating the file.
NLindbomUser contributions licensed under CC BY-SA 3.0