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);
}
/// <summary>
/// create the row header of the data table of the chart
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
private RowHeader CreateRowHeader(Table table)
{
RowHeader rowheader = new RowHeader (table);
int startColumnIndex = m_tableData.startcell .columnIndex ;
int endColumnIndex = m_tableData.endcell .columnIndex ;
Row row = new Row (table);
Cell cell = this.CreateNullStringCell (table);
row.Cells .Add (cell);
for(int i=startColumnIndex; i<=endColumnIndex; i++)
{
Cell tempCell = new Cell (table.Document);
tempCell.OfficeValueType ="string";
Paragraph paragraph = new Paragraph (m_document);
string content =((char)('A'+i-1)).ToString ()+"ĮŠ";
paragraph.TextContent .Add (new SimpleText (m_document ,content));
tempCell.Content .Add (paragraph);
row.Cells .Add (tempCell);
}
rowheader.RowCollection .Add (row);
return rowheader;
}
/// <summary>
/// Gets paragraph from cell
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private Paragraph GetParagraph(Cell cell)
{
if (cell.Content.Count == 0)
return null;
Paragraph p = cell.Content[0] as Paragraph;
return p;
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <returns>the null string cell</returns>
/// <example ><texp/></example>
private Cell CreateNullStringCell(Table table)
{
Cell cell = new Cell (table.Document);
Paragraph paragraph = new Paragraph (m_document );
cell.Content .Add (paragraph);
return cell;
}
/// <summary>
/// Creates the text document table.
/// </summary>
/// <param name="document">The document.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="styleName">Name of the style.</param>
/// <param name="rows">The rows.</param>
/// <param name="columns">The columns.</param>
/// <param name="width">The width.</param>
/// <param name="useTableRowHeader">if set to <c>true</c> [use table row header].</param>
/// <param name="useBorder">The useBorder.</param>
/// <returns></returns>
public static Table CreateTextDocumentTable(AODL.Document.TextDocuments.TextDocument document, string tableName, string styleName, int rows, int columns, double width, bool useTableRowHeader, bool useBorder)
{
string tableCnt = document.DocumentMetadata.TableCount.ToString();
Table table = new Table(document, tableName, styleName);
table.TableStyle.TableProperties.Width = width.ToString().Replace(",",".")+"cm";
for(int i=0; i<columns; i++)
{
Column column = new Column(table, "co"+tableCnt+i.ToString());
column.ColumnStyle.ColumnProperties.Width = GetColumnCellWidth(columns, width);
table.ColumnCollection.Add(column);
}
if(useTableRowHeader)
{
rows--;
RowHeader rowHeader = new RowHeader(table);
Row row = new Row(table, "roh1"+tableCnt);
for(int i=0; i<columns; i++)
{
Cell cell = new Cell(table, "rohce"+tableCnt+i.ToString());
if(useBorder)
cell.CellStyle.CellProperties.Border = Border.NormalSolid;
row.CellCollection.Add(cell);
}
rowHeader.RowCollection.Add(row);
table.RowHeader = rowHeader;
}
for(int ir=0; ir<rows; ir++)
{
Row row = new Row(table, "ro"+tableCnt+ir.ToString());
for(int ic=0; ic<columns; ic++)
{
Cell cell = new Cell(table, "ce"+tableCnt+ir.ToString()+ic.ToString());
if(useBorder)
cell.CellStyle.CellProperties.Border = Border.NormalSolid;
row.CellCollection.Add(cell);
}
table.RowCollection.Add(row);
}
return table;
}
/// <summary>
/// Inserts the cell at the specified position. Both indexes are 1-based indexes!
/// The RowCollection, the rows CellCollection and the ColumnCollection
/// will be resized automatically.
/// </summary>
/// <param name="rowIndex">Index of the row.</param>
/// <param name="columnIndex">Index of the column.</param>
/// <param name="cell">The cell.</param>
public void InsertCellAt(int rowIndex, int columnIndex, Cell cell)
{
if(this.RowCollection.Count <= rowIndex)
{
for(int i=0; i < rowIndex-this.RowCollection.Count; i++)
this.RowCollection.Add(new Row(this, "row"+this.RowCollection.Count.ToString()+i.ToString()));
this.RowCollection[rowIndex-1].InsertCellAt(columnIndex-1, cell);
cell.Row = this.RowCollection[rowIndex-1];
}
else if(this.RowCollection.Count+1 == rowIndex)
{
Row row = new Row(this, this.RowCollection[this.RowCollection.Count-1].StyleName);
row.InsertCellAt(columnIndex-1, cell);
cell.Row = row;
this.RowCollection.Add(row);
}
else
{
this.RowCollection[rowIndex-1].InsertCellAt(columnIndex-1, cell);
cell.Row = this.RowCollection[rowIndex-1];
}
}
/// <summary>
/// Sets the cell properties.
/// </summary>
/// <param name="cell">The cell.</param>
/// <param name="pdfCell">The PDF cell.</param>
/// <returns>The PDF cell with converted odf cell properties.</returns>
private iTextSharp.text.pdf.PdfPCell SetCellProperties(Cell cell, iTextSharp.text.pdf.PdfPCell pdfCell)
{
try
{
return null;
}
catch(Exception)
{
throw;
}
}
private void BothHasLabels()
{
Row row = new Row (table);
Cell cell = new Cell (table.Document);
Paragraph paragraph = new Paragraph (m_document );
cell.Content .Add (paragraph);
row.Cells .Add (cell);
RowHeader rowheader = new RowHeader (table);
for(int i=startColumnIndex; i<endColumnIndex ; i++)
{
Cell cellTemp = m_tableData.table .Rows [startRowIndex-1].Cells[i];
string cellRepeating = cellTemp.ColumnRepeating ;
int cellRepeated=0;
if (cellRepeating!=null)
cellRepeated= Int32.Parse (cellTemp.ColumnRepeating);
if (cellRepeated >1)
{
for(int j=0; j<cellRepeated-1; j++)
{
row.Cells .Add (cellTemp);
i++;
}
}
row.Cells .Add (cellTemp);
}
rowheader.RowCollection .Add (row);
table.RowHeader =rowheader;
for(int i=startRowIndex; i<endRowIndex; i++)
{
Row tempRow = new Row (table);
for(int j=startColumnIndex-1;j<endColumnIndex;j++)
{
Cell cellTemp = m_tableData.table .Rows [i].Cells [j];
string cellRepeating = cellTemp.ColumnRepeating;
int cellRepeated =0;
if (cellRepeating!=null)
cellRepeated= Int32.Parse (cellTemp.ColumnRepeating );
if (cellRepeated>1)
{
for(int m=0; m<cellRepeated-1; m++)
{
tempRow.Cells .Add (cellTemp);
i++;
}
}
tempRow.Cells .Add (cellTemp);
}
table.Rows .Add (tempRow);
}
}
private void CopyCellContens(Cell source, Cell destination)
{
foreach (IContent content in source.Content)
{
IContent clonedContent = new MainContentProcessor(content.Document)
.CreateContent(content.Node);
destination.Content.Add(clonedContent);
}
}
/// <summary>
/// Create a new cell within this table which use the standard style.
/// The cell isn't part of the table until you insert it
/// via the InsertCellAt(int rowIndex, int columnIndex, Cell cell)
/// </summary>
/// <returns>The new cell</returns>
public Cell CreateCell()
{
Cell cell = new Cell(this.Document);
return cell;
}
/// <summary>
/// gets the rowIndex and colIndex of the chart according to the name of the cell
/// </summary>
/// <param name="cellName"></param>
/// <param name="tableData"></param>
/// <returns></returns>
public CellPos GetCellPos(string cellName,Table tableData)
{
int i = 0;
int columnIndex = 0;
int rowIndex = 0;
char[] columnStr = new char [2];
string rowStr = null;
foreach(char c in cellName)
{
if (c>='A'&&c<='Z')
{
columnStr[i]=c;
i++;
}
}
if (i==1)
columnIndex = columnStr[0]-'A';
if (i==2)
columnIndex=(columnStr[0]-'A')*26 +(columnStr[1]-'A');
columnIndex = columnIndex+1;
rowStr = cellName.Substring (i);
rowIndex = Int32.Parse (rowStr);
Cell cell = null;
if (rowIndex<=tableData.Rows .Count )
{
Row row = tableData.Rows [rowIndex-1];
if (columnIndex<=row.Cells.Count )
{
cell= tableData.Rows [rowIndex-1].Cells [columnIndex-1];
}
}
if (cell==null)
{
cell = new Cell(_document);
tableData.InsertCellAt (rowIndex,columnIndex,cell);
}
CellPos pos = new CellPos ();
pos.cell =cell;
pos.columnIndex = columnIndex;
pos.rowIndex = rowIndex;
return pos;
}
/// <summary>
/// Creates the tables.
/// </summary>
/// <param name="lines">The lines.</param>
private void CreateTables(ArrayList lines)
{
string unicodeDelimiter = "\u00BF"; // turned question mark
if (lines != null)
{
Table table = TableBuilder.CreateSpreadsheetTable(
(SpreadsheetDocument)this._document, "Table1", "table1");
//First line must specify the used delimiter
string delimiter = lines[0] as string;
lines.RemoveAt(0);
try
{
//Perform lines
foreach(string line in lines)
{
string lineContent = line.Replace(delimiter, unicodeDelimiter);
string[] cellContents = lineContent.Split(unicodeDelimiter.ToCharArray());
Row row = new Row(table);
foreach(string cellContent in cellContents)
{
Cell cell = new Cell(table.Document);
Paragraph paragraph = ParagraphBuilder.CreateSpreadsheetParagraph(this._document);
paragraph.TextContent.Add(new SimpleText(this._document, cellContent));
cell.Content.Add(paragraph);
row.InsertCellAt(row.Cells.Count, cell);
}
table.Rows.Add(row);
}
}
catch(Exception ex)
{
throw new AODLException("Error while proccessing the csv file.", ex);
}
this._document.Content.Add(table);
}
}
/// <summary>
/// Writes the billing items.
/// </summary>
/// <param name="billingDoc">The billing doc.</param>
private void WriteBillingItems(TextDocument billingDoc)
{
if (billingDoc != null)
{
foreach(IContent content in billingDoc.Content)
{
if (content is Table)
{
Table itemTable = (Table)content;
double priceTotal = 0;
int r = 0;
string lastCellStyleName = "";
foreach(DataRow dr in this._tblItems.Rows)
{
Row itemRow = new Row(itemTable);
int c = 0;
foreach(object obj in dr.ItemArray)
{
lastCellStyleName = "c" + r.ToString() + c.ToString();
Cell cell = new Cell(itemTable.Document, lastCellStyleName);
((CellStyle)cell.Style).CellProperties.Padding = "0.2cm";
((CellStyle)cell.Style).CellProperties.BorderBottom = "0.002cm solid #000000";
Paragraph p = ParagraphBuilder.CreateParagraphWithCustomStyle(billingDoc, "p" + r.ToString() + c.ToString());
ParagraphProperties pp = ((ParagraphStyle)p.Style).ParagraphProperties;
if (pp != null)
{
if (c == 1)
pp.Alignment = TextAlignments.center.ToString();
if (c > 1)
pp.Alignment = TextAlignments.end.ToString();
}
TextProperties tp = ((ParagraphStyle)p.Style).TextProperties;
if (tp != null)
{
tp.Bold = "bold";
tp.FontName = FontFamilies.Arial;
tp.FontSize = "11pt";
if (c < 2)
tp.Italic = "italic";
}
if (c == 4)
priceTotal += Double.Parse(obj.ToString());
p.TextContent.Add(new SimpleText(billingDoc, obj.ToString()));
cell.Content.Add(p);
itemRow.Cells.Add(cell);
c++;
}
itemTable.Rows.Add(itemRow);
r++;
}
Row priceTotalRow = new Row(itemTable);
for(int i=0; i < 4; i++)
priceTotalRow.Cells.Add(new Cell(itemTable.Document));
Cell cell1 = new Cell(itemTable.Document, lastCellStyleName);
Paragraph p1 = ParagraphBuilder.CreateParagraphWithCustomStyle(billingDoc, "ppricetotal");
((ParagraphStyle)p1.Style).ParagraphProperties.Alignment = TextAlignments.end.ToString();
((ParagraphStyle)p1.Style).TextProperties.Bold = "bold";
((ParagraphStyle)p1.Style).TextProperties.FontSize = "11pt";
((ParagraphStyle)p1.Style).TextProperties.FontName = FontFamilies.Arial;
p1.TextContent.Add(new SimpleText(billingDoc, priceTotal.ToString()));
cell1.Content.Add(p1);
priceTotalRow.Cells.Add(cell1);
itemTable.Rows.Add(priceTotalRow);
}
}
}
}
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);
* }
*/
}
/// <summary>
/// Makes a copy of a cell
/// </summary>
/// <returns></returns>
private Cell CloneCell(Cell sourceCell)
{
Cell clonedCell = new Cell(sourceCell.Document, sourceCell.StyleName);
foreach (IContent clonedContent in CloneContentCollection(
new ContentCollection(sourceCell.Content)))
{
clonedCell.Content.Add(clonedContent);
}
return clonedCell;
}
public void SetText(Cell cell, string text)
{
foreach (IContent content in cell.Content)
{
if (content is Paragraph == false)
continue;
Paragraph p = content as Paragraph;
foreach (IText cellText in p.TextContent)
{
cellText.Text = text;
}
}
}
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");
}
/// <summary>
/// Gets the cell as HTML.
/// </summary>
/// <param name="cell">The cell.</param>
/// <returns></returns>
public string GetCellAsHtml(Cell cell)
{
string html = "<td ";
try
{
if(cell != null)
{
if(cell.ColumnRepeating != null)
html += "columnspan=\""+cell.ColumnRepeating+"\" ";
string cellStyle = this.HTMLStyleBuilder.GetCellStyleAsHtml(cell.CellStyle);
if(cellStyle.Length > 0)
html += cellStyle;
int cellIndex = -1;
if(cell.Row != null)
cellIndex = cell.Row.GetCellIndex(cell);
ColumnStyle colStyle = null;
if(cellIndex != -1 && cell.Table != null)
if(cell.Table.ColumnCollection != null)
if(cell.Table.ColumnCollection.Count-1 <= cellIndex)
if(cell.Table.ColumnCollection[cellIndex].ColumnStyle != null)
colStyle = cell.Table.ColumnCollection[cellIndex].ColumnStyle;
string colHtmlStyle = this.HTMLStyleBuilder.GetColumnStyleAsHtml(colStyle);
if(colHtmlStyle.Length > 0)
html += colHtmlStyle;
html += ">\n";
string contentHtml = this.GetIContentCollectionAsHtml(cell.Content);
if(contentHtml.Length > 0)
html += contentHtml;
}
}
catch(Exception ex)
{
AODLException exception = new AODLException("Exception while trying to build a HTML string from a Cell object.");
exception.InMethod = AODLException.GetExceptionSourceInfo(new StackFrame(1, true));
exception.OriginalException = ex;
throw exception;
}
if(!html.Equals("<td "))
html += "</td>\n";
else
html = "";
return html;
}
/// <summary>
/// Creates the table cell.
/// </summary>
/// <param name="node">The node.</param>
/// <returns></returns>
private Cell CreateTableCell(XmlNode node)
{
try
{
//Create a new Cel
Cell cell = new Cell(this._document, node);
ContentCollection iColl = new ContentCollection();
//Recieve CellStyle
IStyle cellStyle = this._document.Styles.GetStyleByName(cell.StyleName);
if (cellStyle != null)
{
cell.Style = cellStyle;
}
//No need for a warning
//Create the cells content
foreach(XmlNode nodeChild in cell.Node.ChildNodes)
{
IContent iContent = this.CreateContent(nodeChild);
if (iContent != null)
{
//iColl.Add(iContent);
AddToCollection(iContent, iColl);
}
else
{
this.OnWarning(new AODLWarning("Couldn't create IContent from a table cell.", nodeChild));
}
}
cell.Node.InnerXml = "";
foreach(IContent iContent in iColl)
AddToCollection(iContent,cell.Content);
//cell.Content.Add(iContent);
return cell;
}
catch(Exception ex)
{
throw new AODLException("Exception while trying to create a Table Row.", ex);
}
}
/// <summary>
/// Gets the cell as HTML.
/// </summary>
/// <param name="cell">The cell.</param>
/// <returns></returns>
public string GetCellAsHtml(Cell cell)
{
string html = "<td ";
try
{
if (cell != null)
{
if (cell.ColumnRepeating != null)
html += "columnspan=\""+cell.ColumnRepeating+"\" ";
string cellStyle = this.HTMLStyleBuilder.GetCellStyleAsHtml(cell.CellStyle);
if (cellStyle.Length > 0)
html += cellStyle;
int cellIndex = -1;
if (cell.Row != null)
cellIndex = cell.Row.GetCellIndex(cell);
ColumnStyle colStyle = null;
if (cellIndex != -1 && cell.Row!= null && cell.Row.Table != null)
if (cell.Row.Table.ColumnCollection != null)
if (cell.Row.Table.ColumnCollection.Count > cellIndex)
if (cell.Row.Table.ColumnCollection[cellIndex].ColumnStyle != null)
colStyle = cell.Row.Table.ColumnCollection[cellIndex].ColumnStyle;
string colHtmlStyle = this.HTMLStyleBuilder.GetColumnStyleAsHtml(colStyle);
if (colHtmlStyle.Length > 0)
html += colHtmlStyle;
html += ">\n";
string contentHtml = this.GetIContentCollectionAsHtml(cell.Content);
if (contentHtml.Length > 0)
html += contentHtml;
}
}
catch(Exception ex)
{
throw new AODLException("Exception while trying to build a HTML string from a Cell object.", ex);
}
if (!html.Equals("<td "))
html += "</td>\n";
else
html = "";
return html;
}
/// <summary>
/// Inserts the cell at the given zero based position.
/// </summary>
/// <param name="position">The position.</param>
/// <param name="cell">The cell.</param>
public void InsertCellAt(int position, Cell cell)
{
// Phil Jollans 16-Feb-2008: Largely rewritten
if ( _cells.Count > position )
{
// We need to ask Lars how he intended this list to work.
// Note that Table.Row_OnRowChanged() adds cells to all rows when
// we add a cell to a single row.
_cells.RemoveAt ( position ) ;
_cells.Insert ( position, cell ) ;
}
else
{
while ( _cells.Count < position )
{
_cells.Add ( new Cell ( this.Table.Document ) ) ;
}
_cells.Add(cell);
}
}
/// <summary>
/// Inserts the cell at the specified position.
/// The RowCollection, the rows CellCollection and the ColumnCollection
/// will be resized automatically.
/// </summary>
/// <param name="rowIndex">Index of the row.</param>
/// <param name="columnIndex">Index of the column.</param>
/// <param name="cell">The cell.</param>
public void InsertCellAt(int rowIndex, int columnIndex, Cell cell)
{
while ( _rows.Count <= rowIndex )
{
_rows.Add ( new Row(this, String.Format("row{0}",_rows.Count)));
}
Row row = _rows [ rowIndex ] ;
row.InsertCellAt ( columnIndex, cell ) ;
cell.Row = row ;
}
/// <summary>
/// Creates the text document table.
/// </summary>
/// <param name="document">The document.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="styleName">Name of the style.</param>
/// <param name="rows">The rows.</param>
/// <param name="columns">The columns.</param>
/// <param name="width">The width.</param>
/// <param name="useTableRowHeader">if set to <c>true</c> [use table row header].</param>
/// <param name="useBorder">The useBorder.</param>
/// <returns></returns>
private Table CreateTextDocumentTable(
AODL.Document.TextDocuments.TextDocument document,
string tableName,
string styleName,
int rows,
int columns,
double width,
Table originalTable)
{
string tableCnt = document.DocumentMetadata.TableCount.ToString();
Table table = new Table(document, tableName, styleName);
table.TableStyle.TableProperties.Width = width.ToString().Replace(",",".")+"cm";
for(int i=0; i < columns; i++)
{
Column column = new Column(table, originalTable.ColumnCollection[i].StyleName);
//column.ColumnStyle.ColumnProperties.Width = GetColumnCellWidth(columns, width);
table.ColumnCollection.Add(column);
}
for(int ir=0; ir < rows; ir++)
{
Row row = new Row(table, originalTable.Rows[ir].StyleName);
for(int ic=0; ic < columns; ic++)
{
Cell cell = new Cell(table.Document, originalTable.Rows[ir].Cells[ic].StyleName);
//if (useBorder)
// cell.CellStyle.CellProperties.Border = Border.NormalSolid;
row.Cells.Add(cell);
}
table.Rows.Add(row);
}
return table;
}
/// <summary>
/// Inserts the cell at the given zero based position.
/// </summary>
/// <param name="position">The position.</param>
/// <param name="cell">The cell.</param>
public void InsertCellAt(int position, Cell cell)
{
if(this.CellCollection.Count <= position)
{
//3 - 5 = 6, 5 - 5 = 6
for(int i=0; i < position-this.CellCollection.Count; i++)
this.CellCollection.Add(new Cell(this.Table));
this.CellCollection.Add(cell);
}
else if(this.CellCollection.Count+1 == position)
{
this.CellCollection.Add(cell);
}
else
{
this.CellCollection.Insert(position, cell);
}
}
/// <summary>
/// Creates the table cell.
/// </summary>
/// <param name="node">The node.</param>
/// <returns></returns>
private Cell CreateTableCell(XmlNode node)
{
try
{
//Create a new Cel
Cell cell = new Cell(this._document, node);
IContentCollection iColl = new IContentCollection();
//Recieve CellStyle
IStyle cellStyle = this._document.Styles.GetStyleByName(cell.StyleName);
if(cellStyle != null)
{
int i=0;
cell.Style = cellStyle;
if(cellStyle.StyleName == "ce244")
i=1;
}
//No need for a warning
//Create the cells content
foreach(XmlNode nodeChild in cell.Node.ChildNodes)
{
IContent iContent = this.CreateContent(nodeChild);
if(iContent != null)
{
iColl.Add(iContent);
}
else
{
if(this.OnWarning != null)
{
AODLWarning warning = new AODLWarning("Couldn't create IContent from a table cell.");
warning.InMethod = AODLException.GetExceptionSourceInfo(new StackFrame(1, true));
warning.Node = nodeChild;
this.OnWarning(warning);
}
}
}
cell.Node.InnerXml = "";
foreach(IContent iContent in iColl)
cell.Content.Add(iContent);
return cell;
}
catch(Exception ex)
{
AODLException exception = new AODLException("Exception while trying to create a Table Row.");
exception.InMethod = AODLException.GetExceptionSourceInfo(new StackFrame(1, true));
exception.Node = node;
exception.OriginalException = ex;
throw exception;
}
}
/// <summary>
/// Gets the index of the cell.
/// </summary>
/// <param name="cell">The cell.</param>
/// <returns>The index of the cell wthin the cell collection. If the
/// cell isn't part of the collection -1 will be returned.</returns>
public int GetCellIndex(Cell cell)
{
if (cell != null && this.Cells != null)
{
for(int i=0; i<this.Cells.Count; i++)
if (this.Cells[i].Equals(cell))
return i;
}
return -1;
}
/// <summary>
/// Create a new cell within this table which use the standard style.
/// The cell isn't part of the table until you insert it
/// via the InsertCellAt(int rowIndex, int columnIndex, Cell cell)
/// </summary>
/// <returns>The new cell</returns>
public Cell CreateCell()
{
Cell cell = new Cell(this);
return cell;
}
/// <summary>
/// create the row serial cell
/// </summary>
/// <param name="table"></param>
/// <param name="SerialNum"></param>
/// <returns></returns>
private Cell CreateRowSerialCell(Table table,int SerialNum)
{
Cell cell = new Cell (table.Document);
cell.OfficeValueType ="string";
Paragraph paragraph = new Paragraph (m_document);
string content = SerialNum.ToString ()+"ŠŠ";
paragraph.TextContent .Add (new SimpleText (m_document,content));
cell.Content .Add (paragraph);
return cell;
}
/// <summary>
/// Create a new cell within this table which will have a custom
/// cellstyle.
/// The cell isn't part of the table until you insert it
/// via the InsertCellAt(int rowIndex, int columnIndex, Cell cell)
/// </summary>
/// <param name="styleName">Name of the style.</param>
/// <returns>The new cell.</returns>
public Cell CreateCell(string styleName)
{
Cell cell = new Cell(this, styleName);
return cell;
}
/// <summary>
/// Create a new cell within this table which will have a custom
/// cellstyle and the cell content is type of the given office value typ.
/// The cell isn't part of the table until you insert it
/// via the InsertCellAt(int rowIndex, int columnIndex, Cell cell)
/// </summary>
/// <param name="styleName">Name of the style.</param>
/// <param name="officeValueType">Type of the office value.</param>
/// <returns></returns>
public Cell CreateCell(string styleName, string officeValueType)
{
Cell cell = new Cell(this, styleName, officeValueType);
return cell;
}
private void WriteTable(IMultipleRowsProducer rp, Table table, IGenerationContext context)
{
DataRow[] rows = rp.GetValue(context).ToArray();
if (rows.Length > 0)
{
DataTable ptab = rows.First().Table;
for (int iCol = 0; iCol < ptab.Columns.Count; iCol++)
{
table.ColumnCollection.Add(new Column(table, string.Empty));
}
for (int iRow = -1; iRow < rows.Length; iRow++)
{
Row row = new Row(table);
table.RowCollection.Add(row);
for (int iCol = 0; iCol < ptab.Columns.Count; iCol++)
{
Cell cell = new Cell(table);
object value = (iRow == -1) ? ptab.Columns[iCol].ColumnName : rows[iRow][iCol];
CreateAddSimpleText(table.Document, cell.Content, value);
row.CellCollection.Add(cell);
}
}
}
}
