public void CreateTableFormatedText()
{
//Create new spreadsheet document
SpreadsheetDocument spreadsheetDocument = new SpreadsheetDocument();
spreadsheetDocument.New();
//Create a new table
Table table = new Table(spreadsheetDocument, "First", "tablefirst");
//Create a new cell, without any extra styles
Cell cell = table.CreateCell();
//cell.OfficeValueType = "string";
//Set full border
//cell.CellStyle.CellProperties.Border = Border.NormalSolid;
//Add a paragraph to this cell
Paragraph paragraph = ParagraphBuilder.CreateSpreadsheetParagraph(
spreadsheetDocument);
//Create some Formated text
FormatedText fText = new FormatedText(spreadsheetDocument, "T1", "Some Text");
//fText.TextStyle.TextProperties.Bold = "bold";
fText.TextStyle.TextProperties.Underline = LineStyles.dotted;
//Add formated text
paragraph.TextContent.Add(fText);
//Add paragraph to the cell
cell.Content.Add(paragraph);
//Insert the cell at row index 2 and column index 3
//All need rows, columns and cells below the given
//indexes will be build automatically.
table.InsertCellAt(2, 3, cell);
//Insert table into the spreadsheet document
spreadsheetDocument.TableCollection.Add(table);
spreadsheetDocument.SaveTo(AARunMeFirstAndOnce.outPutFolder + "formated.ods");
}
public void ComplexCalcLoadTest()
{
string file = AARunMeFirstAndOnce.inPutFolder + @"HazelHours.ods";
FileInfo fInfo = new FileInfo(file);
//Load a spreadsheet document
SpreadsheetDocument document = new SpreadsheetDocument();
try
{
document.Load(file);
//Save it back again
document.SaveTo(AARunMeFirstAndOnce.outPutFolder + fInfo.Name + ".html");
}
catch (Exception ex)
{
if (ex is AODLException)
{
Console.WriteLine("Stacktrace: {0}", ((AODLException)ex).OriginalException.StackTrace);
Console.WriteLine("Msg: {0}", ((AODLException)ex).OriginalException.Message);
if (((AODLException)ex).Node != null)
{
Console.WriteLine("Stacktrace: {0}", ((AODLException)ex).Node.OuterXml);
}
}
}
}
public void CsvToOpenDocumentSpreadsheet()
{
SpreadsheetDocument document = new SpreadsheetDocument();
document.Load(AARunMeFirstAndOnce.inPutFolder + "CsvToOpenDocument.csv");
Assert.IsTrue(document.Content.Count == 1, "Must contain objects.");
document.SaveTo(AARunMeFirstAndOnce.outPutFolder + "CsvToOpenDocument.ods");
}
public void ComplexCalcLoadTest()
{
string file = AARunMeFirstAndOnce.inPutFolder + @"HazelHours.ods";
FileInfo fInfo = new FileInfo(file);
//Load a spreadsheet document
SpreadsheetDocument document = new SpreadsheetDocument();
document.Load(file);
//Save it back again
document.SaveTo(AARunMeFirstAndOnce.outPutFolder + fInfo.Name + ".html");
}
public void HTMLExportTest3()
{
string file = AARunMeFirstAndOnce.inPutFolder + @"simpleCalc.ods";
FileInfo fInfo = new FileInfo(file);
//Load a spreadsheet document
SpreadsheetDocument document = new SpreadsheetDocument();
document.Load(file);
//Save it back again
document.SaveTo(AARunMeFirstAndOnce.outPutFolder + fInfo.Name + ".html");
}
public void TestLengend()
{
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.Load(Path.Combine(AARunMeFirstAndOnce.inPutFolder, @"TestChartOne.ods"));
Chart chart = (Chart)doc.EmbedObjects [0];
chart.ChartLegend.LegendPosition = "left";
chart.ChartLegend.SvgX = "5cm";
chart.ChartLegend.SvgY = "2cm";
doc.SaveTo(Path.Combine(AARunMeFirstAndOnce.outPutFolder, "TestLegend.ods"));
}
public void LoadChartModifyTitle()
{
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.Load(Path.Combine(AARunMeFirstAndOnce.inPutFolder, @"TestChartOne.ods"));
IContent iContent = (EmbedObject)doc.EmbedObjects [0];
((Chart)iContent).ChartTitle.SetTitle("A New Title");
((Chart)iContent).ChartTitle.SvgX = "2cm";
((Chart)iContent).ChartTitle.SvgY = "0.5cm";
doc.SaveTo(Path.Combine(AARunMeFirstAndOnce.outPutFolder, "TestTitle.ods"));
}
public void NewChartWithCellRange()
{
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.Load(Path.Combine(AARunMeFirstAndOnce.inPutFolder, @"testsheet.ods"));
Table table = doc.TableCollection[0];
Chart chart = ChartBuilder.CreateChartByCellRange(table, "A4:F8", ChartTypes.bar, null, null, "刘玉花的测试", 3, "bottom", "P14");
table.InsertChartAt("H2", chart);
doc.Content.Add(table);
doc.SaveTo(Path.Combine(AARunMeFirstAndOnce.outPutFolder, "NewChartWithCellRange.ods"));
}
public void NewChartWithLegend()
{
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.Load(Path.Combine(AARunMeFirstAndOnce.inPutFolder, @"testsheet.ods"));
Table table = doc.TableCollection[0];
Chart chart = ChartBuilder.CreateChartByLegend(table, "A3:F8", ChartTypes.surface, "left", "0.5", "5", "year", "dollars");
table.InsertChartAt("M2", chart);
doc.Content.Add(table);
doc.SaveTo(Path.Combine(AARunMeFirstAndOnce.outPutFolder, "NewChartWithLegend.ods"));
}
public void NewChartWithAxises()
{
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.Load(Path.Combine(AARunMeFirstAndOnce.inPutFolder, @"testsheet.ods"));
Table table = doc.TableCollection[0];
Chart chart = ChartBuilder.CreateChartByAxises(table, "A1:B2", ChartTypes.line, 2);
table.InsertChartAt("I2", chart);
doc.Content.Add(table);
doc.SaveTo(Path.Combine(AARunMeFirstAndOnce.outPutFolder, "NewChartWithAxis.ods"));
}
public void SpreadSheetFormsTest()
{
//Create new spreadsheet document
SpreadsheetDocument spreadsheetDocument = new SpreadsheetDocument();
spreadsheetDocument.New();
//Create a new table
Table table = new Table(spreadsheetDocument, "First", "tablefirst");
//Create a new cell, without any extra styles
Cell cell = new Cell(spreadsheetDocument, "cell001");
cell.OfficeValueType = "string";
//Set full border
cell.CellStyle.CellProperties.Border = Border.NormalSolid;
//Add a paragraph to this cell
Paragraph paragraph = ParagraphBuilder.CreateSpreadsheetParagraph(
spreadsheetDocument);
//Add some text content
paragraph.TextContent.Add(new SimpleText(spreadsheetDocument, "Some text"));
//Add paragraph to the cell
cell.Content.Add(paragraph);
//Insert the cell at row index 2 and column index 3
//All need rows, columns and cells below the given
//indexes will be build automatically.
table.Rows.Add(new Row(table, "Standard"));
table.Rows.Add(new Row(table, "Standard"));
table.Rows.Add(new Row(table, "Standard"));
table.InsertCellAt(3, 2, cell);
//Insert table into the spreadsheet document
ODFForm main_form = new ODFForm(spreadsheetDocument, "mainform");
main_form.Method = Method.Get;
ODFButton butt = new ODFButton(main_form, cell.Content, "butt", "0cm", "0cm", "15mm", "8mm");
butt.Label = "test :)";
main_form.Controls.Add(butt);
spreadsheetDocument.TableCollection.Add(table);
table.Forms.Add(main_form);
spreadsheetDocument.SaveTo(AARunMeFirstAndOnce.outPutFolder + "spreadsheet_forms.ods");
SpreadsheetDocument spreadsheetDocument2 = new SpreadsheetDocument();
spreadsheetDocument2.Load(AARunMeFirstAndOnce.outPutFolder + "spreadsheet_forms.ods");
ODFButton b = spreadsheetDocument2.TableCollection[0].FindControlById("butt") as ODFButton;
Assert.IsNotNull(b);
b.Label = "it works!";
spreadsheetDocument2.SaveTo(AARunMeFirstAndOnce.outPutFolder + "spreadsheet_forms2.ods");
}
public void TestPlotArea()
{
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.Load(Path.Combine(AARunMeFirstAndOnce.inPutFolder, @"TestChartOne.ods"));
Chart chart = (Chart)doc.EmbedObjects [0];
chart.ChartPlotArea.SvgX = "1.2cm";
chart.ChartPlotArea.SvgY = "2.5cm";
chart.ChartPlotArea.Width = "5cm";
chart.ChartPlotArea.Height = "5cm";
doc.SaveTo(Path.Combine(AARunMeFirstAndOnce.outPutFolder, "TestPlotArea.ods"));
}
public void SpreadSheetImportExportTest()
{
//Create new spreadsheet document
SpreadsheetDocument spreadsheetDocument = new SpreadsheetDocument();
spreadsheetDocument.Load(AARunMeFirstAndOnce.inPutFolder + @"bigtable.ods");
ODFForm f = new ODFForm(spreadsheetDocument, "mainform");
ODFButton butt = new ODFButton(f, spreadsheetDocument.TableCollection[0].Rows[1].Cells[1].Content, "butt", "5mm", "15mm", "4cm", "1cm");
f.Controls.Add(butt);
spreadsheetDocument.TableCollection[0].Forms.Add(f);
spreadsheetDocument.SaveTo(AARunMeFirstAndOnce.outPutFolder + "bigtable2.ods");
}
public void SimpleCalcLoadTest()
{
string file = AARunMeFirstAndOnce.inPutFolder + @"simpleCalc.ods";
FileInfo fInfo = new FileInfo(file);
//Load a spreadsheet document
SpreadsheetDocument document = new SpreadsheetDocument();
document.Load(file);
Assert.IsTrue(document.CommonStyles.Count > 0, "Common Styles must be read!");
Console.WriteLine("Common styles: {0}", document.CommonStyles.Count);
//Save it back again
document.SaveTo(AARunMeFirstAndOnce.outPutFolder + fInfo.Name + ".rel.ods");
}
public void LoadChart()
{
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.Load(Path.Combine(AARunMeFirstAndOnce.inPutFolder, @"TestChartOne.ods"));
IContent iContent = (EmbedObject)doc.EmbedObjects [0];
((Chart)iContent).ChartType = ChartTypes.bar.ToString();
((Chart)iContent).XAxisName = "XAxis";
((Chart)iContent).YAxisName = "YAxis";
((Chart)iContent).SvgWidth = "20cm";
((Chart)iContent).SvgHeight = "20cm";
doc.SaveTo(Path.Combine(AARunMeFirstAndOnce.outPutFolder, "LoadChart.ods"));
}
public void NewChartWithTitle()
{
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.Load(Path.Combine(AARunMeFirstAndOnce.inPutFolder, @"testsheet.ods"));
Table table = doc.TableCollection[0];
Chart chart = ChartBuilder.CreateChartByTitle(table, "A3:E7", ChartTypes.stock, "北京红旗中文两千公司九月工资报表", "0.5cm", "0.5cm", null, null);
chart.ChartTitle.TitleStyle.TextProperties.FontSize = "3pt";
chart.EndCellAddress = table.TableName + ".P17";
table.InsertChartAt("I2", chart);
doc.Content.Add(table);
doc.SaveTo(Path.Combine(AARunMeFirstAndOnce.outPutFolder, "NewChartWithTitle.ods"));
}
public void CreateNewSpreadsheet()
{
SpreadsheetDocument spreadsheetDocument = new SpreadsheetDocument();
spreadsheetDocument.New();
Assert.IsNotNull(spreadsheetDocument.DocumentConfigurations2);
Assert.IsNotNull(spreadsheetDocument.DocumentManifest);
Assert.IsNotNull(spreadsheetDocument.DocumentPictures);
Assert.IsNotNull(spreadsheetDocument.DocumentThumbnails);
Assert.IsNotNull(spreadsheetDocument.DocumentSetting);
Assert.IsNotNull(spreadsheetDocument.DocumentStyles);
Assert.IsNotNull(spreadsheetDocument.TableCollection);
spreadsheetDocument.SaveTo(AARunMeFirstAndOnce.outPutFolder + "blank.ods");
Assert.IsTrue(File.Exists(AARunMeFirstAndOnce.outPutFolder + "blank.ods"));
}
public void NewBasicChartThenSetTitle()
{
string expected = "Basic Chart";
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.New();
Table table = new Table(doc, "tab1", "tab1");
for (int i = 1; i <= 1; i++)
{
for (int j = 1; j <= 6; j++)
{
Cell cell = table.CreateCell();
cell.OfficeValueType = "float";
Paragraph paragraph = new Paragraph(doc);
string text = (j + i - 1).ToString();
paragraph.TextContent.Add(new SimpleText(doc, text));
cell.Content.Add(paragraph);
cell.OfficeValueType = "string";
cell.OfficeValue = text;
table.InsertCellAt(i, j, cell);
}
}
Chart basicChart = ChartBuilder.CreateChart(table, ChartTypes.line, "A4:F8");
ChartTitle ct = new ChartTitle(basicChart);
//ct.InitTitle();
ct.SetTitle(expected);
Assert.AreEqual(expected, ((Paragraph)ct.Content[0]).TextContent[0].Text);
basicChart.ChartTitle = ct;
IContent chartTitleContent = null;
chartTitleContent = basicChart.Content.Find(o => o is ChartTitle);
if (chartTitleContent == null)
{
foreach (IContent iContent in basicChart.Content)
{
if (iContent is ChartTitle)
{
chartTitleContent = iContent;
}
}
}
Assert.AreEqual(expected, ((Paragraph)((ChartTitle)chartTitleContent).Content[0]).TextContent[0].Text);
table.InsertChartAt("H2", basicChart);
doc.TableCollection.Add(table);
doc.SaveTo(Path.Combine(AARunMeFirstAndOnce.outPutFolder, "BasicChartWithTitlesetafterwards.ods"));
}
/// <summary>
///
/// </summary>
public FileDocumento SaveAndExportData()
{
FileDocumento file = new FileDocumento();
bool retValue = false;
document.SaveTo(filePath);
//Codice per generare l'export del file
try
{
//string temporaryXSLFilePath = string.Empty;
StreamWriter writer = null;
StringBuilder sb = new StringBuilder();
//Salva e chiudi il file
//temporaryXSLFilePath = HttpContext.Current.Server.MapPath(filePath);
//Crea il file
FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
if (stream != null)
{
byte[] contentODS = new byte[stream.Length];
stream.Read(contentODS, 0, contentODS.Length);
stream.Flush();
stream.Close();
stream = null;
file.content = contentODS;
file.length = contentODS.Length;
file.estensioneFile = "ods";
file.name = "ExportDocumenti";
file.contentType = "application/vnd.oasis.opendocument.spreadsheet";
}
retValue = true;
}
catch (Exception e)
{
// this.file = null;
logger.Debug("Errore esportazione documenti : " + e.Message);
}
File.Delete(filePath);
return(file);
}
public void CreateNewChart()
{
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.New();
Table table = new Table(doc, "tab1", "tab1");
for (int i = 1; i <= 1; i++)
{
for (int j = 1; j <= 6; j++)
{
Cell cell = table.CreateCell();
cell.OfficeValueType = "float";
Paragraph paragraph = new Paragraph(doc);
string text = (j + i - 1).ToString();
paragraph.TextContent.Add(new SimpleText(doc, text));
cell.Content.Add(paragraph);
cell.OfficeValueType = "string";
cell.OfficeValue = text;
table.InsertCellAt(i, j, cell);
}
}
Chart chart = ChartBuilder.CreateChartByAxisName
(table, ChartTypes.bar, "A1:E4", "years", "dollars");
Assert.AreEqual(7, table.Rows[1].Cells.Count);
Assert.AreEqual(6, table.Rows[2].Cells.Count);
Assert.AreEqual(6, table.Rows[3].Cells.Count);
Assert.AreEqual(6, table.Rows[4].Cells.Count);
/*Chart chart = new Chart (table,"ch1");
* chart.ChartType=ChartTypes.bar .ToString () ;
* chart.XAxisName ="yeer";
* chart.YAxisName ="dollar";
* chart.CreateFromCellRange ("A1:E4");
* chart.EndCellAddress ="tab1.K17";*/
table.InsertChartAt("G2", chart);
doc.Content.Add(table);
doc.SaveTo(Path.Combine(AARunMeFirstAndOnce.outPutFolder, @"NewChartOne.ods"));
}
void Supl(int id) //бланк в разрезе по поставщикам, поэтому функция для фильтрации объектов того или иного поставщика
{
SpreadsheetDocument spreadsheetDocument = new SpreadsheetDocument();
spreadsheetDocument.New();
Table table = new Table(spreadsheetDocument, "First", "tablefirst");
for (int j = 0; j < countsup; j++)
{
Paragraph parag = ParagraphBuilder.CreateSpreadsheetParagraph(spreadsheetDocument);
var text = TextBuilder.BuildTextCollection(spreadsheetDocument, " ");
Cell cell = table.CreateCell();
parag.TextContent.Add(new SimpleText(spreadsheetDocument, "элемент бд, где поставщик ид=ид"));//И за это тоже выебу и в прямом, и в переносном смысле за такие фразы
cell.Content.Add(parag);
table.InsertCellAt(0, 0, cell);
spreadsheetDocument.TableCollection.Add(table);
spreadsheetDocument.SaveTo("Matr.ods");
}
}
/// <summary>
/// Metodo per la generazione di un report ODS
/// </summary>
/// <param name="request">Informazioni sul report da produrre</param>
/// <param name="reports">Report da esportare</param>
/// <returns>Foglio di calcolo Open Office</returns>
public FileDocumento GenerateReport(PrintReportRequest request, List <DocsPaVO.Report.Report> reports)
{
// Instanziazione del writer ODS e creazione del documento
SpreadsheetDocument spreadSheetDocument = new SpreadsheetDocument();
spreadSheetDocument.New();
// Generazione di un foglio per ogni report
foreach (var report in reports)
{
// Generazione di un foglio con i dati contenuti nel report
this.AddWorksheet(spreadSheetDocument, report, request.ReportKey);
}
// Inizializzazione del file name
String fileName = String.Format("Report_{0}.ods", DateTime.Now.ToString("dd-MM-yyyy"));
// Inizializzazione del path del file
String filePath;
if (!String.IsNullOrEmpty(DocsPaUtils.Configuration.InitConfigurationKeys.GetValue("0", "BE_TEMP_PATH")))
{
filePath = Path.Combine(
DocsPaUtils.Configuration.InitConfigurationKeys.GetValue("0", "BE_TEMP_PATH"),
String.Format(@"AODF\{0}", Guid.NewGuid()));
}
else
{
filePath = Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.InternetCache),
String.Format(@"AODF\{0}", Guid.NewGuid()));
}
//String filePath = Path.Combine(
// Environment.GetFolderPath(Environment.SpecialFolder.InternetCache),
// String.Format(@"AODF\{0}", Guid.NewGuid()));
// Creazione della cartella
DirectoryInfo dirInfo = Directory.CreateDirectory(filePath);
// Salvataggio del file Open Document
spreadSheetDocument.SaveTo(Path.Combine(filePath, fileName));
// Generazione del risultato dell'export
FileDocumento document = new FileDocumento();
using (MemoryStream stream = new MemoryStream(File.ReadAllBytes(Path.Combine(filePath, fileName))))
{
document.name = fileName;
document.path = String.Empty;
document.fullName = document.name;
document.contentType = "application/vnd.oasis.opendocument.spreadsheet";
document.content = new Byte[stream.Length];
stream.Read(document.content, 0, document.content.Length);
stream.Flush();
stream.Close();
}
// Si provano a cancellare File e cartella
this.DeleteTemporaryData(dirInfo);
return(document);
}
