private static Row CreateResultsHeaderRow(int rowIndex)
{
var index = -1;
var headerRow = new Row {
RowIndex = (uint)rowIndex
};
var firstcell = CreateCell("Email Address", ref index, rowIndex, 2);
headerRow.AppendChild(firstcell);
var secondCell = CreateCell("Authenticated?", ref index, rowIndex, 2);
headerRow.AppendChild(secondCell);
var thirdCell = CreateCell("Correct Answers", ref index, rowIndex, 2);
headerRow.AppendChild(thirdCell);
var lastCell = CreateCell("Date", ref index, rowIndex, 2);
headerRow.AppendChild(lastCell);
return(headerRow);
}
private static Row CreateProjectRow(DateTime firstDayOfMonth, DateTime lastDayOfMonth, Project project, List <string> mergeableRows, DateTime monthDate, int rowIndex)
{
int index = -1;
Row projectRow = new Row();
projectRow.RowIndex = (UInt32)rowIndex;
DateTime dt = firstDayOfMonth;
//adding first cell
mergeableRows.Add(ColumnLetter(index + 1) + rowIndex);
Cell firstcell = CreateCell(" Activity report for " +
project.Title + " during " + monthDate.ToString("MMMM yyyy"), ref index, rowIndex, 4);
projectRow.AppendChild(firstcell);
while (dt.Date <= lastDayOfMonth.Date)
{
string text = "";
Cell newcell = CreateCell(text, ref index, rowIndex, 4);
dt = dt.AddDays(1);
projectRow.AppendChild(newcell);
}
mergeableRows.Add(ColumnLetter(index + 1) + rowIndex);
return(projectRow);
}
public void ConvertToDataTable_NoHeadersNoGaps_DataTable()
{
void AppendCells(SheetData sheetData)
{
var row = new Row();
row.AppendChild(new Cell {
CellReference = "A1", DataType = CellValues.String, CellValue = new CellValue("A,1")
});
row.AppendChild(new Cell {
CellReference = "B1", DataType = CellValues.String, CellValue = new CellValue("B,1")
});
row.AppendChild(new Cell {
CellReference = "C1", DataType = CellValues.String, CellValue = new CellValue("C,1")
});
sheetData.AppendChild(row);
}
using (var stream = new MemoryStream())
using (var document = this.GetDocument(stream, AppendCells))
{
var result = document.ConvertToDataTable("Sheet1");
Assert.Equal("Sheet1", result.Name);
Assert.Equal(1, result.Rows.Count);
Assert.Equal(3, result.Columns.Count);
Assert.Equal("C,1", result.Rows[0][2].ToString());
}
}
private static Row CreateDatesRow(DateTime firstDayOfMonth, DateTime lastDayOfMonth, int rowIndex)
{
int index = -1;
Row headerRow = new Row();
headerRow.RowIndex = (UInt32)rowIndex;
DateTime dt = firstDayOfMonth;
Cell firstcell = CreateCell("", ref index, rowIndex, 2);
headerRow.AppendChild(firstcell);
while (dt.Date <= lastDayOfMonth.Date)
{
Cell newcell = CreateCell(dt.ToString("dd/MM/yyyy"), ref index, rowIndex, 2);
dt = dt.AddDays(1);
headerRow.AppendChild(newcell);
}
Cell totalcell = CreateCell("Total", ref index, rowIndex, 2);
headerRow.AppendChild(totalcell);
return(headerRow);
}
Row CreateContentRow(int index, string territory, decimal salesLastYear, decimal salesThisYear)
{
//Create the new row.
Row r = new Row();
r.RowIndex = (UInt32)index;
//First cell is a text cell, so create it and append it.
Cell firstCell = CreateTextCell(headerColumns[0], territory, index);
r.AppendChild(firstCell);
//Create the cells that contain the data.
for (int i = 1; i < headerColumns.Length; i++)
{
Cell c = new Cell();
c.CellReference = headerColumns[i] + index;
CellValue v = new CellValue();
if (i == 1)
{
v.Text = salesLastYear.ToString();
}
else
{
v.Text = salesThisYear.ToString();
}
c.AppendChild(v);
r.AppendChild(c);
}
return(r);
}
private bool ImportListData <T>(WorksheetPart worksheetPart, IEnumerable <Row> listRow, List <T> listSourceData, int intInputDataStartRowIndex, int intInputDataStartColumnIndex, bool blnIsDeleteTempRow = true) where T : class, new()
{
if (listSourceData == null || listSourceData.Count == 0)
{
return(true);
}
var rowObj = listRow.FirstOrDefault(item => item.RowIndex == intInputDataStartRowIndex);
if (rowObj == null)
{
return(false);
}
var arrProperties = typeof(T).GetProperties();
if (arrProperties == null || arrProperties.Length == 0)
{
return(false);
}
var cellObj = rowObj.GetFirstChild <Cell>();
var cellStyleIndex = cellObj.StyleIndex;
var sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
foreach (var item in listSourceData)
{
if (item == null)
{
continue;
}
var rowAdded = new Row();
for (int i = 1; i < intInputDataStartColumnIndex; i++)
{
rowAdded.AppendChild(new Cell());
}
foreach (var subItem in arrProperties)
{
string strValue = subItem.GetValue(item) as string;
strValue = string.IsNullOrWhiteSpace(strValue) ? "" : strValue;
var cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(strValue);
cell.StyleIndex = cellStyleIndex;
rowAdded.AppendChild(cell);
}
sheetData.AppendChild(rowAdded);
}
if (blnIsDeleteTempRow)
{
sheetData.RemoveChild(rowObj);
}
return(true);
}
private Row GenerateRow(CacheFileEntry cacheFileEntry)
{
Row tRow = new Row();
tRow.AppendChild(CreateCell(cacheFileEntry.Name, CellValues.String));
tRow.AppendChild(CreateCell(cacheFileEntry.Extension, CellValues.String));
tRow.AppendChild(CreateCell(cacheFileEntry.Size.ToString("0.000")));
return(tRow);
}
private Row CreateHeaderRowForExcelsheet2()
{
Row workRow = new Row();
workRow.AppendChild(CreateCell("Name", CellValues.String, 3));
workRow.AppendChild(CreateCell("Dateityp", CellValues.String, 3));
workRow.AppendChild(CreateCell("Grösse in B", CellValues.String, 3));
return(workRow);
}
public static void Run()
{
// ExStart:InsertTableDirectly
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir_WorkingWithTables();
Document doc = new Document();
// We start by creating the table object. Note how we must pass the document object
// To the constructor of each node. This is because every node we create must belong
// To some document.
Table table = new Table(doc);
// Add the table to the document.
doc.FirstSection.Body.AppendChild(table);
// Here we could call EnsureMinimum to create the rows and cells for us. This method is used
// To ensure that the specified node is valid, in this case a valid table should have at least one
// Row and one cell, therefore this method creates them for us.
// Instead we will handle creating the row and table ourselves. This would be the best way to do this
// If we were creating a table inside an algorthim for example.
Row row = new Row(doc);
row.RowFormat.AllowBreakAcrossPages = true;
table.AppendChild(row);
// We can now apply any auto fit settings.
table.AutoFit(AutoFitBehavior.FixedColumnWidths);
// Create a cell and add it to the row
Cell cell = new Cell(doc);
cell.CellFormat.Shading.BackgroundPatternColor = Color.LightBlue;
cell.CellFormat.Width = 80;
// Add a paragraph to the cell as well as a new run with some text.
cell.AppendChild(new Paragraph(doc));
cell.FirstParagraph.AppendChild(new Run(doc, "Row 1, Cell 1 Text"));
// Add the cell to the row.
row.AppendChild(cell);
// We would then repeat the process for the other cells and rows in the table.
// We can also speed things up by cloning existing cells and rows.
row.AppendChild(cell.Clone(false));
row.LastCell.AppendChild(new Paragraph(doc));
row.LastCell.FirstParagraph.AppendChild(new Run(doc, "Row 1, Cell 2 Text"));
dataDir = dataDir + "Table.InsertTableUsingNodes_out.doc";
// Save the document to disk.
doc.Save(dataDir);
// ExEnd:InsertTableDirectly
Console.WriteLine("\nTable using notes inserted successfully.\nFile saved at " + dataDir);
}
public static void CreatingTableRepeatingSectionMappedToCustomXmlPart(string dataDir)
{
// ExStart:CreatingTableRepeatingSectionMappedToCustomXmlPart
Document doc = new Document();
DocumentBuilder builder = new DocumentBuilder(doc);
CustomXmlPart xmlPart = doc.CustomXmlParts.Add("Books",
"<books><book><title>Everyday Italian</title><author>Giada De Laurentiis</author></book>" +
"<book><title>Harry Potter</title><author>J K. Rowling</author></book>" +
"<book><title>Learning XML</title><author>Erik T. Ray</author></book></books>");
Table table = builder.StartTable();
builder.InsertCell();
builder.Write("Title");
builder.InsertCell();
builder.Write("Author");
builder.EndRow();
builder.EndTable();
StructuredDocumentTag repeatingSectionSdt =
new StructuredDocumentTag(doc, SdtType.RepeatingSection, MarkupLevel.Row);
repeatingSectionSdt.XmlMapping.SetMapping(xmlPart, "/books[1]/book", "");
table.AppendChild(repeatingSectionSdt);
StructuredDocumentTag repeatingSectionItemSdt =
new StructuredDocumentTag(doc, SdtType.RepeatingSectionItem, MarkupLevel.Row);
repeatingSectionSdt.AppendChild(repeatingSectionItemSdt);
Row row = new Row(doc);
repeatingSectionItemSdt.AppendChild(row);
StructuredDocumentTag titleSdt =
new StructuredDocumentTag(doc, SdtType.PlainText, MarkupLevel.Cell);
titleSdt.XmlMapping.SetMapping(xmlPart, "/books[1]/book[1]/title[1]", "");
row.AppendChild(titleSdt);
StructuredDocumentTag authorSdt =
new StructuredDocumentTag(doc, SdtType.PlainText, MarkupLevel.Cell);
authorSdt.XmlMapping.SetMapping(xmlPart, "/books[1]/book[1]/author[1]", "");
row.AppendChild(authorSdt);
doc.Save(dataDir + "Document.docx");
// ExEnd:CreatingTableRepeatingSectionMappedToCustomXmlPart
Console.WriteLine("\nCreation of a Table Repeating Section Mapped To a Custom Xml Part is successfull.");
}
private void AddDatas(SheetData sheetData, SharedStringTable sharedStringTable, JArray tracks)
{
uint count = 1;
foreach (JObject track in tracks)
{
// Add a row to the cell table.
Row row = new Row()
{
RowIndex = count
};
sheetData.Append(row);
string countStr = count.ToString();
row.AppendChild(new Cell()
{
CellReference = "A" + countStr,
CellValue = new CellValue((string)track["name"]),
DataType = new EnumValue <CellValues>(CellValues.String)
});
string artistStr = string.Empty;
foreach (JObject artist in track["ar"])
{
artistStr += (string)artist["name"] + ';';
}
row.AppendChild(new Cell()
{
CellReference = "B" + countStr,
CellValue = new CellValue(InsertSharedStringItem(artistStr.Substring(0, artistStr.Length - 1), sharedStringTable).ToString()),
DataType = new EnumValue <CellValues>(CellValues.SharedString)
});
row.AppendChild(new Cell()
{
CellReference = "C" + countStr,
CellValue = new CellValue(InsertSharedStringItem((string)track["al"]["name"], sharedStringTable).ToString()),
DataType = new EnumValue <CellValues>(CellValues.SharedString)
});
uint time = (uint)track["dt"];
row.AppendChild(new Cell()
{
CellReference = "D" + countStr,
CellValue = new CellValue($"{time / 60000:00}:{time % 60000 / 1000:00}.{time % 1000:000}"),
DataType = new EnumValue <CellValues>(CellValues.String)
});
count++;
}
}
public static void Run()
{
// ExStart:InsertTableDirectly
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir_WorkingWithTables();
Document doc = new Document();
// We start by creating the table object. Note how we must pass the document object
// To the constructor of each node. This is because every node we create must belong
// To some document.
Table table = new Table(doc);
// Add the table to the document.
doc.FirstSection.Body.AppendChild(table);
// Here we could call EnsureMinimum to create the rows and cells for us. This method is used
// To ensure that the specified node is valid, in this case a valid table should have at least one
// Row and one cell, therefore this method creates them for us.
// Instead we will handle creating the row and table ourselves. This would be the best way to do this
// If we were creating a table inside an algorthim for example.
Row row = new Row(doc);
row.RowFormat.AllowBreakAcrossPages = true;
table.AppendChild(row);
// We can now apply any auto fit settings.
table.AutoFit(AutoFitBehavior.FixedColumnWidths);
// Create a cell and add it to the row
Cell cell = new Cell(doc);
cell.CellFormat.Shading.BackgroundPatternColor = Color.LightBlue;
cell.CellFormat.Width = 80;
// Add a paragraph to the cell as well as a new run with some text.
cell.AppendChild(new Paragraph(doc));
cell.FirstParagraph.AppendChild(new Run(doc, "Row 1, Cell 1 Text"));
// Add the cell to the row.
row.AppendChild(cell);
// We would then repeat the process for the other cells and rows in the table.
// We can also speed things up by cloning existing cells and rows.
row.AppendChild(cell.Clone(false));
row.LastCell.AppendChild(new Paragraph(doc));
row.LastCell.FirstParagraph.AppendChild(new Run(doc, "Row 1, Cell 2 Text"));
dataDir = dataDir + "Table.InsertTableUsingNodes_out.doc";
// Save the document to disk.
doc.Save(dataDir);
// ExEnd:InsertTableDirectly
Console.WriteLine("\nTable using notes inserted successfully.\nFile saved at " + dataDir);
}
public override void addDataRows(SheetData sheetData, GroupReport report)
{
// бежим по строкам
foreach (Instance instance in report.Instances)
{
var row = new Row();
Instance.Property property;
foreach (TemplateField field in report.Template.Fields)
{
if (field.IsVisible)
{
property = instance.OwnProperties.First(o => o.Attribute.ID.ToString() == field.Attribute.ID.ToString());
var cell = new Cell();
var cellValue = new CellValue();
ApplyStyle(property, field, ref cell, ref cellValue);
cell.Append(cellValue);
row.AppendChild(cell);
}
}
sheetData.AppendChild(row);
}
}
private static void AddRows(OpenXmlCompositeElement sheet, int maxCount, int columns)
{
var maxIndex = sheet.Elements <Row>()
.Select(r => r.RowIndex.Value).Max();
var count = sheet.Elements <Row>()
.Count();
for (var i = count; i <= maxCount; i++)
{
var row = new Row {
RowIndex = ++maxIndex
};
for (var j = 1; j < columns + 1; j++)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(string.Empty),
CellReference = GetExcelColumnName(j) + row.RowIndex
};
row.AppendChild(cell);
}
sheet.AppendChild(row);
}
}
/// <summary>
/// Creates a new table in the document with the given dimensions and text in each cell.
/// </summary>
private Table CreateTable(Document doc, int rowCount, int cellCount, string cellText)
{
Table table = new Table(doc);
// Create the specified number of rows.
for (int rowId = 1; rowId <= rowCount; rowId++)
{
Row row = new Row(doc);
table.AppendChild(row);
// Create the specified number of cells for each row.
for (int cellId = 1; cellId <= cellCount; cellId++)
{
Cell cell = new Cell(doc);
row.AppendChild(cell);
// Add a blank paragraph to the cell.
cell.AppendChild(new Paragraph(doc));
// Add the text.
cell.FirstParagraph.AppendChild(new Run(doc, cellText));
}
}
return(table);
}
public byte[] Generate()
{
var memoryStream = new MemoryStream();
using (var document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
{
var workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
var sheets = workbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet 1"
});
var sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
var row1 = new Row();
row1.AppendChild(
new Cell
{
CellValue = new CellValue("Abp Framework"),
DataType = CellValues.String
}
);
sheetData.AppendChild(row1);
var row2 = new Row();
row2.AppendChild(
new Cell
{
CellValue = new CellValue("Open Source"),
DataType = CellValues.String
}
);
sheetData.AppendChild(row2);
var row3 = new Row();
row3.AppendChild(
new Cell
{
CellValue = new CellValue("WEB APPLICATION FRAMEWORK"),
DataType = CellValues.String
}
);
sheetData.AppendChild(row3);
document.Save();
document.Close();
return(memoryStream.ToArray());
}
}
private List <string> GetColumnsForDisplaying(DataColumnCollection dataColumnCollection, bool blnIsDisplayingDBColumn, SheetData sheetData)
{
var listColumnName = new List <string>();
if (!blnIsDisplayingDBColumn)
{
foreach (DataColumn column in dataColumnCollection)
{
listColumnName.Add(column.ColumnName);
}
return(listColumnName);
}
var headerRow = new Row();
foreach (DataColumn column in dataColumnCollection)
{
string strColumnName = column.ColumnName;
listColumnName.Add(strColumnName);
var cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(strColumnName);
cell.StyleIndex = 5U;
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
return(listColumnName);
}
public void WriteHeader(WorksheetPart wssheatpart, DbDataReader reader)
{
SheetData firstChild = wssheatpart.Worksheet.GetFirstChild <SheetData>();
Row headerRow = FindRow(firstChild, 1);
int fields = reader.FieldCount;
headerRow.RemoveAllChildren();
for (int i = 0; i < fields; i++)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(reader.GetName(i));
headerRow.AppendChild(cell);
}
/*
* for (int i = 0; i < 5; i++)
* {
* Cell cell = new Cell();
* cell.DataType = CellValues.String;
* cell.CellValue = new CellValue("col" + i.ToString());
* headerRow.AppendChild(cell);
* }
*/
}
public Stream GenerateReport <T>(IList <T> records, ColumnDef <T>[] columnDefs)
{
var stream = new MemoryStream();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
WorkbookStylesPart sp = workbookpart.AddNewPart <WorkbookStylesPart>("rId3");
uint[] styleIndexies = AddCellStyles(columnDefs, sp);
CellFormat cellFormat = sp.Stylesheet.CellFormats.ChildElements[(int)styleIndexies[0]] as CellFormat;
#region captain
Row captainRow = new Row();
captainRow.RowIndex = (UInt32)1;
int captainSyleIndex = AddCaptainFormat(sp.Stylesheet, cellFormat.BorderId);
for (int i = 0; i < columnDefs.Length; i++)
{
Cell cellCaptain = new Cell();
cellCaptain.CellReference = $"{(char)((int)'A' + i)}{captainRow.RowIndex}";
cellCaptain.CellValue = new CellValue(columnDefs[i].Captain);
cellCaptain.DataType = new EnumValue <CellValues>(CellValues.String);
cellCaptain.StyleIndex = (uint)captainSyleIndex;
captainRow.AppendChild(cellCaptain);
}
sheetData.AppendChild(captainRow);
uint rowIndex = captainRow.RowIndex;
#endregion
foreach (var record in records)
{
var dataRow = new Row();
dataRow.RowIndex = (uint)++rowIndex;
sheetData.Append(dataRow);
for (int k = 0; k < columnDefs.Length; k++)
{
Cell cellData = new Cell();
cellData.CellReference = $"{(char)((int)'A' + k)}{dataRow.RowIndex}";
cellData.DataType = columnDefs[k].TargetDataType;
dataRow.Append(cellData);
cellData.CellValue = columnDefs[k].GetValueFunc(record);
cellData.StyleIndex = styleIndexies[k];
}
}
SetColumnWidth(columnDefs, worksheetPart, sheetData, styleIndexies);
AppendNewSheet(spreadsheetDocument, worksheetPart);
}
stream.Position = 0;
return(stream);
}
private void WriteColums(SheetData sheetData, int tableIndex)
{
Row row = new Row()
{
RowIndex = 1
};
sheetData.Append(row);
foreach (DataColumn column in ds.Tables[tableIndex].Columns)
{
RunProperties runProperties = new RunProperties();
runProperties.Append(new Bold());
runProperties.Append(new Color()
{
Rgb = "aaaaaa"
});
runProperties.Append(new FontSize()
{
Val = 20
});
Cell cell = CreateTextCell(ds.Tables[tableIndex].Columns.IndexOf(column) + 1, 1, column.ColumnName, runProperties);
row.AppendChild(cell);
}
}
private static void CreateTable <T>(IList <T> objects, ref int rowIndex, int numCols,
List <SpreadsheetField> fields, List <char> headers, SheetData sheetData, bool hidden = false, int outline = 0)
{
// for each object
foreach (var rowObj in objects)
{
// row group?
var list = rowObj as IList <object>;
if (list != null)
{
CreateTable(list, ref rowIndex, numCols, fields, headers, sheetData, true, outline + 1);
continue;
}
rowIndex++;
// create a row
var row = new Row
{
RowIndex = (uint)rowIndex,
Collapsed = new BooleanValue(false),
OutlineLevel = new ByteValue((byte)outline),
Hidden = new BooleanValue(hidden)
};
int col;
// populate columns using supplied objects
for (col = 0; col < numCols; col++)
{
var field = fields[col];
var columnObj = GetColumnObject(field.FieldName, rowObj);
if (columnObj == null || columnObj == DBNull.Value)
{
continue;
}
Cell cell;
if (field.GetType() == typeof(HyperlinkField))
{
var displayColumnObj = GetColumnObject(((HyperlinkField)field).DisplayFieldName, rowObj);
cell = CreateHyperlinkCell(rowIndex, headers, columnObj, displayColumnObj, col);
}
else if (field.GetType() == typeof(DecimalNumberField))
{
cell = CreateDecimalNumberCell(rowIndex, headers, columnObj,
((DecimalNumberField)field).DecimalPlaces, col);
}
else
{
cell = CreateCell(rowIndex, headers, columnObj, col);
}
row.AppendChild(cell);
} // for each column
sheetData.AppendChild(row);
}
}
/// <summary>
/// Insert date from datatable to worksheet.
/// </summary>
private static void InsertTableData(ReportResult reportResult, SheetData sheetData, List <DataRow> rows)
{
// Add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
int headerColInx = 0;
foreach (ReportColumn column in reportResult.Metadata.ReportColumns.Values)
{
if (!column.IsHidden && column.Type != "Image")
{
Cell headerCell = CreateTextCell(null);
SetCellLocation(headerCell, headerColInx, 1);
header.AppendChild(headerCell);
sharedStrings.TryAdd(sharedStringIndex, column.Title.Trim());
headerColInx++;
}
}
sheetData.AppendChild(header);
// Loop through each data row
int excelRowIndex = 2;
foreach (DataRow row in rows)
{
Row excelRow = CreateContentRow(reportResult, row, excelRowIndex++);
sheetData.AppendChild(excelRow);
}
}
public static void FillRow(Row row, List <string> values, List <UInt32> styles)
{
for (int i = 0; i < values.Count; i++)
{
string value = values[i];
Cell cell = row.Elements <Cell>().ElementAtOrDefault(i);
bool exist = cell != null;
if (!exist)
{
cell = new Cell();
}
cell.CellValue = new CellValue(value);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
cell.StyleIndex = i != values.Count - 1
? styles.FirstOrDefault()
: styles.LastOrDefault();
if (!exist)
{
row.AppendChild(cell);
}
}
}
public static void GenerateReport(List <DataObject> objects)
{
SheetData data = new SheetData();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataObject obj in objects)
{
Cell headerCell = createTextCell(objects.IndexOf(obj) + 1, 1, obj.Name);
header.AppendChild(headerCell);
}
data.AppendChild(header);
SpreadsheetDocument doc = CreateDoc(data);
/*using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(fileName, true))
* {
* WorkbookPart workbook = spreadsheet.WorkbookPart;
* //create a reference to Sheet1
* WorksheetPart worksheet = workbook.WorksheetParts.Last();
*
*
* ///loop through each data row DataRow contentRow;
* for (int i = 0; i < table.Rows.Count; i++)
* {
* contentRow = table.Rows[i];
* data.AppendChild(createContentRow(contentRow, i + 2));
* }
* } */
}
public static void ExcelTest(string excelOutput, DataTable dataTable)
{
using var spreadsheetDocument = SpreadsheetDocument.Create(excelOutput, SpreadsheetDocumentType.Workbook);
var workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
var sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
#region 此处换成datatable
for (int i = 0; i < 10; i++)
{
Row row = new Row();
for (int j = 0; j < 10; j++)
{
Cell dataCell = new Cell();
dataCell.CellValue = new CellValue($"{i + 1}行{j + 1}列");
dataCell.DataType = new EnumValue <CellValues>(CellValues.String);
row.AppendChild(dataCell);
}
sheetData.Append(row);
}
#endregion
workbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
//Creation d'une ligne en mode monoSheet
public Row creerLigne2(DataRow dr, int index, uint[] style)
{
Row r = new Row()
{
RowIndex = (uint)index
};
Cell c = new Cell();
int i = 0;
foreach (var att in dr.ItemArray)
{
c = new Cell();
try { c = XcelWin.createCellDouble(headerColumns[i], index, Convert.ToDouble(att), style[0]); }
catch (Exception) { string tmp; if (att == DBNull.Value)
{
tmp = "";
}
else
{
tmp = att.ToString();
} c = XcelWin.createTextCell(headerColumns[i], index, tmp, style[0]); }
r.AppendChild(c);
i++;
}
return(r);
}
public static void GenerateReport(List<DataObject> objects)
{
SheetData data = new SheetData();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataObject obj in objects)
{
Cell headerCell = createTextCell(objects.IndexOf(obj) + 1, 1, obj.Name);
header.AppendChild(headerCell);
}
data.AppendChild(header);
SpreadsheetDocument doc = CreateDoc(data);
/*using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(fileName, true))
{
WorkbookPart workbook = spreadsheet.WorkbookPart;
//create a reference to Sheet1
WorksheetPart worksheet = workbook.WorksheetParts.Last();
///loop through each data row DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
} */
}
/// <summary>
/// This method is used to create the Row to append in excel
/// </summary>
/// <param name="rIndex"></param>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <returns></returns>
private static Row GetRowToAppendInExcel(SpreadsheetDocument document, WorksheetPart worksheetPart, int rIndex, List <ExcelCellValues> columnValueList)
{
Row r = new Row();
try
{
r.RowIndex = (UInt32)rIndex;
foreach (var colValue in columnValueList)
{
if (!string.IsNullOrEmpty(colValue.CellUpdateValue))
{
Cell cell = new Cell();
cell.CellReference = colValue.CellName + Convert.ToString(rIndex);
cell.DataType = CellValues.String;
InlineString inlinefString = new InlineString();
Text txt = new Text();
txt.Text = colValue.CellUpdateValue;
inlinefString.AppendChild(txt);
cell.AppendChild(inlinefString);
r.AppendChild(cell);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(r);
}
private void CreateContent(System.Data.DataTable _DataSet, SheetData sheetData, UInt32 StartRow, _GetExcelInfo _ge)
{
Cell cell;
for (int r = 0; r < _DataSet.Rows.Count; r++)
{
Row row = new Row();
row.RowIndex = ++StartRow;
// row = new DocumentFormat.OpenXml.Spreadsheet.Row { RowIndex = ++rowIndex };
sheetData.AppendChild(row);
DataRow dr = _DataSet.Rows[r];
for (int c = 0; c < _DataSet.Columns.Count; c++)
{
string CurrentColumn = _ge.GetXCellViaNumber(c + 1);
cell = CreateTextCell(CurrentColumn, StartRow, _DataSet.Rows[r][c].ToString());
//cell.StyleIndex = 0;
row.AppendChild(cell);
}
}
}
public static MemoryStream CreateExcelFileStream(IEnumerable data)
{
var stream = new MemoryStream();
DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(data), (typeof(DataTable)));
using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
Row headerRow = new Row();
List <string> columns = new List <string>();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
Row newRow = new Row();
foreach (string col in columns)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookPart.Workbook.Save();
document.Close();
stream.Position = 0;
}
return(stream);
}
private static void AddRows <T>(List <T> data, List <string> columns, SheetData sheetData)
{
foreach (var dataMember in data)
{
var typeOfData = dataMember.GetType();
var row = new Row();
foreach (string column in columns)
{
var cell = new Cell {
DataType = CellValues.String
};
var property = typeOfData.GetProperty(column);
var columnValue = property.GetValue(dataMember, null);
cell.AppendChild(new CellValue {
Text = columnValue != null ? columnValue.ToString() : string.Empty
});
row.AppendChild(cell);
}
sheetData.AppendChild(row);
}
}
private void AppendDataToExtendedSheet(Worksheet worksheet, SheetData sheetData,
ExcelData data)
{
UInt32 rowIdex = 0;
Row row;
MergeCells mergeCells = new MergeCells();
// Add sheet data
foreach (var rowData in data.Cells)
{
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
foreach (var excelCell in rowData)
{
var cell = CreateCell(ColumnLetter(excelCell.ColumnIndex),
(uint)(excelCell.RowIndex + 1), excelCell.Content ?? string.Empty);
row.AppendChild(cell);
if (excelCell.ColSpan > 1 || excelCell.RowSpan > 1)
{
var mergeCell = CreateMergeCell(excelCell);
mergeCells.Append(mergeCell);
}
}
}
worksheet.InsertAfter(mergeCells, worksheet.Elements <SheetData>().First());
}
private static Row CreateNewRow(int rowIndex, params string[] data)
{
// New Row
Row row = new Row { RowIndex = (UInt32)rowIndex };
for (int i = 0; i < data.Length; i++)
{
// A = 65 for the first column, B = 66, C = 67...
string column = ((char) (65 + i)).ToString();
// New Cell
Cell cell = new Cell
{
DataType = CellValues.InlineString,
CellReference = column + rowIndex
};
// Create Text object
Text t = new Text {Text = data[i]};
// Append Text to InlineString object
InlineString inlineString = new InlineString();
inlineString.AppendChild(t);
// Append InlineString to Cell
cell.AppendChild(inlineString);
// Append Cell to Row
row.AppendChild(cell);
}
return row;
}
private static Row createContentRow(
int rowIndex, string text)
{
Row row = new Row { RowIndex = (UInt32)rowIndex };
Cell dataCell = createTextCell(1, rowIndex, text);
row.AppendChild(dataCell);
return row;
}
//создает строку заголовка
protected virtual void addHeaderRow(Row headerRow, DataTable dt)
{
foreach (DataColumn HeaderColumn in dt.Columns)
{
Cell cell = new Cell() { StyleIndex = (UInt32Value)0U };
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(HeaderColumn.ColumnName);
headerRow.AppendChild(cell);
}
}
public MemoryStream GenerateExcel(SLExcelData data)
{
var stream = new MemoryStream();
var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
var sheet = new Sheet() {
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1, Name = data.SheetName ?? "Sheet 1"
};
sheets.AppendChild(sheet);
// Add header
UInt32 rowIdex = 0;
var row = new Row { RowIndex = ++rowIdex };
sheetData.AppendChild(row);
var cellIdex = 0;
foreach (var header in data.Headers) {
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, header ?? string.Empty));
}
if (data.Headers.Count > 0) {
// Add the column configuration if available
if (data.ColumnConfigurations != null) {
var columns = (Columns)data.ColumnConfigurations.Clone();
worksheetPart.Worksheet
.InsertAfter(columns, worksheetPart.Worksheet.SheetFormatProperties);
}
}
// Add sheet data
foreach (var rowData in data.DataRows) {
cellIdex = 0;
row = new Row { RowIndex = ++rowIdex };
sheetData.AppendChild(row);
foreach (var callData in rowData) {
var cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, callData ?? string.Empty);
row.AppendChild(cell);
}
}
workbookpart.Workbook.Save();
document.Close();
return stream;
}
private Row CreateContentRow(DataGridViewRow dataRow, int startRowIndex)
{
var row = new Row { RowIndex = (UInt32)startRowIndex };
for (var i = 0; i < _headerColumns.Length; i++)
{
var cell = _factory.Create(dataRow.Cells[i].Value);
cell.CellReference = _headerColumns[i] + startRowIndex;
row.AppendChild(cell);
}
return row;
}
protected virtual Row CreateHeaderRow(IEnumerable<string> headers)
{
var row = new Row();
foreach (var columnName in headers)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(columnName)
};
row.AppendChild(cell);
}
return row;
}
public void ImportDataTable(SpreadsheetDocument package, DataTable table, string sheetName)
{
//populate the data into the spreadsheet
WorkbookPart workbook = package.WorkbookPart;
WorksheetPart worksheetPart = workbook.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
SheetData data = worksheetPart.Worksheet.GetFirstChild<SheetData>();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataColumn column in table.Columns) {
Cell headerCell = CreateTextCell(
table.Columns.IndexOf(column) + 1,
1,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++) {
contentRow = table.Rows[i];
data.AppendChild(CreateContentRow(contentRow, i + 2));
}
// Create Sheets object.
Sheets sheets = workbook.Workbook.GetFirstChild<Sheets>();
string relationshipId = workbook.GetIdOfPart(worksheetPart);
// Create a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0) {
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
}
public void AddRow(Worksheet worksheet, bool isHeader, params object[] values)
{
var row = new Row();
var sheetData = worksheet.GetFirstChild<SheetData>();
var styleIndex = StyleConst.CellFormat.CellFormatStyle.CELL_REGULAR;
if (isHeader)
styleIndex = StyleConst.CellFormat.CellFormatStyle.CELL_HEADER;
foreach (var value in values)
{
var cell = GetCell(value, styleIndex);
row.AppendChild(cell);
}
sheetData.Append(row);
worksheet.Save();
}
private Row BuildRowFromExpandoObject(SheetModel model, ISheetDefinition sheetDefinition)
{
var row = new Row();
foreach (var columnDefinition in sheetDefinition.ColumnDefinitions)
{
var propValue = GetPropertyValue(model, columnDefinition.PropertyName);
CellValues cellType;
string cellValue;
_converterManager.GetCellTypeAndValue(propValue, out cellType, out cellValue);
var cell = new Cell
{
DataType = cellType,
CellValue = new CellValue(cellValue)
};
cell.AddMdsolAttribute("type",
propValue == null ? typeof(object).FullName : propValue.GetType().FullName);
cell.AddMdsolAttribute("propertyName", columnDefinition.PropertyName);
row.AppendChild(cell);
}
return row;
}
public void ExportDataTable(DataTable table, string exportFile)
{
//create the empty spreadsheet template and save the file //using the class generated by the Productivity tool
ExcelDocument excelDocument = new ExcelDocument();
excelDocument.CreatePackage(exportFile);
//string filename = "";
//File.Copy(filename, filename, true);
//populate the data into the spreadsheet
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(exportFile, true))
{
WorkbookPart workbook = spreadsheet.WorkbookPart;
//create a reference to Sheet1
WorksheetPart worksheet = workbook.WorksheetParts.Last();
SheetData data = worksheet.Worksheet.GetFirstChild<SheetData>();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataColumn column in table.Columns)
{
Cell headerCell = createTextCell(
table.Columns.IndexOf(column) + 1,
1,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
}
}
public static void GenerateReport(List<DataObject> objects)
{
const string fileName = @"C:\Users\Masha\report.xlsx";
using (SpreadsheetDocument doc = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbook = doc.WorkbookPart;
WorksheetPart worksheet = workbook.WorksheetParts.Last();
SheetData data = worksheet.Worksheet.GetFirstChild<SheetData>();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
Cell headerCell = createTextCell(1, 1, "header");
header.AppendChild(headerCell);
data.AppendChild(header);
for (int i = 0; i < objects.Count; i++)
{
data.AppendChild(createContentRow(i + 2, objects[i].Name));
}
}
}
private void ExportDataSet(DataTable dt, SpreadsheetDocument workbook, uint startrowindex)
{
WorkbookPart workbookPart = workbook.WorkbookPart;
Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().First(s => s.Name == "Лист1");
var sheetPart = (WorksheetPart) (workbookPart.GetPartById(theSheet.Id));
Worksheet worksheet = sheetPart.Worksheet;
var sheetData = worksheet.GetFirstChild<SheetData>();
var headerRow = new Row();
WorkbookStylesPart style = workbookPart.WorkbookStylesPart;
if (style != null)
{
workbookPart.DeletePart(style);
}
var stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
StyleSheetCreatorForExport.GenerateStylesheet().Save(stylesPart);
CellFormats cellFormats = workbookPart.WorkbookStylesPart.Stylesheet.Elements<CellFormats>().First();
/////////
var headerformat = new CellFormat
{
NumberFormatId = 0U,
FontId = 6U,
FillId = 0U,
BorderId = 1U,
FormatId = 0U,
ApplyNumberFormat = true
};
cellFormats.Append(headerformat);
uint styleIndex = cellFormats.Count++;
var bodyformat = new CellFormat
{
NumberFormatId = 0U,
FontId = 5U,
FillId = 0U,
BorderId = 1U,
FormatId = 0U,
ApplyNumberFormat = true
};
cellFormats.Append(bodyformat);
/////////
headerRow.RowIndex = startrowindex;
//headerRow.StyleIndex = styleIndex;
var columns = new List<string>();
for (int headerindex = 1; headerindex < dt.Columns.Count; headerindex++)
{
columns.Add(dt.Columns[headerindex].ColumnName);
var cell = new Cell();
cell.StyleIndex = styleIndex;
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dt.Columns[headerindex].ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
uint i = startrowindex + 1;
foreach (DataRow dtrow in dt.Rows)
{
var newRow = new Row();
newRow.RowIndex = i;
i++;
foreach (String col in columns)
{
var cell = new Cell();
cell.StyleIndex = styleIndex + 1;
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dtrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
public static Row createContentRow(
DataRow dataRow,
int rowIndex)
{
Row row = new Row
{
RowIndex = (UInt32)rowIndex
};
for (int i = 0; i < dataRow.Table.Columns.Count; i++)
{
Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
row.AppendChild(dataCell);
}
return row;
}
public static void InsertValuesInSheets(string sheetName, SelectQueryBuilder queryBuilder, WorkbookPart workbookPart, DataTable table)
{
WorksheetPart worksheetPart = null;
if (!string.IsNullOrEmpty(sheetName))
{
Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>();
worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
}
else
{
worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();
}
if (worksheetPart != null)
{
SheetData data = worksheetPart.Worksheet.GetFirstChild<SheetData>();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataColumn column in table.Columns)
{
Cell headerCell = createTextCell(
table.Columns.IndexOf(column) + 1,
1,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
worksheetPart.Worksheet.Save();
}
}
public void Write(DataTable dt)
{
var sheets = _wbPart.Workbook.Descendants<Sheet>();
int sheetCount = sheets.Count();
var sheet = sheets.Where(c => c.Name.Value.ToLower() == dt.TableName.ToLower()).FirstOrDefault();
WorksheetPart wsPart = null;
if (sheet == null)
{
wsPart = _wbPart.AddNewPart<WorksheetPart>();
wsPart.Worksheet = new Worksheet(new SheetData());
sheet = new Sheet()
{
Id = _wbPart.GetIdOfPart(wsPart),
Name = dt.TableName,
SheetId = (uint)(sheetCount + 1)
};
_wbPart.Workbook.GetFirstChild<Sheets>().Append(sheet);
}
else
{
wsPart = _wbPart.GetPartById(sheet.Id) as WorksheetPart;
wsPart.Worksheet.GetFirstChild<SheetData>().RemoveAllChildren();
}
SheetData sheetData = wsPart.Worksheet.GetFirstChild<SheetData>();
Row headRow = new Row();
headRow.RowIndex = 1;
for (int i = 0; i < dt.Columns.Count; i++)
{
Cell c = new Cell();
c.DataType = new EnumValue<CellValues>(CellValues.String);
c.CellReference = getColumnName(i + 1) + "1";
c.CellValue = new CellValue(dt.Columns[i].ColumnName);
headRow.AppendChild(c);
}
sheetData.AppendChild(headRow);
for (int i = 0; i < dt.Rows.Count; i++)
{
Row r = new Row();
r.RowIndex = (UInt32)i + 2;
for (int j = 0; j < dt.Columns.Count; j++)
{
Cell c = new Cell();
c.DataType = new EnumValue<CellValues>(getCellType(dt.Columns[j].DataType));
c.CellReference = getColumnName(j + 1) + r.RowIndex.ToString();
DataRow dr = dt.Rows[i];
if (dr.IsNull(j))
{
c.CellValue = new CellValue("");
}
else if (c.DataType.Value == CellValues.Boolean)
{
string value = bool.Parse(dt.Rows[i][j].ToString()) ? "1" : "0";
c.CellValue = new CellValue(value);
}
else
{
c.CellValue = new CellValue(dt.Rows[i][j].ToString());
}
r.Append(c);
}
sheetData.Append(r);
}
_wbPart.Workbook.Save();
}
/// <summary>
/// Creates a new table in the document with the given dimensions and text in each cell.
/// </summary>
private Table CreateTable(Document doc, int rowCount, int cellCount, string cellText)
{
Table table = new Table(doc);
// Create the specified number of rows.
for (int rowId = 1; rowId <= rowCount; rowId++)
{
Row row = new Row(doc);
table.AppendChild(row);
// Create the specified number of cells for each row.
for (int cellId = 1; cellId <= cellCount; cellId++)
{
Cell cell = new Cell(doc);
row.AppendChild(cell);
// Add a blank paragraph to the cell.
cell.AppendChild(new Paragraph(doc));
// Add the text.
cell.FirstParagraph.AppendChild(new Run(doc, cellText));
}
}
return table;
}
//OverRide
public void saveXLS(String testName, DataSet ds)
{
SpreadsheetDocument xl = null;
string fullPath = Properties.Settings.Default.ExportSavePath + @"\" + testName;
fullPath += ".xlsx";
try
{
xl = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook);
}
catch (IOException)
{
MessageBox.Show("Another Copy of the Document is open, Please close the document and retry export");
throw;
}
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = GenerateStyleSheet();
wbsp.Stylesheet.Save();
//save the longest width for each column.
string[] longestWordPerColumn = new string[35];
int k = 0;
//create and add header row.
Row headerRow = new Row();
//add headers for germline mutation export
string[] germlineHeaders = { "Chromosome", "Position", "Gene Name", "Ref", "Var", "Strand", "Ref Codon", "Var Codon", "Ref AA", "Var AA", "AA Name", "CDS Name", "Cosmic Details", "Shows", "History", "RefSNP", "Clinical Significance", "MAF", "Chromosome Sample Count", "Alleles", "Allepe pop %" };
foreach (string s in germlineHeaders)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(s);
cell.StyleIndex = 1;
headerRow.AppendChild(cell);
longestWordPerColumn[k] = s;
k++;
}
sd.AppendChild(headerRow);
List<Mutation> mutationList = new List<Mutation>();//remove this
//create and add rows for each mutation.
//Here we go through ds
int tbllength = ds.Tables[0].Rows.Count;
String[] infoString = new String[21];
for (int rowNum = 0; rowNum < tbllength; rowNum++)//for each mutation m
{
Row newRow = new Row();
for (int j = 0; j < 21; j++)
{
infoString[j] = (string)ds.Tables[0].Rows[rowNum].ItemArray[j];
}
//string[] infoString = m.getInfoForExport();
for (int i = 0; i < infoString.Length; i++)
{
Cell cell1 = new Cell();
if (i == 1)
cell1.DataType = CellValues.Number;
else
cell1.DataType = CellValues.String;
cell1.CellValue = new CellValue(infoString[i]);
if (!infoString[12].Equals("-----"))//index 12 is the Cosmic Name postion so
cell1.StyleIndex = 2;
else
cell1.StyleIndex = 3;
newRow.AppendChild(cell1);
//if (longestWordPerColumn[i].Length < infoString[i].Length)
// longestWordPerColumn[i] = infoString[i];
}
sd.AppendChild(newRow);
}
//Sets the column width to longest width for each column.
Columns columns = new Columns();
for (int i = 0; i < 21; i++)
{
columns.Append(CreateColumnData((UInt32)i + 1, (UInt32)i + 1, GetWidth("Calibri", 11, longestWordPerColumn[i])));
}
ws.Append(columns);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "Sheet1";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}
/// <summary>
/// Creates a content row for the given dataRow
/// </summary>
/// <param name="cellType">The type of the Cell</param>
/// <param name="dataRow">DataRow that contains the data for the row it's creating</param>
/// <param name="rowIndex">Index for the row in the table</param>
/// <returns>Returns dataRow with proper data filled in</returns>
private Row createContentRow(Constants.CellDataType cellType,
DataRow dataRow,
int rowIndex)
{
var row = new Row {
RowIndex = (UInt32) rowIndex
};
for (int i = 0; i < dataRow.Table.Columns.Count; i++) {
Cell dataCell;
if (i > 0) {
switch (cellType) {
case Constants.CellDataType.Number:
dataCell = createNumCell(i + 1, rowIndex, dataRow[i]);
break;
default:
dataCell = createTextCell(i + 1, rowIndex,
dataRow[i]);
break;
}
} else {
dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
}
row.AppendChild(dataCell);
}
return row;
}
// Given a worksheet, a column name, and a row index,
// gets the cell at the specified column and
public static Cell GetCell(Worksheet worksheet, string columnName, Row row)
{
// Setup the row
if (row == null)
{
throw new Exception("Row does not exist");
}
string cellReference = columnName + row.RowIndex;
uint cellIndex = excelColMap.First(f => f.Value == columnName).Key;
int cnt = row.Elements<Cell>().Count();
Cell existingCell = null;
if (cnt >= cellIndex) {
existingCell = row.Elements<Cell>().Where(c => string.Compare
(c.CellReference.Value, cellReference, true) == 0).FirstOrDefault();
}
if (existingCell == null)
{
existingCell = row.AppendChild(new Cell());
existingCell.CellReference = cellReference;
}
return existingCell;
}
private Row createContentRow(int index, string firstColVal, IEnumerable<double>values)
{
Row r = new Row();
r.RowIndex = (UInt32)index;
char col = 'B';
Cell cell = createTextCell("A", firstColVal, index);
r.AppendChild(cell);
//Create cells that contain data
foreach (double val in values) {
Cell c = new Cell();
c.CellReference = col.ToString() + index;
CellValue v = new CellValue();
v.Text = val.ToString();
c.AppendChild(v);
r.AppendChild(c);
}
return r;
}
/// <summary>
/// Given a list of datatables, and the reportyType, it generates a excel file
/// using correct template and the data given
/// </summary>
/// <param name="reportType">Either Report or AuditLog from constants</param>
/// <param name="tableDictionary">
/// Dictionary of the tables to be exported. For either type,
/// the number of tables in the list must not exceed 15.
/// </param>
/// <param name="templatePath">Template file path</param>
/// <param name="workingCopyPath">Working copy path</param>
public void exportDataTable(Constants.ReportType reportType,
Dictionary<string, DataTable>
tableDictionary,
string templatePath, string workingCopyPath)
{
//Instead of creating a new excel file, let's use the template and make a copy to work with.
System.IO.File.Copy(templatePath, workingCopyPath, true);
if (tableDictionary.Count > 0) {
//populate the data into the spreadsheet
using (SpreadsheetDocument spreadsheet =
SpreadsheetDocument.Open(workingCopyPath, true)) {
WorkbookPart workbook = spreadsheet.WorkbookPart;
//Workbook workbook = spreadsheet.WorkbookPart.Workbook;
IEnumerable<WorksheetPart> sheets =
//workbook.Descendants<WorksheetPart>();
workbook.GetPartsOfType<WorksheetPart>();
DataTable table;
SheetData data;
string sheetName;
WorksheetPart worksheetPart;
//if there are more than 15 worksheets that needs to be created,
//the following method copies the first template and adds extra worksheets
//to the given workbook.
for (int x = 16; x <= tableDictionary.Count; x++) {
copySheet(spreadsheet.DocumentType, workbook, "Sheet1",
"Sheet" + x.ToString());
}
int i = 1;
foreach (var keyValue in tableDictionary) {
table = keyValue.Value;
sheetName = "Sheet" + (i).ToString();
worksheetPart = getWorksheetPart(workbook, sheetName);
//Merge Cells for title
string endColumnIndex =
getColumnName(table.Columns.Count);
MergeTwoCells(worksheetPart.Worksheet, "A1",
endColumnIndex + "1");
data =
worksheetPart.Worksheet.GetFirstChild<SheetData>
();
//creates title
var titleRow = new Row();
titleRow.RowIndex = (UInt32) 1;
Cell titleCell = createTextCell(1, 1, keyValue.Key);
titleRow.AppendChild(titleCell);
data.AppendChild(titleRow);
//add column names to the first row
var header = new Row();
header.RowIndex = (UInt32) Constants.REPORT_HEADER_ROW;
foreach (DataColumn column in table.Columns) {
Cell headerCell = createTextCell(
table.Columns.IndexOf(column) + 1,
Constants.REPORT_HEADER_ROW,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int j = 0; j < table.Rows.Count; j++) {
contentRow = table.Rows[j];
switch (reportType) {
case Constants.ReportType.Report:
data.AppendChild(
createContentRow(
Constants.CellDataType.Number,
contentRow,
j + Constants.DATA_START_ROW));
break;
default:
data.AppendChild(
createContentRow(
Constants.CellDataType.Text,
contentRow,
j + Constants.DATA_START_ROW));
break;
}
}
if (reportType == Constants.ReportType.Report) {
DrawingsPart drawingsPart =
worksheetPart.GetPartsOfType<DrawingsPart>()
.FirstOrDefault();
if (drawingsPart != null) {
ChartPart chartPart =
drawingsPart.GetPartsOfType<ChartPart>()
.FirstOrDefault();
fixChartData(chartPart, table.Rows.Count,
table.Columns.Count);
}
}
//incerement i to get the nextsheet
i++;
}
//if there were less than 15 worksheets to create, the rest of the templates are deleted.
for (int j = i; j < 16; j++) {
sheetName = "Sheet" + (j).ToString();
deleteAWorkSheet(workbook, sheetName);
}
}
}
string fileName = (reportType == Constants.ReportType.Report)
? "Reports.xlsx"
: "AuditLog.xlsx";
HttpResponse response = System.Web.HttpContext.Current.Response;
response.ClearContent();
response.Clear();
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition",
"attachment; filename=" + fileName + ";");
response.TransmitFile(workingCopyPath);
response.Flush();
response.End();
if (System.IO.File.Exists(workingCopyPath)) {
System.IO.File.Delete(workingCopyPath);
}
}
private Row createHeaderRow(string[] headers)
{
Cell c;
Row r = new Row() { RowIndex = 1 };
char col = 'A';
foreach (var h in headers) {
c = createTextCell((col++).ToString(), h, 1);
r.AppendChild(c);
}
return r;
}
public void InsertTableUsingNodeConstructors()
{
//ExStart
//ExFor:Table
//ExFor:Row
//ExFor:Row.RowFormat
//ExFor:RowFormat
//ExFor:Cell
//ExFor:Cell.CellFormat
//ExFor:CellFormat
//ExFor:CellFormat.Shading
//ExFor:Cell.FirstParagraph
//ExId:InsertTableUsingNodeConstructors
//ExSummary:Shows how to insert a table using the constructors of nodes.
Document doc = new Document();
// We start by creating the table object. Note how we must pass the document object
// to the constructor of each node. This is because every node we create must belong
// to some document.
Table table = new Table(doc);
// Add the table to the document.
doc.FirstSection.Body.AppendChild(table);
// Here we could call EnsureMinimum to create the rows and cells for us. This method is used
// to ensure that the specified node is valid, in this case a valid table should have at least one
// row and one cell, therefore this method creates them for us.
// Instead we will handle creating the row and table ourselves. This would be the best way to do this
// if we were creating a table inside an algorthim for example.
Row row = new Row(doc);
row.RowFormat.AllowBreakAcrossPages = true;
table.AppendChild(row);
// We can now apply any auto fit settings.
table.AutoFit(AutoFitBehavior.FixedColumnWidths);
// Create a cell and add it to the row
Cell cell = new Cell(doc);
cell.CellFormat.Shading.BackgroundPatternColor = Color.LightBlue;
cell.CellFormat.Width = 80;
// Add a paragraph to the cell as well as a new run with some text.
cell.AppendChild(new Paragraph(doc));
cell.FirstParagraph.AppendChild(new Run(doc, "Row 1, Cell 1 Text"));
// Add the cell to the row.
row.AppendChild(cell);
// We would then repeat the process for the other cells and rows in the table.
// We can also speed things up by cloning existing cells and rows.
row.AppendChild(cell.Clone(false));
row.LastCell.AppendChild(new Paragraph(doc));
row.LastCell.FirstParagraph.AppendChild(new Run(doc, "Row 1, Cell 2 Text"));
doc.Save(MyDir + @"\Artifacts\Table.InsertTableUsingNodes.doc");
//ExEnd
Assert.AreEqual(1, doc.GetChildNodes(NodeType.Table, true).Count);
Assert.AreEqual(1, doc.GetChildNodes(NodeType.Row, true).Count);
Assert.AreEqual(2, doc.GetChildNodes(NodeType.Cell, true).Count);
Assert.AreEqual("Row 1, Cell 1 Text\r\nRow 1, Cell 2 Text", doc.FirstSection.Body.Tables[0].ToString(SaveFormat.Text).Trim());
}
public static bool SetDataToExcel(string pathToExcelFile, string sheetName, DataTable data, string[] intro, string title)
{
try
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook wb;
if (!System.IO.File.Exists(pathToExcelFile))
{
//15/03/2011 Template ?
System.IO.File.Copy(SystemHelper.GetConfigValue("appStartupPath") + "\\template\\newWorkbook.xlsx", pathToExcelFile);
}
wb = app.Workbooks.Open(pathToExcelFile, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//Find sheetname
int index = 1;
for (index = 1; index <= wb.Sheets.Count; index++)
{
if (((Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[index]).Name.ToLower().Trim() == sheetName.ToLower().Trim())
break;
}
if (index > wb.Sheets.Count)
{
wb.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
((Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[index]).Activate();
}
Microsoft.Office.Interop.Excel.Worksheet activeSheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
activeSheet.Name = sheetName;
//activeSheet.Cells.FormatConditions =
if (!System.IO.File.Exists(pathToExcelFile))
{
//wb.SaveAs(pathToExcelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.SaveAs(pathToExcelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
wb.Save();
}
// exit excel, still now work so far
wb.Close(true, Type.Missing, Type.Missing);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app.Workbooks);
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(app) != 0)
{ };
//System.Diagnostics.Process.GetProcessById(app.Windows.Application.Hwnd).Close();
//System.Diagnostics.Process.GetProcessById(app.Windows.Application.Hwnd).Kill();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(pathToExcelFile, true))
{
IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0) // the sheet with that name couldn't be found
{
return false;
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
//sheetData.RemoveAllChildren<Row>();
//SharedStringTablePart shareStringTablePart = document.WorkbookPart.SharedStringTablePart;
int lastRowIndex = sheetData.ChildElements.Count;
int k = 0; //step in intro
for (int i = lastRowIndex; i < data.Rows.Count + 4 + lastRowIndex + intro.Length; i++) // +1 for the header label, + 2 for spacing row, + 1 for title
{
DataRow dataRow = null;
if (i > lastRowIndex + 3 + intro.Length)
{
dataRow = data.Rows[i - 4 - lastRowIndex - intro.Length];
}
Row aRow = new Row();
aRow.RowIndex = (UInt32)i + 1;
bool isRowEnd = false;//Fix the excel expand
for (int j = 0; j < data.Columns.Count; j++)
{
if (isRowEnd)
{
break;//Fix the excel expand
}
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = GetAlpha(j + 1) + (i + 1);
InlineString inlineString = new InlineString();
Text t = new Text();
if (i <= lastRowIndex + intro.Length - 1)//Intro
{
if (j == 0)
{
t.Text = intro[k];
k++;
cell.StyleIndex = (UInt32Value)3U;
isRowEnd = true;
}
}
else if (i == lastRowIndex + intro.Length + 1)//Title
{
if (j == 0)
{
t.Text = title;
cell.StyleIndex = (UInt32Value)4U;
isRowEnd = true;
}
}
else if (i == lastRowIndex + 3 + intro.Length)//Header
{
t.Text = data.Columns[j].ToString();
cell.StyleIndex = (UInt32Value)1U;
}
else
{
if (i != intro.Length + lastRowIndex && i != intro.Length + lastRowIndex + 2)//Null spacing row
t.Text = dataRow[j].ToString();//Data row
}
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
aRow.AppendChild(cell);
}
sheetData.AppendChild(aRow);
}
System.Xml.XmlWriter test = System.Xml.XmlWriter.Create("Test.xml");
worksheetPart.Worksheet.WriteTo(test);
test.Close();
worksheetPart.Worksheet.Save();
document.WorkbookPart.Workbook.Save();
document.Close();
}
}
catch (Exception e)
{
SystemHelper.LogEntry(string.Format("Error occurs on method {0} - Message {1}", "GetDataFromExcel", e.Message));
return false;
}
return true;
}
/// <summary>
/// Convert a Datatuple to a Row
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="dataTupleId">Id of the Datatuple to convert</param>
/// <param name="rowIndex">Position of the Row</param>
/// <returns></returns>
protected Row DatatupleToRow(long dataTupleId, int rowIndex)
{
Row row = new Row();
row.RowIndex = Convert.ToUInt32(rowIndex);
DataTuple dataTuple = DatasetManager.DataTupleRepo.Query(d=>d.Id.Equals(dataTupleId)).FirstOrDefault();
dataTuple.Materialize();
int columnIndex = 0;
columnIndex += offset;
// need to add this empty cell to add cells to the right place
row.AppendChild(GetEmptyCell(rowIndex, 0));
foreach (VariableIdentifier variableIdentifier in VariableIdentifiers)
{
VariableValue variableValue = dataTuple.VariableValues.Where(p => p.VariableId.Equals(variableIdentifier.id)).First();
Cell cell = VariableValueToCell(variableValue, rowIndex, columnIndex);
row.AppendChild(cell);
}
return row;
}
//Main class function, export the mutationList to XLSX file, sets file name to testName.
public static void saveXLS(String testName, List<Mutation> mutationList)
{
SpreadsheetDocument xl = null;
string fullPath = Properties.Settings.Default.ExportSavePath + @"\" + testName;
fullPath += ".xlsx";
try
{
xl = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook);
}
catch (IOException )
{
throw ;
}
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = GenerateStyleSheet();
wbsp.Stylesheet.Save();
//save the longest width for each column.
string[] longestWordPerColumn = new string[12];
int k = 0;
//create and add header row.
Row headerRow = new Row();
foreach (string s in Mutation.getHeaderForExport())
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(s);
cell.StyleIndex = 1;
headerRow.AppendChild(cell);
longestWordPerColumn[k] = s;
k++;
}
sd.AppendChild(headerRow);
//create and add rows for each mutation.
foreach (Mutation m in mutationList)
{
Row newRow = new Row();
string[] infoString = m.getInfoForExport();
for (int i = 0; i < infoString.Length; i++)
{
Cell cell1 = new Cell();
if (i == 1)
cell1.DataType = CellValues.Number;
else
cell1.DataType = CellValues.String;
cell1.CellValue = new CellValue(infoString[i]);
if (!m.CosmicName.Equals("-----"))
cell1.StyleIndex = 2;
else
cell1.StyleIndex = 3;
newRow.AppendChild(cell1);
if (longestWordPerColumn[i].Length < infoString[i].Length)
longestWordPerColumn[i] = infoString[i];
}
sd.AppendChild(newRow);
}
//Sets the column width to longest width for each column.
Columns columns = new Columns();
for (int i = 0; i < 12; i++)
{
columns.Append(CreateColumnData((UInt32)i + 1, (UInt32)i + 1, GetWidth("Calibri", 11, longestWordPerColumn[i])));
}
ws.Append(columns);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "Sheet1";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}
/// <summary>
/// Convert a Datatuple to a Row
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="dataTuple">Datatuple to convert</param>
/// <param name="rowIndex">Position of the Row</param>
/// <returns></returns>
protected Row DatatupleToRow(AbstractTuple dataTuple, int rowIndex)
{
Row row = new Row();
row.RowIndex = Convert.ToUInt32(rowIndex);
int columnIndex = 0;
columnIndex += offset;
// need to add this empty cell to add cells to the right place
row.AppendChild(GetEmptyCell(rowIndex, 0));
foreach (VariableIdentifier variableIdentifier in VariableIdentifiers)
{
VariableValue variableValue = dataTuple.VariableValues.Where(p => p.VariableId.Equals(variableIdentifier.id)).First();
Cell cell = VariableValueToCell(variableValue, rowIndex, columnIndex);
row.AppendChild(cell);
}
return row;
}
private Row CreateContentRow(DataRow dataRow, int rowIndex)
{
Row row = new Row {
RowIndex = (UInt32)rowIndex
};
for (int i = 0; i < dataRow.Table.Columns.Count; i++) {
Cell dataCell = null;
object value = dataRow[i];
Type columnType = dataRow.Table.Columns[i].DataType;
if (columnType == typeof(int) ||
columnType == typeof(decimal) ||
columnType == typeof(float) ||
columnType == typeof(long) ||
columnType == typeof(double)) {
dataCell = CreateNumberCell(i + 1, rowIndex, value, columnType);
}
else {
dataCell = CreateTextCell(i + 1, rowIndex, value);
}
row.AppendChild(dataCell);
}
return row;
}
