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 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 static void AddCell(this Table tbl, int row, int col, string text, Color back)
{
var c = tbl.CreateCell();
c.CellStyle = new CellStyle(tbl.Document)
{
CellProperties = { BackgroundColor = back.ToHex() }
};
var p = ParagraphBuilder.CreateSpreadsheetParagraph(tbl.Document);
var t = new FormatedText(tbl.Document, "t" + row + col, text);
p.TextContent.Add(t);
c.Content.Add(p);
tbl.InsertCellAt(row, col, c);
}
/// <summary>
///
/// </summary>
/// <param name="title"></param>
public void SetTitle(String title)
{
Cell titolo = new Cell(document, "cell001");
titolo.OfficeValueType = "string";
Paragraph titoloParagraph = ParagraphBuilder.CreateSpreadsheetParagraph(document);
FormatedText fText = new FormatedText(document, "T1", title);
fText.TextStyle.TextProperties.Bold = "bold";
fText.TextStyle.TextProperties.FontSize = "20pt";
titoloParagraph.TextContent.Add(fText);
titolo.Content.Add(titoloParagraph);
table.Rows.Add(new Row(table));
table.Rows[0].Cells.Add(titolo);
this.title = title;
currentRow++;
}
/// <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);
Paragraph paragraph = ParagraphBuilder.CreateSpreadsheetParagraph(this._document);
paragraph.TextContent.Add(new SimpleText(this._document, cellContent));
cell.Content.Add(paragraph);
row.InsertCellAt(row.CellCollection.Count, cell);
}
table.RowCollection.Add(row);
}
}
catch (Exception ex)
{
AODLException aodlExeception = new AODLException("Error while proccessing the csv file.");
aodlExeception.InMethod = AODLException.GetExceptionSourceInfo(new StackFrame(1, true));
aodlExeception.OriginalException = ex;
throw aodlExeception;
}
this._document.Content.Add(table);
}
}
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>
///
/// </summary>
/// <param name="row"></param>
private void SetColumnsName(DataRow row)
{
table.Rows.Add(new Row(table));
foreach (String columnName in row.ItemArray)
{
Cell columnHeader = new Cell(document, "cell003");
columnHeader.OfficeValueType = "string";
columnHeader.CellStyle.CellProperties.Border = Border.HeavySolid;
columnHeader.CellStyle.CellProperties.BackgroundColor = "#D0B1A1";
Paragraph titoloParagraph = ParagraphBuilder.CreateSpreadsheetParagraph(document);
FormatedText fText = new FormatedText(document, "T3", columnName);
fText.TextStyle.TextProperties.Bold = "bold";
fText.TextStyle.TextProperties.FontSize = "10pt";
titoloParagraph.TextContent.Add(fText);
columnHeader.Content.Add(titoloParagraph);
table.Rows[currentRow].Cells.Add(columnHeader);
}
currentRow++;
}
/// <summary>
/// Metodo per l'impostazione delle informazioni aggiuntive di un report
/// </summary>
/// <param name="additionalInformation"></param>
/// <param name="sheet"></param>
/// <param name="reportKey"></param>
private void SetAdditionalInformation(String additionalInformation, Table sheet, String reportKey)
{
Cell addInfoCell = new Cell(sheet.Document, "cell002");
addInfoCell.OfficeValueType = "string";
Paragraph subTitleParagraph = ParagraphBuilder.CreateSpreadsheetParagraph(sheet.Document);
FormatedText fText = new FormatedText(sheet.Document, "T2", additionalInformation);
fText.TextStyle.TextProperties.Bold = "bold";
fText.TextStyle.TextProperties.FontSize = "15pt";
if (reportKey == "RegistroAccessiPublish")
{
fText.TextStyle.TextProperties.Bold = String.Empty;
addInfoCell.CellStyle.CellProperties.BackgroundColor = "#DDDDDD";
}
subTitleParagraph.TextContent.Add(fText);
addInfoCell.Content.Add(subTitleParagraph);
sheet.Rows.Add(new Row(sheet));
sheet.Rows[2].Cells.Add(addInfoCell);
}
/// <summary>
/// Metodo per l'impostazione del titolo di un report
/// </summary>
/// <param name="title">Titolo da assegnare al foglio</param>
/// <param name="sheet">Foglio a cui aggiungere il titolo</param>
private void SetTitle(String title, Table sheet, String reportKey)
{
Cell titleCell = new Cell(sheet.Document, "cell001");
titleCell.OfficeValueType = "string";
Paragraph titleParagraph = ParagraphBuilder.CreateSpreadsheetParagraph(sheet.Document);
FormatedText fText = new FormatedText(sheet.Document, "T1", title);
fText.TextStyle.TextProperties.Bold = "bold";
fText.TextStyle.TextProperties.FontSize = "16pt";
if (reportKey == "RegistroAccessiPublish")
{
fText.TextStyle.TextProperties.Bold = String.Empty;
titleCell.CellStyle.CellProperties.BackgroundColor = "#DDDDDD";
}
titleParagraph.TextContent.Add(fText);
titleCell.Content.Add(titleParagraph);
sheet.Rows.Add(new Row(sheet));
sheet.Rows[0].Cells.Add(titleCell);
}
/// <summary>
/// Metodo per l'aggiunta dell'header ad un foglio Excel
/// </summary>
/// <param name="header">Header da aggiungere</param>
/// <param name="sheet">Foglio a cui aggiungere l'instestazione</param>
private void AddHeaderRow(DocsPaVO.Report.HeaderColumnCollection header, Table sheet, String reportKey)
{
sheet.Rows.Add(new Row(sheet));
int actualRow = sheet.Rows.Count - 1;
int actualCol = 0;
foreach (var headerColumn in header)
{
Cell headerCell = new Cell(sheet.Document, "cell003");
headerCell.OfficeValueType = "string";
headerCell.CellStyle.CellProperties.Border = Border.HeavySolid;
headerCell.CellStyle.CellProperties.BackgroundColor = "#D0B1A1";
Paragraph paragraph = ParagraphBuilder.CreateSpreadsheetParagraph(sheet.Document);
FormatedText fText = new FormatedText(sheet.Document, "T3", headerColumn.ColumnName);
fText.TextStyle.TextProperties.Bold = "bold";
fText.TextStyle.TextProperties.FontName = "Arial";
fText.TextStyle.TextProperties.FontSize = "11pt";
paragraph.TextContent.Add(fText);
headerCell.Content.Add(paragraph);
sheet.Rows[actualRow].Cells.Add(headerCell);
if (reportKey == "RegistroAccessiPublish")
{
headerCell.CellStyle.CellProperties.BackgroundColor = String.Empty;
fText.TextStyle.TextProperties.FontName = "ArialNarrow";
fText.TextStyle.TextProperties.FontColor = "#006699";
sheet.ColumnCollection[actualCol].ColumnStyle.ColumnProperties.Width = String.Format("{0}px", (headerColumn.ColumnSize * 2).ToString());
Cell titleCell = new Cell(sheet.Document, "cell001");
Cell subTitleCell = new Cell(sheet.Document, "cell002");
Cell addInfoCell = new Cell(sheet.Document, "cell000");
titleCell.CellStyle.CellProperties.BackgroundColor = "#DDDDDD";
subTitleCell.CellStyle.CellProperties.BackgroundColor = "#DDDDDD";
addInfoCell.CellStyle.CellProperties.BackgroundColor = "#DDDDDD";
sheet.Rows[0].Cells.Add(titleCell);
sheet.Rows[1].Cells.Add(subTitleCell);
sheet.Rows[2].Cells.Add(addInfoCell);
}
actualCol++;
}
}
public void CreateTableFormatedText()
{
//Create new spreadsheet document
_spreadsheetDocument3 = new SpreadsheetDocument();
using (IPackageReader reader = new OnDiskPackageReader())
{
_spreadsheetDocument3.Load(AARunMeFirstAndOnce.inPutFolder + @"blank.ods", new OpenDocumentImporter(reader));
}
//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);
}
/// <summary>
/// Metodo per l'aggiunta delle righe al report
/// </summary>
/// <param name="sheet">Foglio a cui aggiungere le righe</param>
/// <param name="reportRows">Righe del report</param>
private void AddReportData(Table sheet, DocsPaVO.Report.ReportMapRow reportRows)
{
foreach (var row in reportRows.Rows)
{
sheet.Rows.Add(new Row(sheet));
foreach (var column in row.Columns)
{
Cell columnItem = new Cell(sheet.Document, "cell004");
columnItem.OfficeValueType = "string";
columnItem.CellStyle.CellProperties.Border = Border.HeavySolid;
Paragraph paragraph = ParagraphBuilder.CreateSpreadsheetParagraph(sheet.Document);
FormatedText fText = new FormatedText(sheet.Document, "T4", column.Value);
//fText.TextStyle.TextProperties.Bold = "bold";
fText.TextStyle.TextProperties.FontSize = "11pt";
paragraph.TextContent.Add(fText);
columnItem.Content.Add(paragraph);
int rowNum = sheet.Rows.Count - 1;
sheet.Rows[rowNum].Cells.Add(columnItem);
}
}
}
/// <summary>
///
/// </summary>
/// <param name="subtitle"></param>
public void SetSubtitle(String subtitle)
{
if (!title.Equals(""))
{
Cell sottoTitolo = new Cell(document, "cell002");
sottoTitolo.OfficeValueType = "string";
Paragraph sottoTitoloParagraph = ParagraphBuilder.CreateSpreadsheetParagraph(document);
FormatedText fText = new FormatedText(document, "T2", subtitle);
fText.TextStyle.TextProperties.Bold = "bold";
fText.TextStyle.TextProperties.FontSize = "15pt";
sottoTitoloParagraph.TextContent.Add(fText);
sottoTitolo.Content.Add(sottoTitoloParagraph);
table.Rows.Add(new Row(table));
table.Rows[1].Cells.Add(sottoTitolo);
currentRow++;
}
else
{
throw new Exception("Try to insert subtitle before title, please insert title first");
}
}
public void CreateSimpleTable()
{
//Create new spreadsheet document
_spreadsheetDocument2 = new SpreadsheetDocument();
using (IPackageReader reader = new OnDiskPackageReader())
{
_spreadsheetDocument2.Load(AARunMeFirstAndOnce.inPutFolder + @"blank.ods", new OpenDocumentImporter(reader));
}
//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.Value;
Assert.AreEqual(text, cellText);
}
/// <summary>
/// First Row of data must contains columns' names
/// </summary>
/// <param name="data"></param>
public void SetData(DataSet data)
{
int dataRows = 0;
if (data.Tables[0].Rows[0] != null)
{
SetColumnsName(data.Tables[0].Rows[0]);
foreach (DataRow row in data.Tables[0].Rows)
{
if (dataRows != 0)
{
table.Rows.Add(new Row(table));
foreach (String columnName in row.ItemArray)
{
Cell columnItem = new Cell(document, "cell004");
columnItem.OfficeValueType = "string";
columnItem.CellStyle.CellProperties.Border = Border.HeavySolid;
//columnItem.CellStyle.CellProperties.BackgroundColor = "#D0B1A1";
Paragraph titoloParagraph = ParagraphBuilder.CreateSpreadsheetParagraph(document);
FormatedText fText = new FormatedText(document, "T4", columnName);
fText.TextStyle.TextProperties.Bold = "bold";
fText.TextStyle.TextProperties.FontSize = "10pt";
titoloParagraph.TextContent.Add(fText);
columnItem.Content.Add(titoloParagraph);
table.Rows[currentRow].Cells.Add(columnItem);
}
currentRow++;
}
dataRows++;
}
}
else
{
throw new Exception("Columns' names not found");
}
}
