public void CreateSimpleTable()
{
//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("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.InsertCellAt(2, 3, cell);
//Insert table into the spreadsheet document
spreadsheetDocument.TableCollection.Add(table);
spreadsheetDocument.SaveTo(AARunMeFirstAndOnce.outPutFolder+"simple.ods");
}
public void CreateSimpleTable()
{
//Create new spreadsheet document
_spreadsheetDocument2 = new SpreadsheetDocument();
_spreadsheetDocument2.Load(AARunMeFirstAndOnce.inPutFolder+@"blank.ods");
//Create a new table
Table table = new Table(_spreadsheetDocument2, "First", "tablefirst");
table.Rows.Add(new Row(table));
//Create a new cell, without any extra styles
Cell cell = new Cell(_spreadsheetDocument2, "cell001");
cell.OfficeValueType = "string";
//Set full border
cell.CellStyle.CellProperties.Border = Border.NormalSolid;
//Add a paragraph to this cell
Paragraph paragraph = ParagraphBuilder.CreateSpreadsheetParagraph(
_spreadsheetDocument2);
//Add some text content
String cellText = "Some text";
paragraph.TextContent.Add(new SimpleText(_spreadsheetDocument2, cellText));
//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(1, 1, cell);
//Insert table into the spreadsheet document
_spreadsheetDocument2.TableCollection.Add(table);
// Test inserted content
Assert.AreEqual(_spreadsheetDocument2.TableCollection[0], table);
String text = _spreadsheetDocument2.TableCollection[0].Rows[1].Cells[1].Node.InnerText;
Assert.AreEqual(text, cellText);
}
void Example1()
{
//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();
//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("example1_formated.ods");
}
public void CreateRowsAndColumns()
{
SpreadsheetDocument doc = new SpreadsheetDocument ();
doc.New ();
Table table = new Table (doc,"tab1","tab1");
for(int i=1; i<=4; i++)
{
for(int j=1; j<=5;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);
}
}
Assert.AreEqual(5, table.Rows.Count);
for (int i = 1; i < 4; i++)
{
Row row = table.Rows[i];
Assert.AreEqual(6, row.Cells.Count);
}
}
public SpreadsheetReportGenerator ()
{
//Create new spreadsheet document
document = new SpreadsheetDocument();
document.New();
table = TableBuilder.CreateSpreadsheetTable (document, "First", "");
}
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 CellHasReferenceToRow()
{
string file = AARunMeFirstAndOnce.inPutFolder + @"bigtable.ods";
SpreadsheetDocument textDocument = new SpreadsheetDocument();
textDocument.Load(file);
Table table = textDocument.Content[1] as Table;
Row row = table.Rows[0];
Cell cell = row.Cells[0];
Assert.AreEqual(row, cell.Row);
Assert.AreEqual(table, row.Table);
}
public void CreateNewSpreadsheet()
{
_spreadsheetDocument1 = new SpreadsheetDocument();
_spreadsheetDocument1.New();
Assert.IsNotNull(_spreadsheetDocument1.DocumentConfigurations2);
Assert.IsNotNull(_spreadsheetDocument1.DocumentManifest);
Assert.IsNotNull(_spreadsheetDocument1.DocumentPictures);
Assert.IsNotNull(_spreadsheetDocument1.DocumentThumbnails);
Assert.IsNotNull(_spreadsheetDocument1.DocumentSetting);
Assert.IsNotNull(_spreadsheetDocument1.DocumentStyles);
Assert.IsNotNull(_spreadsheetDocument1.TableCollection);
//_spreadsheetDocument1.SaveTo(AARunMeFirstAndOnce.outPutFolder+"blank.ods");
//Assert.IsTrue(File.Exists(AARunMeFirstAndOnce.outPutFolder+"blank.ods"));
}
public void AutomaticallCreateRows()
{
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<=1;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);
}
}
// test that we have this number of rows and cells
Assert.AreEqual(2, table.Rows.Count);
for (int i = 1; i < table.Rows.Count; i++)
{
Row row = table.Rows[i];
Assert.AreEqual(2, row.Cells.Count);
}
// force to insert more cells
table.InsertCellAt(5, 5, table.CreateCell () );
// assert, that the cells were added
Assert.AreEqual(6, table.Rows.Count);
for (int i = 0; i < table.Rows.Count; i++)
{
Row row = table.Rows[i];
Assert.AreEqual(6, row.Cells.Count);
}
}
/// <summary>
/// Exports the provided report template to an ODS document.
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
protected override Stream Export(IGenerationContext context)
{
IReportTemplateSection sdet = context.Template.Sections.GetSection(SectionType.Detail);
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.New();
IMultipleRowsProducer producer = sdet.RootElement.FindFirstMultipleRowsProducer();
if (producer != null)
{
Table table = TableBuilder.CreateSpreadsheetTable(doc, "Table", string.Empty);
doc.Content.Add(table);
WriteTable(producer, table, context);
}
return CreateStream(doc);
}
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"));
}
public override void Export(IEnumerable<List<CellInfo>> exportInfo)
{
SpreadsheetDocument spreadsheetDocument = new SpreadsheetDocument();
spreadsheetDocument.New();
Table table = new Table(spreadsheetDocument, "DataGrid", string.Empty);
int rowIndex = 0;
foreach (List<CellInfo> cellInfos in exportInfo)
{
for (int columnIndex = 0; columnIndex < cellInfos.Count; columnIndex++)
{
CellInfo cellInfo = cellInfos[columnIndex];
Cell cell = table.CreateCell();
Paragraph paragraph = ParagraphBuilder.CreateSpreadsheetParagraph(spreadsheetDocument);
SimpleText fText = new SimpleText(spreadsheetDocument, (cellInfo.Value ?? string.Empty).ToString());
paragraph.TextContent.Add(fText);
cell.Content.Add(paragraph);
table.InsertCellAt(rowIndex, columnIndex, cell);
}
rowIndex++;
}
spreadsheetDocument.TableCollection.Add(table);
Save(spreadsheetDocument);
}
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 class_traslate_spreadsheet(string query_sql, string[] args_names_field, string[] args_type_field, bool typetext, string[] args_field_text, string name_field_text, bool more_title, string[] args_more_title)
{
//Console.WriteLine(name_field_text+" nombre del campo");
int files_field = 0;
string [] array_field_text = new string[args_field_text.Length];
connectionString = conexion_a_DB._url_servidor + conexion_a_DB._port_DB + conexion_a_DB._usuario_DB + conexion_a_DB._passwrd_user_DB;
nombrebd = conexion_a_DB._nombrebd;
//Create new spreadsheet open document (.ods)
AODL.Document.SpreadsheetDocuments.SpreadsheetDocument spreadsheetDocument = new AODL.Document.SpreadsheetDocuments.SpreadsheetDocument();
spreadsheetDocument.New();
//Create a new table
AODL.Document.Content.Tables.Table table = new AODL.Document.Content.Tables.Table(spreadsheetDocument, "hoja1", "tablefirst");
NpgsqlConnection conexion;
conexion = new NpgsqlConnection(connectionString + nombrebd);
// Verifica que la base de datos este conectada
try{
conexion.Open();
NpgsqlCommand comando;
comando = conexion.CreateCommand();
comando.CommandText = query_sql;
Console.WriteLine(comando.CommandText);
comando.ExecuteNonQuery(); comando.Dispose();
NpgsqlDataReader lector = comando.ExecuteReader();
// Creando los nombres de ancabezado de los campos
for (int colum_field = 0; colum_field < args_names_field.Length; colum_field++)
{
AODL.Document.Content.Tables.Cell cell = table.CreateCell();
//cell.OfficeValueType ="float";
AODL.Document.Content.Text.Paragraph paragraph = new AODL.Document.Content.Text.Paragraph(spreadsheetDocument);
string text = (string)args_names_field[colum_field].ToString().Trim();
paragraph.TextContent.Add(new AODL.Document.Content.Text.SimpleText(spreadsheetDocument, text));
cell.Content.Add(paragraph);
cell.OfficeValueType = "string";
cell.OfficeValue = text;
table.InsertCellAt(files_field, colum_field, cell);
}
if (typetext == true)
{
// Creando los nombres de ancabezado de los campos cuando son de tipo text (almacenado en una tabla tipo text)
for (int colum_field2 = 0; colum_field2 < args_field_text.Length; colum_field2++)
{
AODL.Document.Content.Tables.Cell cell = table.CreateCell();
//cell.OfficeValueType ="float";
AODL.Document.Content.Text.Paragraph paragraph = new AODL.Document.Content.Text.Paragraph(spreadsheetDocument);
string text = (string)args_field_text[colum_field2].ToString().Trim();
paragraph.TextContent.Add(new AODL.Document.Content.Text.SimpleText(spreadsheetDocument, text));
cell.Content.Add(paragraph);
cell.OfficeValueType = "string";
cell.OfficeValue = text;
table.InsertCellAt(files_field, colum_field2 + args_names_field.Length, cell);
}
}
if (more_title == true)
{
int title_field_text = 0;
if (typetext == true)
{
title_field_text = args_field_text.Length;
}
// Creando los nombres de ancabezado de los campos cuando son de tipo text (almacenado en una tabla tipo text)
for (int colum_field3 = 0; colum_field3 < args_more_title.Length; colum_field3++)
{
AODL.Document.Content.Tables.Cell cell = table.CreateCell();
//cell.OfficeValueType ="float";
AODL.Document.Content.Text.Paragraph paragraph = new AODL.Document.Content.Text.Paragraph(spreadsheetDocument);
string text = (string)args_more_title[colum_field3].ToString().Trim();
paragraph.TextContent.Add(new AODL.Document.Content.Text.SimpleText(spreadsheetDocument, text));
cell.Content.Add(paragraph);
cell.OfficeValueType = "string";
cell.OfficeValue = text;
table.InsertCellAt(files_field, colum_field3 + args_names_field.Length + title_field_text, cell);
}
}
files_field++;
string texto = "";
while (lector.Read())
{
for (int colum_field = 0; colum_field < args_names_field.Length; colum_field++)
{
AODL.Document.Content.Tables.Cell cell = table.CreateCell();
//cell.OfficeValueType ="float";
AODL.Document.Content.Text.Paragraph paragraph = new AODL.Document.Content.Text.Paragraph(spreadsheetDocument);
string text = lector[(string)args_names_field[colum_field]].ToString().Trim();
paragraph.TextContent.Add(new AODL.Document.Content.Text.SimpleText(spreadsheetDocument, text));
cell.Content.Add(paragraph);
cell.OfficeValueType = (string)args_type_field [colum_field];
cell.OfficeValue = text;
table.InsertCellAt(files_field, colum_field, cell);
}
if (typetext == true)
{
texto = (string)lector[name_field_text]; // puede ser una campo de la base de datos tipo Text
char[] delimiterChars = { '\n' }; // delimitador de Cadenas
char[] delimiterChars1 = { ';' }; // delimitador de Cadenas
//string texto = "1;daniel; ;olivares;cuevas";
//"2;genaro;cuevas;bazaldua\n"+
//"3;gladys;perez;orellana\n";
string[] words = texto.Split(delimiterChars); // Separa las Cadenas
if (texto != "")
{
// Recorre la variable
foreach (string s in words)
{
if (s.Length > 0)
{
string texto1 = (string)s;
string[] words1 = texto1.Split(delimiterChars1);
//for (int i = 1; i <= 6; i++){
int i = 0;
int i2 = 1;
foreach (string s1 in words1)
{
//Console.WriteLine(s1.ToString());
if (i2 <= args_field_text.Length)
{
array_field_text[i] = s1.ToString();
}
i++;
i2++;
}
}
}
for (int i = 0; i < array_field_text.Length; i++)
{
//Console.WriteLine(array_field_text[i]);
AODL.Document.Content.Tables.Cell cell = table.CreateCell();
//cell.OfficeValueType ="float";
AODL.Document.Content.Text.Paragraph paragraph = new AODL.Document.Content.Text.Paragraph(spreadsheetDocument);
string text = (string)array_field_text[i];
paragraph.TextContent.Add(new AODL.Document.Content.Text.SimpleText(spreadsheetDocument, text));
cell.Content.Add(paragraph);
cell.OfficeValueType = "string";
cell.OfficeValue = text;
table.InsertCellAt(files_field, i + args_names_field.Length, cell);
}
}
else
{
}
}
files_field++;
}
conexion.Close();
//Insert table into the spreadsheet document
spreadsheetDocument.TableCollection.Add(table);
spreadsheetDocument.SaveTo("export.ods");
// open the document automatic
System.Diagnostics.Process.Start("export.ods");
}catch (NpgsqlException ex) {
MessageDialog msgBoxError = new MessageDialog(MyWinError, DialogFlags.DestroyWithParent,
MessageType.Error,
ButtonsType.Close, "PostgresSQL error: {0}", ex.Message);
msgBoxError.Run(); msgBoxError.Destroy();
}
/*
* // Leyendo el archivo .ods
* spreadsheetDocument.Load("export.ods");
* Assert.IsNotNull(spreadsheetDocument.TableCollection, "Table collection must exits.");
* //Assert.IsTrue(spreadsheetDocument.TableCollection.Count == 6, "There must be 3 tables available.");
*
* int paso = spreadsheetDocument.TableCollection.Count;
*
* Console.WriteLine(paso.ToString());
* int i2 = 0; // current row index
* int ii = 0; // current cell index
* string innerText = ""; // current inner text
* try{
* //Assert.IsTrue(spreadsheetDocument.TableCollection[0].Rows.Count == 5, "There must be 6 rows available.");
* for(i2= 0; i2 < spreadsheetDocument.TableCollection[0].Rows.Count; i2++){
* string contents = "Row " + i2 + ": ";
* //Assert.IsTrue(spreadsheetDocument.TableCollection[0].Rows[i2].Cells.Count == 3, "There must be 3 cells available.");
* for(ii = 0; ii < spreadsheetDocument.TableCollection[0].Rows[i2].Cells.Count; ii++){
* innerText = spreadsheetDocument.TableCollection[0].Rows[i2].Cells[ii].Node.InnerText;
* if (spreadsheetDocument.TableCollection[0].Rows[i2].Cells[ii].OfficeValue != null){
* contents += spreadsheetDocument.TableCollection[0].Rows[i2].Cells[ii].OfficeValue.ToString() + " ";
* }else{
* contents += innerText + " ";
* }
* }
* Console.WriteLine(contents);
* }
* }catch(System.Exception ex){
* string where = "occours in Row " + i2.ToString() + " and cell " + ii.ToString() + " last cell content " + innerText + "\n\n";
* Console.WriteLine(where + ex.Message + "\n\n" + ex.StackTrace);
* }
*/
/*
* // Usando libreria SmartXLS
* WorkBook book = new WorkBook();
*
* try{
* //Sets the number of worksheets in this workbook
* book.NumSheets = 2;
* // set sheet names
* book.setSheetName(0, "hoja1"); // renombrando la pestaña
* book.setSheetName(1, "hoja2"); // renombrando la pestaña
* book.Sheet = 0;
* // book.setText(Fila, columna , "texto");
* book.setText(0, 0, "foliodeservicio");
* book.setText(0, 1, "descripcion_producto");
* book.setText(0, 2, "idproducto");
* book.setText(0, 3, "cantidadaplicada");
* book.setText(0, 4, "preciounitario");
* book.setText(0, 5, "ppcantidad");
* book.setText(0, 6, "fechcreacion");
* book.setText(0, 7, "descripcion_admisiones");
* book.setText(0, 8, "idtipoadmision");
* book.setText(0, 9, "descripcion_grupo_producto");
* book.setText(0, 10, "aplicar_iva");
*
* NpgsqlConnection conexion;
* conexion = new NpgsqlConnection (connectionString+nombrebd);
* // Verifica que la base de datos este conectada
* try{
* conexion.Open ();
* NpgsqlCommand comando;
* comando = conexion.CreateCommand ();
* comando.CommandText = "SELECT osiris_erp_cobros_deta.folio_de_servicio AS foliodeservicio,descripcion_producto,to_char(osiris_erp_cobros_deta.id_producto,'999999999999') AS idproducto, "+
* "to_char(osiris_erp_cobros_deta.cantidad_aplicada,'99999.99') AS cantidadaplicada,to_char(osiris_erp_cobros_deta.precio_producto,'99999999.99') AS preciounitario,"+
* "to_char(osiris_erp_cobros_deta.cantidad_aplicada * osiris_erp_cobros_deta.precio_producto,'99999999.99') AS ppcantidad,"+
* "to_char(osiris_erp_cobros_deta.fechahora_creacion,'dd-MM-yyyy HH24:mi:ss') AS fechcreacion, osiris_his_tipo_admisiones.descripcion_admisiones,"+
* "osiris_erp_cobros_deta.id_tipo_admisiones AS idtipoadmision,descripcion_grupo_producto,osiris_productos.aplicar_iva," +
* "to_char(osiris_erp_cobros_deta.id_secuencia,'9999999999') AS secuencia " +
* "FROM osiris_erp_cobros_deta,osiris_productos,osiris_his_tipo_admisiones,osiris_grupo_producto " +
* "WHERE osiris_erp_cobros_deta.id_producto = osiris_productos.id_producto " +
* "AND osiris_erp_cobros_deta.id_tipo_admisiones = osiris_his_tipo_admisiones.id_tipo_admisiones " +
* "AND osiris_productos.id_grupo_producto = osiris_grupo_producto.id_grupo_producto " +
* "AND osiris_erp_cobros_deta.eliminado = false " +
* "AND osiris_erp_cobros_deta.folio_de_servicio IN('"+numeroatencion_+"') " +
* "ORDER BY to_char(osiris_erp_cobros_deta.fechahora_creacion,'yyyy-MM-dd HH24:mm:ss'),osiris_erp_cobros_deta.id_tipo_admisiones ASC," +
* "osiris_productos.id_grupo_producto;";
* comando.ExecuteNonQuery(); comando.Dispose();
* //Console.WriteLine(comando.CommandText);
* NpgsqlDataReader lector = comando.ExecuteReader ();
* while (lector.Read()){
* book.setNumber(number_file, 0, int.Parse(numeroatencion_));
* book.setText(number_file, 1, (string) lector["descripcion_producto"]);
* book.setText(number_file, 2, (string) lector["idproducto"]);
* number_file++;
* }
* conexion.Close();
* book.write("export.xls");
*
* System.Diagnostics.Process.Start("export.xls");
*
*
* }catch(NpgsqlException ex){
* MessageDialog msgBoxError = new MessageDialog (MyWinError,DialogFlags.DestroyWithParent,
* MessageType.Error,
* ButtonsType.Close,"PostgresSQL error: {0}",ex.Message);
* msgBoxError.Run (); msgBoxError.Destroy();
* }
*
*
* }catch (System.Exception ex){
* Console.Error.WriteLine(ex);
* }
*/
}
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 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 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 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 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 RowAndCellIterate()
{
string file = AARunMeFirstAndOnce.inPutFolder+@"simpleCalc.ods";
_spreadsheetDocument4 = new SpreadsheetDocument();
_spreadsheetDocument4.Load(file);
Assert.IsNotNull(_spreadsheetDocument4.TableCollection, "Table collection must exits.");
Assert.IsTrue(_spreadsheetDocument4.TableCollection.Count == 3, "There must be 3 tables available.");
int i = 0; // current row index
int ii = 0; // current cell index
string innerText = ""; // current inner text
try
{
Assert.IsTrue(_spreadsheetDocument4.TableCollection[0].Rows.Count == 6, "There must be 6 rows available.");
for(i = 0; i < _spreadsheetDocument4.TableCollection[0].Rows.Count; i++)
{
string contents = "Row " + i + ": ";
Assert.IsTrue(_spreadsheetDocument4.TableCollection[0].Rows[i].Cells.Count == 3, "There must be 3 cells available.");
for(ii = 0; ii < _spreadsheetDocument4.TableCollection[0].Rows[i].Cells.Count; ii++)
{
innerText = _spreadsheetDocument4.TableCollection[0].Rows[i].Cells[ii].Node.InnerText;
if (_spreadsheetDocument4.TableCollection[0].Rows[i].Cells[ii].OfficeValue != null)
{
contents += _spreadsheetDocument4.TableCollection[0].Rows[i].Cells[ii].OfficeValue.ToString() + " ";
}
else
{
contents += innerText + " ";
}
}
Console.WriteLine(contents);
}
}
catch(Exception ex)
{
string where = "occours in Row " + i.ToString() + " and cell " + ii.ToString() + " last cell content " + innerText + "\n\n";
Console.WriteLine(where + ex.Message + "\n\n" + ex.StackTrace);
}
}
public void CreateTableFormatedText()
{
//Create new spreadsheet document
_spreadsheetDocument3 = new SpreadsheetDocument();
_spreadsheetDocument3.Load(AARunMeFirstAndOnce.inPutFolder+@"blank.ods");
//Create a new table
Table table = new Table(_spreadsheetDocument3, "First", "tablefirst");
table.Rows.Add(new Row(table));
//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(
_spreadsheetDocument3);
//Create some Formated text
FormatedText fText = new FormatedText(_spreadsheetDocument3, "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
_spreadsheetDocument3.TableCollection.Add(table);
// Test inserted content
Object insertedText = ((Paragraph)_spreadsheetDocument3.TableCollection[0].Rows[2].Cells[3].Content[0]).TextContent[0];
Assert.AreEqual(fText, insertedText as FormatedText);
}
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 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 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 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 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 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 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"));
}
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 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"));
}
