public static void Run()
{
// ExStart:ReleaseUnmanagedResourcesForWorkbooks
// Create workbook object
Workbook wb1 = new Workbook();
// Call Dispose method
wb1.Dispose();
// Call Dispose method via Using statement
using (Workbook wb2 = new Workbook())
{
// Any other code goes here
}
// ExEnd:ReleaseUnmanagedResourcesForWorkbooks
}
public int ConvertToPDF(string sourceFile, string pdfFile)
{
if (string.IsNullOrEmpty(sourceFile))
{
throw new ArgumentNullException(nameof(sourceFile));
}
if (string.IsNullOrEmpty(pdfFile))
{
throw new ArgumentNullException(nameof(pdfFile));
}
if (!File.Exists(sourceFile))
{
throw new FileNotFoundException($"source file '{sourceFile}' doesn't exists");
}
if (File.Exists(pdfFile))
{
File.Delete(pdfFile);
}
if (!Check(true))
{
return(-1);
}
Workbook workBook = null;
try
{
workBook = app.Workbooks.Open(sourceFile);
workBook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, pdfFile);
return(1);
}
catch (Exception ex)
{
Kill();
throw new Exception("convert failed: " + sourceFile, ex);
}
finally
{
workBook?.Close(XlSaveAction.xlDoNotSaveChanges);
workBook?.Dispose();
}
}
private void ConvertXLSMToXLSX(String sourceFile, string destFile)
{
Workbook workbook = new Workbook();
try
{
workbook.LoadFromFile(sourceFile);
workbook.SaveToFile(destFile, ExcelVersion.Version2016);
}
catch (Exception ex)
{
errMsg += Environment.NewLine + "Exception occured at ConvertXLSToXLSX: " + ex.Message;
logMe.Error("Exception occured at ConvertXLSToXLSX: " + ex.Message);
}
finally
{
workbook.Dispose();
}
}
public void VerifyThatTheSessionDataIsWrittenToAWorkbookandCanBeRetrieved()
{
NetOffice.ExcelApi.Application application = null;
Workbook workbook = null;
try
{
application = new Application();
workbook = application.Workbooks.Open(this.testfilepath, false, false);
var workbookSessionDal = new WorkbookSessionDal(workbook);
workbookSessionDal.Write(this.workbookSession);
workbook.Save();
var retrievedWbSession = workbookSessionDal.Read();
Assert.NotNull(retrievedWbSession);
Assert.AreEqual(this.workbookSession.RebuildDateTime, retrievedWbSession.RebuildDateTime);
Assert.AreEqual(this.workbookSession.Person.Iid, retrievedWbSession.Person.Iid);
Assert.AreEqual(this.workbookSession.DomainOfExpertise.Iid, retrievedWbSession.DomainOfExpertise.Iid);
}
catch (Exception ex)
{
Console.WriteLine(ex);
throw ex;
}
finally
{
if (workbook != null)
{
Console.WriteLine("Closing workbook {0}", this.testfilepath);
workbook.Close();
workbook.Dispose();
}
if (application != null)
{
Console.WriteLine("Closing Excel Application");
application.Quit();
application.Dispose();
}
}
}
/// <summary>
/// Releases unmanaged and - optionally - managed resources.
/// </summary>
/// <param name="disposing"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param>
protected virtual void Dispose(bool disposing)
{
if (disposed)
{
return;
}
if (disposing)
{
if (disposeWorksheet)
{
range.Worksheet.Dispose();
}
if (disposeWorkbook)
{
Workbook.SaveAs(path);
Workbook.Dispose();
}
}
disposed = true;
}
// gerando e salvando planilha excel via FreeSpire
public static async Task <Boolean> GeraSpireXLSAsync(DataTable _dt, string _path)
{
// via nuget FreeSpire.XLS
// gerando arquivo xlsc com 2 planilhas !
try
{
// criando planilha 1 - _dt1
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
// populando a planilha 1 com o DataTable _dt
await Task.Run(() =>
{
sheet.InsertDataTable(_dt, true, 1, 1);
// definindo altura da linha = 15
for (int i = sheet.LastRow - 1; i >= 0; i--)
{
sheet.SetRowHeight(i + 1, 15);
}
workbook.Worksheets[0].Name = "Plan1";
workbook.Worksheets[1].Name = "Plan2";
workbook.Worksheets[2].Name = "Plan3";
// workbook.Worksheets[2].Remove();
// salvando arquivo em .... _path
workbook.SaveToFile(_path, ExcelVersion.Version2013);
workbook.Dispose();
});
return(true);
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Gera XLSX", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
}
public void LoadStreamReport(string exportFilePath, Stream stream, DataSet dataSet, bool isExcel)
{
var book = new Workbook();
book.LoadFromStream(stream);
stream.Close();
//Log.Instance.Reset();
var bookTemplate = new ReportBookTemplate {
DataSource = dataSet
};
bookTemplate.LoadTemplate(book, paramMap);
bookTemplate.FillTemplate();
book.SaveToFile(exportFilePath, isExcel ? FileFormat.Version2010 : FileFormat.PDF);
book.Dispose();
//GC.Collect();
Process.Start(exportFilePath);
}
private int ConvertFile(string sourcefile)
{
try
{
Workbook wb = new Workbook(sourcefile);
// int ncount = wb.Worksheets.Count;
// ncount = ncount > 1 ? 1 : ncount;
// int[] pages = new int[ncount];
// for (int j = 0; j < ncount; j++ )
// {
// pages[j] = j + 1;
// }
string outtifffile = (outpath + @"\" + GetFileName(sourcefile) + ".tiff").Replace(@"\\", @"\");
wb.Save(outtifffile, SaveFormat.TIFF);
wb.Dispose();
Image img = Image.FromFile(outtifffile);
for (int i = 0; i < 1; i++)
{
img.SelectActiveFrame(System.Drawing.Imaging.FrameDimension.Page, i);
img.Save((outpath + @"\" + GetFileName(sourcefile) + i + ".png").Replace(@"\\", @"\"), System.Drawing.Imaging.ImageFormat.Png);
}
img.Dispose();
File.Delete(outtifffile);
return(0);
}
catch (Exception e)
{
return(1);
}
}
private void SaveFile(string file)
{
Workbook work = new Workbook();
Worksheet wsheek = null;
if (File.Exists(file))
{
work.LoadFromFile(file);
}
try {
wsheek = work.Worksheets[0];
int rows = wsheek.LastRow + 1;
for (int i = 0; i < lvQuer.Items.Count; i++)
{
for (int c = 0; c < lvQuer.Columns.Count; c++)
{
wsheek.Range[rows + i, c + 1].Text = lvQuer.Items[i].SubItems[c].Text;
}
}
work.SaveToFile(file, FileFormat.Version2007);
work.Dispose();
} catch { }
MessageBox.Show("导出完成!");
}
public void AddRow(CheckPointLogModel logData)
{
var columns = new List<KeyValuePair<string, object>>();
if (logData != null)
{
columns.Add(new KeyValuePair<string, object>("№", logData.Counters.Journal));
columns.Add(new KeyValuePair<string, object>("Дата", this.TruncateDate(logData.StartDate).ToString(StringConstrants.DateTimeFormat)));
columns.Add(new KeyValuePair<string, object>("Changelist", logData.Counters.MaxCommitChange));
columns.Add(new KeyValuePair<string, object>("Upgrade", logData.Counters.Upgrade));
columns.Add(new KeyValuePair<string, object>("Потребители", logData.UserCount));
columns.Add(new KeyValuePair<string, object>("Проекти", logData.ProjectCount));
columns.Add(new KeyValuePair<string, object>("Файлове", logData.Sizes.FilesCount));
columns.Add(new KeyValuePair<string, object>("Версии", logData.Sizes.RevisionsCount));
columns.Add(new KeyValuePair<string, object>("Депо", string.Format("{0:0.00} Mb", logData.DepotSize)));
columns.Add(new KeyValuePair<string, object>("Log", string.Format("{0:0.00} Mb", logData.Log.FileSize)));
columns.Add(new KeyValuePair<string, object>("Auditlog", string.Format("{0:0.00} Mb", logData.AuditLog.FileSize)));
columns.Add(new KeyValuePair<string, object>("Име", logData.Arhive != null ? logData.Arhive.ArhivePatternName : string.Empty));
columns.Add(new KeyValuePair<string, object>("Размер", string.Format("{0:0.00} Mb", logData.Arhive != null ? logData.Arhive.Size : 0)));
columns.Add(new KeyValuePair<string, object>("Платформа", logData.ServerInfo.Platform));
columns.Add(new KeyValuePair<string, object>("Версия", string.Format("'{0}", logData.ServerInfo.Version)));
columns.Add(new KeyValuePair<string, object>("Ревизия", logData.ServerInfo.Revision));
columns.Add(new KeyValuePair<string, object>("от Дата", this.TruncateDate(logData.ServerInfo.Date).ToString(StringConstrants.DateFormat)));
}
var workbook = new Workbook();
workbook.LoadFromFile(this.FullFileRoot);
Worksheet sheet = workbook.Worksheets.FirstOrDefault(s => s.Name == SheetName) as Worksheet;
if (sheet == null)
{
throw new ArgumentNullException("Excel sheet not found");
}
var range = sheet.Range;
var headerStart = this.FindHeader(range, "№");
var lastRow = this.FindLastRow(range, headerStart);
sheet.InsertRow(lastRow.Row, 1, InsertOptionsType.FormatAsBefore);
for (int index = 0; index < columns.Count; index++)
{
var currentCol = lastRow.Column + index;
var headerText = sheet.Range[headerStart.Row, currentCol].Text;
var currentCell = columns[index];
if (headerText == currentCell.Key)
{
sheet.Range[lastRow.Row, currentCol].Value2 = currentCell.Value;
}
}
workbook.Save();
workbook.Dispose();
}
