public static void CreateSpreadsheetWorkbook(string filepath, List <Dictionary <string, string> > Data)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
var spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
var workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add Sheets to the Workbook.
var sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
// Get the sheetData cell table.
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet();
var rowindex = 2;
foreach (var _row in Data)
{
var row = new Row
{
RowIndex = (uint)rowindex
};
foreach (var pair in _row)
{
var newCell = new Cell {
CellReference = pair.Key, DataType = CellValues.InlineString
};
var inlineString = new InlineString();
var t = new Text
{
Text = pair.Value
};
inlineString.AppendChild(t);
newCell.AppendChild(inlineString);
row.AppendChild(newCell);
}
sheetData.AppendChild(row);
rowindex++;
}
worksheetPart.Worksheet.Append(sheetData);
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet"
};
sheets.Append(sheet);
spreadsheetDocument.Save();
// Close the document.
spreadsheetDocument.Close();
}
public byte[] CreaExcelMancanti(AddebitiModel Addebiti)
{
byte[] content;
MemoryStream ms = new MemoryStream();
//string filename = @"c:\temp\mancanti.xlsx";
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
// Setting up columns
Columns columns = new Columns(
new Column
{
Min = 1,
Max = 1,
Width = 15,
CustomWidth = true
},
new Column
{
Min = 2,
Max = 2,
Width = 20,
CustomWidth = false
},
new Column
{
Min = 3,
Max = 3,
Width = 20,
CustomWidth = true
},
new Column
{
Min = 4,
Max = 4,
Width = 40,
CustomWidth = false
},
new Column
{
Min = 5,
Max = 5,
Width = 60,
CustomWidth = true
},
new Column
{
Min = 1,
Max = 1,
Width = 15,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Mancanti"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Data inserimento", CellValues.String, 2),
ConstructCell("Azienda", CellValues.String, 2),
ConstructCell("Lavorante", CellValues.String, 2),
ConstructCell("Modello", CellValues.String, 2),
ConstructCell("Descrizione", CellValues.String, 2),
ConstructCell("Quantità", CellValues.String, 2));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
foreach (AddebitoModel addebito in Addebiti.Addebiti)
{
row = new Row();
row.Append(
ConstructCell(addebito.DataInserimento.ToShortDateString(), CellValues.String, 1),
ConstructCell(addebito.Azienda, CellValues.String, 1),
ConstructCell(addebito.LavoranteDescrizione, CellValues.String, 1),
ConstructCell(addebito.Modello, CellValues.String, 1),
ConstructCell(addebito.ModelloDescrizione, CellValues.String, 1),
ConstructCell(addebito.QuantitaDifettosi.ToString(), CellValues.Number, 1));
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
public byte[] CreaExcelMagazziniEsterni(List <MagazzinoLavorantiEsterniModel> magazzini, string lavorante, string DataInizio, string DataFine)
{
byte[] content;
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
// Setting up columns
Columns columns = new Columns(
new Column
{
Min = 1,
Max = 1,
Width = 15,
CustomWidth = true
},
new Column
{
Min = 2,
Max = 2,
Width = 20,
CustomWidth = false
},
new Column
{
Min = 3,
Max = 3,
Width = 15,
CustomWidth = true
},
new Column
{
Min = 4,
Max = 4,
Width = 15,
CustomWidth = false
},
new Column
{
Min = 5,
Max = 5,
Width = 30,
CustomWidth = true
},
new Column
{
Min = 6,
Max = 6,
Width = 60,
CustomWidth = false
},
new Column
{
Min = 7,
Max = 7,
Width = 10,
CustomWidth = true
},
new Column
{
Min = 8,
Max = 8,
Width = 10,
CustomWidth = true
},
new Column
{
Min = 9,
Max = 9,
Width = 30,
CustomWidth = true
},
new Column
{
Min = 10,
Max = 10,
Width = 60,
CustomWidth = false
},
new Column
{
Min = 11,
Max = 11,
Width = 10,
CustomWidth = true
},
new Column
{
Min = 12,
Max = 12,
Width = 10,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = lavorante
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
Row staticRow = new Row();
staticRow.Append(
ConstructCell("Dal", CellValues.String, 2),
ConstructCell(DataInizio, CellValues.String, 1),
ConstructCell("Al", CellValues.String, 2),
ConstructCell(DataFine, CellValues.String, 1));
sheetData.AppendChild(staticRow);
sheetData.AppendChild(new Row());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Azienda", CellValues.String, 2),
ConstructCell("ODL", CellValues.String, 2),
ConstructCell("Data inizio", CellValues.String, 2),
ConstructCell("Data fine", CellValues.String, 2),
ConstructCell("Modello", CellValues.String, 2),
ConstructCell("Descrizione", CellValues.String, 2),
ConstructCell("Quantità", CellValues.String, 2),
ConstructCell("Peso", CellValues.String, 2),
ConstructCell("Componente", CellValues.String, 2),
ConstructCell("Descrizione", CellValues.String, 2),
ConstructCell("Quantità", CellValues.String, 2),
ConstructCell("Peso", CellValues.String, 2));
sheetData.AppendChild(row);
foreach (MagazzinoLavorantiEsterniModel elemento in magazzini)
{
row = new Row();
row.Append(
ConstructCell(elemento.Azienda, CellValues.String, 1),
ConstructCell(elemento.ODL, CellValues.String, 1),
ConstructCell(elemento.DataInizio, CellValues.String, 1),
ConstructCell(elemento.DataFine, CellValues.String, 1),
ConstructCell(elemento.Modello, CellValues.String, 1),
ConstructCell(elemento.ModelloDescrizione, CellValues.String, 1),
ConstructCell(elemento.Quanita.ToString(), CellValues.String, 1),
ConstructCell(elemento.Peso.ToString(), CellValues.String, 1),
ConstructCell(elemento.Componente, CellValues.String, 1),
ConstructCell(elemento.ComponenteDescrizione, CellValues.String, 1),
ConstructCell(elemento.QuanitaComponente.ToString(), CellValues.String, 1),
ConstructCell(elemento.PesoComponente.ToString(), CellValues.String, 1));
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
static void Main(string[] args)
{
if (args.Count() < 1)
{
Console.WriteLine("Please enter a file path:");
args = new string[1];
args[0] = Console.ReadLine();
}
var path = args[0];
if (!File.Exists(path) || !path.ToUpper().Contains("CSV"))
{
Console.WriteLine("Must provide a valid CSV");
System.Threading.Thread.Sleep(500);
return;
}
List <dynamic> issues;
DataTable csvDataTable = ConvertCSVtoDataTable(path);
//using (var wb = new ClosedXML.Excel.XLWorkbook()) {
// wb.AddWorksheet(table, "Sheet1");
// foreach (var ws in wb.Worksheets)
// {
// ws.Columns().AdjustToContents();
// }
var outputPath = path.Substring(0, path.Length - 3) + "xlsx";
// wb.SaveAs(output);
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(outputPath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Create sheetdatas
SheetData xlSheetData = new SheetData();
Row xlHeaderRow = new Row();
foreach (DataColumn col in csvDataTable.Columns)
{
object cellData = col.ColumnName;
Cell xlCell = null;
if (cellData != null)
{
xlCell = new Cell(new InlineString(new DocumentFormat.OpenXml.Spreadsheet.Text(cellData.ToString())))
{
DataType = CellValues.InlineString
};
}
else
{
xlCell = new Cell(new InlineString(new DocumentFormat.OpenXml.Spreadsheet.Text(String.Empty)))
{
DataType = CellValues.InlineString
};
}
xlHeaderRow.Append(xlCell);
}
xlSheetData.Append(xlHeaderRow);
// Add content as lines
foreach (DataRow row in csvDataTable.Rows)
{
Row xlRow = new Row();
foreach (DataColumn col in csvDataTable.Columns)
{
object cellData = row[col];
Cell xlCell = null;
if (cellData != null)
{
xlCell = new Cell(new InlineString(new DocumentFormat.OpenXml.Spreadsheet.Text(cellData.ToString())))
{
DataType = CellValues.InlineString
};
}
else
{
xlCell = new Cell(new InlineString(new DocumentFormat.OpenXml.Spreadsheet.Text(String.Empty)))
{
DataType = CellValues.InlineString
};
}
xlRow.Append(xlCell);
}
xlSheetData.Append(xlRow);
}
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(xlSheetData);
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "CSV"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
Console.WriteLine("wrote to : " + outputPath);
System.Threading.Thread.Sleep(500);
return;
//}
}
void ExportDocFormat_OpenXML(string sQueryString, string sColumns, string sPath)
{
double iRowCounter = 0;
try
{
using (SqlConnection conMSSQL1 = new SqlConnection(GV.sMSSQL1))
{
string sQueryData = "SELECT " + sColumns + sQueryString;
string sQueryCount = "SELECT COUNT(*) " + sQueryString;
lblExportDisplayPercent.Invoke((MethodInvoker) delegate { lblExportDisplayPercent.Text = "Fetching data..."; });
DataTable dtRowCount = GV.MSSQL1.BAL_ExecuteQuery(sQueryCount);
double iTotalRowCount = Convert.ToDouble(dtRowCount.Rows[0][0]);
conMSSQL1.Open();
//SqlCommand cmdExport = new SqlCommand("SET GLOBAL net_read_timeout = 100;" + sQueryData, conMSSQL1);
SqlCommand cmdExport = new SqlCommand(sQueryData, conMSSQL1);
lblExportDisplayPercent.Invoke(
(MethodInvoker) delegate { lblExportDisplayPercent.Text = "Estimated Row count :" + iTotalRowCount; });
//if (GV.conMYSdfQL.State != ConnectionState.Open)
// GV.conMYSdfQL.Open();
SqlDataReader rdrExport = cmdExport.ExecuteReader();
int iRowsPerSheetCounter = 0;
int iRowPerSheet = 100000;
DataTable dtSchema = rdrExport.GetSchemaTable();
DataTable dtExportData = new DataTable();
var listCols = new List <DataColumn>();
if (iTotalRowCount > 0)
{
if (File.Exists(sPath))
{
File.Delete(sPath);
}
string sSheetName = string.Empty;
if ((GV.sProjectName).Length > 20)
{
sSheetName = (GV.sProjectName).Substring(0, 20);
}
else
{
sSheetName = GV.sProjectName;
}
uint sheetId = 1; //Start at the first sheet in the Excel workbook.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(sPath,
SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
// Add Sheets to the Workbook.
Sheets sheets;
sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = sSheetName + "_" + sheetId
};
sheets.Append(sheet);
//Stopwatch sWatch = new Stopwatch();
//sWatch.Start();
Row rowColumn = new Row();
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]);
var column = new DataColumn(columnName, (Type)(drow["DataType"]));
column.Unique = (bool)drow["IsUnique"];
column.AllowDBNull = (bool)drow["AllowDBNull"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
dtExportData.Columns.Add(column);
Cell cellColumn = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(GM.RemoveNonXMLChars(columnName))
};
rowColumn.AppendChild(cellColumn);
}
sheetData.AppendChild(rowColumn);
while (rdrExport.Read())
{
Row newRow = new Row();
for (int i = 0; i < listCols.Count; i++)
{
Cell cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(GM.RemoveNonXMLChars(rdrExport[i].ToString()))
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
iRowCounter++;
iRowsPerSheetCounter++;
//if (iRowCounter == 4194)
//{ }
lblExportDisplayPercent.Invoke(
(MethodInvoker)
delegate { lblExportDisplayPercent.Text = "Writing " + iRowCounter + " / " + iTotalRowCount; });
circularProgressExport.Value = Convert.ToInt32((iRowCounter / iTotalRowCount) * 100);
if (iRowsPerSheetCounter == iRowPerSheet)
{
lblExportDisplayPercent.Invoke(
(MethodInvoker) delegate { lblExportDisplayPercent.Text = "Splitting Worksheet..."; });
iRowsPerSheetCounter = 0;
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
spreadsheetDocument = SpreadsheetDocument.Open(sPath, true);
workbookpart = spreadsheetDocument.WorkbookPart;
if (workbookpart.Workbook == null)
{
workbookpart.Workbook = new Workbook();
}
lblExportDisplayPercent.Invoke(
(MethodInvoker) delegate { lblExportDisplayPercent.Text = "Opening Worksheet..."; });
worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
if (sheets.Elements <Sheet>().Any())
{
sheetId = sheets.Elements <Sheet>().Max(s => s.SheetId.Value) + 1;
}
else
{
sheetId = 1;
}
var sheeet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = sSheetName + "_" + sheetId
};
sheets.Append(sheeet);
Row rowColumnNewSheet = new Row();
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]);
Cell cellColumn = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(GM.RemoveNonXMLChars(columnName))
};
rowColumnNewSheet.AppendChild(cellColumn);
}
sheetData.AppendChild(rowColumnNewSheet);
}
}
lblExportDisplayPercent.Invoke((MethodInvoker) delegate { lblExportDisplayPercent.Text = "Saving Worksheet..."; });
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
//iExcelExportStatus = 3;
// Call Close when done reading.
lblExportDisplayPercent.Invoke((MethodInvoker) delegate { lblExportDisplayPercent.Text = "Closing Worksheet..."; });
rdrExport.Close();
//GV.conMYSfdQL.Close();
lblExportDisplayPercent.Invoke(
(MethodInvoker) delegate { lblExportDisplayPercent.Text = "Worksheet saved sucessfully..."; });
//sWatch.Stop();
//MessageBoxEx.Show("Exported sucessfully", "Campaign Manager", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
ToastNotification.Show(this, "No data to export", eToastPosition.TopRight);
}
//else
// ToastNotification.Show(this, "Filter doesn't return any row(s) or invalid filter conditions.", eToastPosition.TopRight);
}
}
catch (Exception ex)
{
if (GV.sUserType == "Admin")
{
MessageBoxEx.Show(ex.Message, "Campaign Manager", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
}
static public bool ExportarExcel(DataSet ds, string archivo)
{
try
{
using (var workbook = SpreadsheetDocument.Create(archivo, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
foreach (System.Data.DataTable table in ds.Tables)
{
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = table.TableName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (System.Data.DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
DateTime val;
string dato;
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
dato = dsrow[col].ToString();
if (dato.Length >= 10 && DateTime.TryParse(dato, out val) == true)
{
dato = val.ToString("yyyy/MM/dd");
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dato); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Open(archivo);
excel.Visible = true;
return(true);
}
catch (Exception ex)
{
Utiles.WriteErrorLog(ex.Message);
return(false);
}
}
static void Main(string[] args)
{
string
currentDirectory = Directory.GetCurrentDirectory();
currentDirectory = currentDirectory.Substring(0, currentDirectory.LastIndexOf("bin", currentDirectory.Length - 1));
string
outputFileName = currentDirectory + "xlsx_tst.xlsx";
if (File.Exists(outputFileName))
{
File.Delete(outputFileName);
}
SpreadsheetDocument
spreadsheetDocument = SpreadsheetDocument.Create(outputFileName, SpreadsheetDocumentType.Workbook);
WorkbookPart
workbookpart = spreadsheetDocument.AddWorkbookPart();
#if ADD_NUMBERING_FORMAT
WorkbookStylesPart
workbookStylesPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorkbookStylesPart>();
workbookStylesPart.Stylesheet = new Stylesheet();
Stylesheet
stylesheet = spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet;
//stylesheet.Append(GenerateNumberingFormats());
stylesheet.Append(GenerateFonts());
stylesheet.Append(GenerateFills());
stylesheet.Append(GenerateBorders());
stylesheet.Append(GenerateCellStyleFormats());
stylesheet.Append(GenerateCellFormats());
stylesheet.Append(GenerateCellStyles());
#endif
workbookpart.Workbook = new Workbook();
WorksheetPart
worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets
sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet
sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Test"
};
sheets.Append(sheet);
SheetData
sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
Row
row;
row = new Row()
{
RowIndex = (UInt32Value)2U
};
sheetData.Append(row);
Cell
cell;
cell = new Cell()
{
CellReference = "A2", StyleIndex = (UInt32Value)1U, CellValue = new CellValue("string")
};
row.Append(cell);
/*
* cell = new Cell() { CellReference = "B2", StyleIndex = (UInt32Value)11U, DataType = new EnumValue<CellValues>(CellValues.Boolean), CellValue = new CellValue("1") };
* row.Append(cell);
*
* cell = new Cell() { CellReference = "C2", StyleIndex = (UInt32Value)11U, DataType = new EnumValue<CellValues>(CellValues.Boolean), CellValue = new CellValue("0") };
* row.Append(cell);
*
* newCell.CellValue = new CellValue("100");
* newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
*
* newCell = new Cell() { CellReference = "B1", DataType = new EnumValue<CellValues>(CellValues.Number), StyleIndex = (UInt32Value)1U, CellValue = new CellValue(DateTime.Now.ToOADate().ToString(CultureInfo.InvariantCulture)) };
* row.Append(newCell);
*
*/
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
public override Task WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
{
var query = (IQueryable)context.Object;
var queryString = context.HttpContext.Request.QueryString;
var columns = queryString.Value.Contains("$select") ?
OutputFormatter.GetPropertiesFromSelect(queryString.Value, query.ElementType) :
OutputFormatter.GetProperties(query.ElementType);
var stream = new MemoryStream();
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
var workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
var workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
GenerateWorkbookStylesPartContent(workbookStylesPart);
var sheets = workbookPart.Workbook.AppendChild(new Sheets());
var sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
var sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
var headerRow = new Row();
foreach (var column in columns)
{
headerRow.Append(new Cell()
{
CellValue = new CellValue(column.Key),
DataType = new EnumValue <CellValues>(CellValues.String)
});
}
sheetData.AppendChild(headerRow);
foreach (var item in query)
{
var row = new Row();
foreach (var column in columns)
{
var value = OutputFormatter.GetValue(item, column.Key);
var stringValue = value.ToString();
var cell = new Cell();
var underlyingType = column.Value.IsGenericType &&
column.Value.GetGenericTypeDefinition() == typeof(Nullable <>) ?
Nullable.GetUnderlyingType(column.Value) : column.Value;
var typeCode = Type.GetTypeCode(underlyingType);
if (typeCode == TypeCode.DateTime)
{
if (stringValue != string.Empty)
{
cell.CellValue = new CellValue()
{
Text = DateTime.Parse(stringValue).ToOADate().ToString()
};
cell.StyleIndex = (UInt32Value)1U;
}
}
else if (typeCode == TypeCode.Boolean)
{
cell.CellValue = new CellValue(stringValue.ToLower());
cell.DataType = new EnumValue <CellValues>(CellValues.Boolean);
}
else if (IsNumeric(typeCode))
{
cell.CellValue = new CellValue(stringValue);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else
{
cell.CellValue = new CellValue(stringValue);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
}
row.Append(cell);
}
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
}
if (stream?.Length > 0)
{
stream.Seek(0, SeekOrigin.Begin);
}
return(stream.CopyToAsync(context.HttpContext.Response.Body));
}
public async Task <string> GetXlsxAsync(IReadOnlyCollection <Department> departments)
{
if (departments == null)
{
throw new InvalidOperationException("尚未進行選股，無法匯出。");
}
if (departments.Count == 0)
{
throw new InvalidOperationException("股別數為零，無法匯出。");
}
if (departments.Select(x => x.Students).Sum(x => x.Count) == 0)
{
throw new InvalidOperationException("股內人數加總為零，無法匯出。");
}
var date = DateTime.Now;
var ms = new MemoryStream();
using (var spreadsheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheet.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
// Insert stylesheet to make the top row bold
var workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
workbookStylesPart.Stylesheet = CreateStylesheet();
// Define sheet
var sheetName = $"{date:yyyy}-股員名單";
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet {
Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sheetName
};
sheets.AppendChild(sheet);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
// Insert data
var row = new Row {
RowIndex = 1
};
row.Append(
new Cell {
CellValue = new CellValue("學號"), DataType = CellValues.String
},
new Cell {
CellValue = new CellValue("姓名"), DataType = CellValues.String
},
new Cell {
CellValue = new CellValue("股別"), DataType = CellValues.String
});
sheetData.AppendChild(row);
uint rowIndex = 2;
foreach (var department in departments)
{
foreach (var student in department.Students)
{
var entryRow = new Row {
RowIndex = rowIndex
};
entryRow.Append(
new Cell {
CellValue = new CellValue(student.Id), DataType = CellValues.String, StyleIndex = 1
},
new Cell {
CellValue = new CellValue(student.Name), DataType = CellValues.String, StyleIndex = 1
},
new Cell {
CellValue = new CellValue(student.Department.Name), DataType = CellValues.String, StyleIndex = 1
});
sheetData.AppendChild(entryRow);
rowIndex++;
}
}
// Add title
spreadsheet.PackageProperties.Title = $"{date:yyyy} 選股結果";
spreadsheet.PackageProperties.Creator = "RaffleBlazor 選股程式";
}
return(await ReadStreamToBase64(ms));
}
private void CreateSpreadSheet <T>(string filepath, IEnumerable <T> data)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
var spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
var workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
var workStylePart = workbookpart.AddNewPart <WorkbookStylesPart>();
workStylePart.Stylesheet = new Stylesheet();
// Add Sheets to the Workbook.
var sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet"
};
sheets.Append(sheet);
AddStyleSheet(workStylePart);
var t = typeof(T);
var propInfos = t.GetProperties(BindingFlags.Public | BindingFlags.Instance);
CreateExcelColumns(propInfos);
if (data != null)
{
var enumerable = data as List <T> ?? data.ToList();
var headerRow = new Row()
{
RowIndex = uint.Parse((1).ToString())
};
var sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
ProcessColumns(headerRow, 0, propInfos, enumerable, true);
sheetData.AppendChild(headerRow);
for (var i = 0; i < enumerable.Count(); i++)
{
var excelRow = new Row()
{
RowIndex = uint.Parse((i + 2).ToString())
};
ProcessColumns(excelRow, i, propInfos, enumerable, false);
sheetData.AppendChild(excelRow);
}
}
// Close the document.
spreadsheetDocument.Close();
}
public async Task <FileStreamResult> Download(int id)
{
var storedReport = await _reportService.GetReportResultsAsync(id);
PageTitle = storedReport.request.Name ?? "Report Results";
var viewModel = new ReportResultsViewModel
{
Title = PageTitle
};
viewModel.ReportSet = JsonConvert
.DeserializeObject <StoredReportSet>(storedReport.request.ResultJson);
var ms = new System.IO.MemoryStream();
using (var workbook = SpreadsheetDocument.Create(ms,
DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook();
workbook.WorkbookPart.Workbook.Sheets = new Sheets();
var stylesPart = workbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GetStylesheet();
stylesPart.Stylesheet.Save();
foreach (var report in viewModel.ReportSet.Reports)
{
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
sheetPart.Worksheet = new Worksheet(sheetData);
var sheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>();
var relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>()
.Select(_ => _.SheetId.Value).Max() + 1;
}
string sheetName = report.Title ?? PageTitle ?? "Report Results";
if (sheetName.Length > 31)
{
sheetName = sheetName.Substring(0, 31);
}
var sheet = new Sheet
{
Id = relationshipId,
SheetId = sheetId,
Name = sheetName
};
sheets.Append(sheet);
var maximumColumnWidth = new Dictionary <int, int>();
if (report.HeaderRow != null)
{
var headerRow = new Row();
int columnNumber = 0;
foreach (var dataItem in report.HeaderRow)
{
(var cell, var length) = CreateCell(dataItem);
cell.StyleIndex = ExcelStyleIndexBold;
headerRow.AppendChild(cell);
if (maximumColumnWidth.ContainsKey(columnNumber))
{
maximumColumnWidth[columnNumber]
= Math.Max(maximumColumnWidth[columnNumber], length);
}
else
{
maximumColumnWidth.Add(columnNumber, length);
}
columnNumber++;
}
sheetData.Append(headerRow);
}
foreach (var resultRow in report.Data)
{
var row = new Row();
int columnNumber = 0;
foreach (var resultItem in resultRow)
{
(var cell, var length) = CreateCell(resultItem);
row.AppendChild(cell);
if (maximumColumnWidth.ContainsKey(columnNumber))
{
maximumColumnWidth[columnNumber]
= Math.Max(maximumColumnWidth[columnNumber], length);
}
else
{
maximumColumnWidth.Add(columnNumber, length);
}
columnNumber++;
}
sheetData.Append(row);
}
if (report.FooterRow != null)
{
var footerRow = new Row();
int columnNumber = 0;
foreach (var dataItem in report.FooterRow)
{
(var cell, var length) = CreateCell(dataItem);
cell.StyleIndex = ExcelStyleIndexBold;
footerRow.AppendChild(cell);
if (maximumColumnWidth.ContainsKey(columnNumber))
{
maximumColumnWidth[columnNumber]
= Math.Max(maximumColumnWidth[columnNumber], length);
}
else
{
maximumColumnWidth.Add(columnNumber, length);
}
columnNumber++;
}
sheetData.Append(footerRow);
}
if (report.FooterText != null)
{
foreach (var dataItem in report.FooterText)
{
var footerTextRow = new Row();
(var cell, var length) = CreateCell(dataItem);
footerTextRow.AppendChild(cell);
sheetData.Append(footerTextRow);
}
}
foreach (var value in maximumColumnWidth.Keys.OrderByDescending(_ => _))
{
var columnId = value + 1;
var width = maximumColumnWidth[value] + ExcelPaddingCharacters;
Columns cs = sheet.GetFirstChild <Columns>();
if (cs != null)
{
var columnElements = cs.Elements <Column>()
.Where(_ => _.Min == columnId && _.Max == columnId);
if (columnElements.Count() > 0)
{
var column = columnElements.First();
column.Width = width;
column.CustomWidth = true;
}
else
{
var column = new Column
{
Min = (uint)columnId,
Max = (uint)columnId,
Width = width,
CustomWidth = true
};
cs.Append(column);
}
}
else
{
cs = new Columns();
cs.Append(new Column
{
Min = (uint)columnId,
Max = (uint)columnId,
Width = width,
CustomWidth = true
});
sheetPart.Worksheet.InsertAfter(cs,
sheetPart.Worksheet.GetFirstChild <SheetFormatProperties>());
}
}
}
workbook.Save();
workbook.Close();
ms.Seek(0, System.IO.SeekOrigin.Begin);
var fileOutput = new FileStreamResult(ms, ExcelMimeType)
{
FileDownloadName = $"{PageTitle}.{ExcelFileExtension}"
};
return(fileOutput);
}
}
private bool ExportToExcel(DataTable table, string sFilePath, string sSheetName = "")
{
try
{
SpreadsheetDocument workbook;
if (sSheetName == "")
{
sSheetName = table.TableName;
}
if (File.Exists(sFilePath))
{
workbook = SpreadsheetDocument.Open(sFilePath, true);
}
else
{
workbook = SpreadsheetDocument.Create(sFilePath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
WorkbookPart workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook();
workbook.WorkbookPart.Workbook.Sheets = new Sheets();
}
uint sheetId = 1;
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
sheetPart.Worksheet = new Worksheet(sheetData);
Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
Sheet oSheet = sheets.Elements <Sheet>().Where(s => s.Name == sSheetName).FirstOrDefault();
if (oSheet != null)
{
oSheet.Remove();
}
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sSheetName
};
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);
}
workbook.Close();
return(true);
}
catch (Exception ex)
{
if (ShowMessage)
{
Reporter.ToUser(eUserMsgKeys.ExportExcelFileFailed, ex.Message.ToString());
}
}
return(false);
}
public ActionResult Excel(string isim, string soyisim, string adres, string email)
{
var kisi = new Kisi();
kisi.ad = isim;
kisi.soyad = soyisim;
kisi.adres = adres;
kisi.email = email;
var kisiler = new List <Kisi>();
if (Session["kisiler"] == null)
{
kisiler.Add(kisi);
Session["kisiler"] = kisiler;
}
else
{
kisiler = (List <Kisi>)(Session["kisiler"]);
kisiler.Add(kisi);
Session["kisiler"] = kisiler;
}
using (MemoryStream mem = new MemoryStream())
{
var spreadsheetDocument = SpreadsheetDocument.Create(mem, SpreadsheetDocumentType.Workbook);
var workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
SheetData sheetData1 = new SheetData();
var baslikSatiri = new Row();
baslikSatiri.Append(CreateCell("İsim"));
baslikSatiri.Append(CreateCell("Soyisim"));
baslikSatiri.Append(CreateCell("Adres"));
baslikSatiri.Append(CreateCell("E-mail"));
sheetData1.Append(baslikSatiri);
foreach (var item in kisiler)
{
var tRow = new Row();
tRow.Append(CreateCell(item.ad));
tRow.Append(CreateCell(item.soyad));
tRow.Append(CreateCell(item.adres));
tRow.Append(CreateCell(item.email));
sheetData1.Append(tRow);
}
worksheetPart.Worksheet = new Worksheet(sheetData1);
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "sayfa1"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
string handle = Guid.NewGuid().ToString();
mem.Position = 0;
TempData[handle] = mem.ToArray();
return(new JsonResult()
{
Data = new { FileGuid = handle, FileName = "dosya.xlsx" }
});
}
}
public static void Export(SLExcelData data, string fileName)
{
var document = SpreadsheetDocument
.Create(fileName, 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);
UInt32 rowIdex = 0;
if (data.Headers.Count > 0)
{
// Add header
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));
}
// 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)
{
var cellIdex = 0;
var 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();
}
public Excel(string sheetName, string path, List <string[]> body, string[] header)
{
if (body[0].Length != header.Length)
{
throw new System.Exception("Le colonne sono diverse!");
}
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
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);
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = sheetName
};
Row row = new Row()
{
RowIndex = 1
};
for (int i = 0; i < header.Length; i++)
{
Cell c = new Cell()
{
CellValue = new CellValue(header[i]), DataType = CellValues.String
};
row.Append(c);
}
sheetData.Append(row);
foreach (var v in body)
{
Row r = new Row();
for (int i = 0; i < v.Length; i++)
{
Cell c = new Cell()
{
CellValue = new CellValue(v[i]), DataType = CellValues.String
};
r.Append(c);
}
sheetData.Append(r);
}
sheets.Append(sheet);
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
}
public static void CreateSpreadsheetWorkbook(string filepath)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet"
};
sheets.Append(sheet);
// Get the sheetData cell table.
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
// Add a row to the cell table.
Row row;
row = new Row()
{
RowIndex = 1
};
sheetData.Append(row);
// In the new row, find the column location to insert a cell in A1.
Cell refCell = null;
foreach (Cell cell in row.Elements <Cell>())
{
if (string.Compare(cell.CellReference.Value, "A1", true) > 0)
{
refCell = cell;
break;
}
}
// Add the cell to the cell table at A1.
Cell newCell = new Cell()
{
CellReference = "A1"
};
row.InsertBefore(newCell, refCell);
// Set the cell value to be a numeric value of 100.
newCell.CellValue = new CellValue("100");
newCell.DataType = new EnumValue <CellValues>(CellValues.Number);
// Close the document.
spreadsheetDocument.Close();
}
static public bool ExportaListaAExcel2 <T>(List <T> lista, string archivo, string hoja)
{
try
{
using (var workbook = SpreadsheetDocument.Create(archivo, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = hoja
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
IList <PropertyInfo> campos = new List <PropertyInfo>(typeof(T).GetProperties());
foreach (PropertyInfo campo in campos)
{
columns.Add(campo.Name);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(campo.Name);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (T row in lista)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
DateTime val;
string dato;
foreach (String col in columns)
{
PropertyInfo campo = campos.ToList().Find(x => x.Name == col);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
object obj = campo.GetValue(row, null);
if (obj == null)
{
dato = "";
}
else
{
dato = obj.ToString();
}
if (dato.Length >= 10 && DateTime.TryParse(dato, out val) == true)
{
dato = val.ToString("yyyy/MM/dd");
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dato); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Open(archivo);
excel.Visible = true;
return(true);
}
catch (Exception ex)
{
Utiles.WriteErrorLog(ex.Message);
return(false);
}
}
public static Stream ToXLSX(TranslationModule project)
{
MemoryStream xlsStream = new MemoryStream();
var worksheet = new MemoryWorksheet();
Export.ToIWorksheet(project, worksheet);
var ss = new Stylesheet();
ss.CellStyleFormats = new CellStyleFormats();
ss.CellStyleFormats.Count = 1;
ss.CellStyles = new CellStyles();
ss.CellStyles.Count = 1;
ss.DifferentialFormats = new DifferentialFormats();
ss.DifferentialFormats.Count = 0;
ss.TableStyles = new TableStyles();
ss.TableStyles.Count = 0;
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(xlsStream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = myDoc.AddWorkbookPart();
//workbookPart.Workbook = new Workbook();
WorkbookStylesPart stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = ss;
stylesPart.Stylesheet.Save();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
//SheetData sheetData = new SheetData();
//worksheetPart.Worksheet = new Worksheet(sheetData);
/*
* Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
* Sheet sheet = new Sheet()
* {
* Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart),
* SheetId = 1,
* Name = "mySheet"
* };
* sheets.Append(sheet);
*/
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
for (int row = 0; row < worksheet.Rows; row++)
{
var oxa = new List <OpenXmlAttribute>();
// this is the row index
oxa.Add(new OpenXmlAttribute("r", null, row.ToString()));
writer.WriteStartElement(new Row(), oxa);
for (int col = 0; col < worksheet.Columns; col++)
{
oxa = new List <OpenXmlAttribute>();
// this is the data type ("t"), with CellValues.String ("str")
oxa.Add(new OpenXmlAttribute("t", null, "str"));
string val = worksheet[row, col] != null ? worksheet[row, col].ToString() : "";
//var cell = new Cell(new CellValue(val));
writer.WriteStartElement(new Cell(), oxa);
//Cell c = new Cell();
CellValue v = new CellValue(val);
writer.WriteElement(v);
//c.AppendChild(v);
//writer.WriteElement();
writer.WriteEndElement();
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(myDoc.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
// you can use object initialisers like this only when the properties
// are actual properties. SDK classes sometimes have property-like properties
// but are actually classes. For example, the Cell class has the CellValue
// "property" but is actually a child class internally.
// If the properties correspond to actual XML attributes, then you're fine.
writer.WriteElement(new Sheet()
{
Name = "Sheet1",
SheetId = 1,
Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart)
});
// this is for Sheets
writer.WriteEndElement();
// this is for Workbook
writer.WriteEndElement();
writer.Close();
myDoc.Close();
}
return(xlsStream);
}
public static bool LargeExport(DataTable dt, string filename)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
//this list of attributes will be used when writing a start element
List <OpenXmlAttribute> attributes;
OpenXmlWriter writer;
int columnNum = 0;
document.AddWorkbookPart();
WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
List <String> columns = new List <string>();
attributes = new List <OpenXmlAttribute>();
attributes.Add(new OpenXmlAttribute("r", null, "1"));
writer.WriteStartElement(new Row(), attributes);
foreach (System.Data.DataColumn column in dt.Columns)
{
columns.Add(column.ColumnName);
//create a new list of attributes
// add the row index attribute to the list
attributes = new List <OpenXmlAttribute>();
// add data type attribute - in this case inline string (you might want to look at the shared strings table)
attributes.Add(new OpenXmlAttribute("t", null, "str"));
//add the cell reference attribute
string cName = GetColumnName(columnNum);
attributes.Add(new OpenXmlAttribute("r", "", string.Format("{0}{1}", cName, "1")));
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(column.ColumnName));
writer.WriteEndElement();
}
writer.WriteEndElement();
int rowNum = 1;
foreach (System.Data.DataRow dsrow in dt.Rows)
{
rowNum++;
//create a new list of attributes
attributes = new List <OpenXmlAttribute>();
// add the row index attribute to the list
attributes.Add(new OpenXmlAttribute("r", null, rowNum.ToString()));
//write the row start element with the row index attribute
writer.WriteStartElement(new Row(), attributes);
columnNum = 0;
foreach (String col in columns)
{
columnNum++;
//reset the list of attributes
attributes = new List <OpenXmlAttribute>();
// add data type attribute - in this case inline string (you might want to look at the shared strings table)
attributes.Add(new OpenXmlAttribute("t", null, "str"));
//add the cell reference attribute
string cName = GetColumnName(columnNum);
//if (columnNum == 360)
// throw new Exception();
attributes.Add(new OpenXmlAttribute("r", "", string.Format("{0}{1}", cName, rowNum)));
//write the cell start element with the type and reference attributes
writer.WriteStartElement(new Cell(), attributes);
//write the cell value
string dato = dsrow[col].ToString();
//if (string.IsNullOrEmpty(dato))
// throw new Exception();
DateTime val;
if (dato.Length >= 10 && DateTime.TryParse(dato, out val) == true)
{
string colname = dt.Columns[col].ColumnName;
if (colname.Substring(colname.Length - 2) == "_F")
{
dato = val.ToString("yyyy/MM/dd");
}
if (colname.Substring(colname.Length - 2) == "_H")
{
dato = val.ToString("HH:mm");
}
}
char c = Convert.ToChar(0x1D);
dato = dato.Replace(c, ' ');
c = Convert.ToChar(0x1C);
dato = dato.Replace(c, ' ');
dato = dato.Replace('\r', ' ');
dato = dato.Replace('\n', ' ');
try
{
writer.WriteElement(new CellValue(dato));
}
catch (Exception ex)
{
continue;
}
// write the end cell element
writer.WriteEndElement();
}
// write the end row element
writer.WriteEndElement();
}
// write the end SheetData element
writer.WriteEndElement();
// write the end Worksheet element
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = "Large Sheet",
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
// End Sheets
writer.WriteEndElement();
// End Workbook
writer.WriteEndElement();
writer.Close();
document.Close();
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Open(filename);
excel.Visible = true;
return(true);
}
public static Stream GenerateExcel()
{
var stream = new MemoryStream();
var document = SpreadsheetDocument
.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a new worksheet part to the workbook.
WorksheetPart newWorksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new SheetData());
//Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>();
var sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
string relationshipId = document.WorkbookPart.GetIdOfPart(newWorksheetPart);
//This bit is required for iPad to be able to read the sheets inside the xlsx file. The file will still work fine in Excel
string relationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet";
document.Package.GetPart(document.WorkbookPart.Uri).CreateRelationship(new Uri(newWorksheetPart.Uri.OriginalString.Replace("/xl/", String.Empty).Trim(), UriKind.Relative), TargetMode.Internal, relationshipType);
document.Package.GetPart(document.WorkbookPart.Uri).DeleteRelationship(relationshipId);
PackageRelationshipCollection sheetRelationships = document.Package.GetPart(document.WorkbookPart.Uri).GetRelationshipsByType(relationshipType);
relationshipId = sheetRelationships.Where(f => f.TargetUri.OriginalString == newWorksheetPart.Uri.OriginalString.Replace("/xl/", String.Empty).Trim()).Single().Id;
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Max(s => s.SheetId.Value) + 1;
}
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = "test"
};
sheets.Append(sheet);
//worksheets.Add(new Worksheet(newWorksheetPart.Worksheet, sheetId));
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
/*
* var sheet = new Sheet()
* {
* Id = document.WorkbookPart
* .GetIdOfPart(worksheetPart),
* SheetId = 1,
* Name = "Sheet 1"
* };
* sheets.AppendChild(sheet);
*/
// Add header
UInt32 rowIdex = 0;
var row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
int 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 new [] { new [] { "a", "b", "c" }, new [] { "d", "e", "f" } })
{
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);
}
public ActionResult RolesExcel()
{
RolesRetrieveReq req = null;
RequestParameter para = new RequestParameter();
para.Load(Request);
req = new RolesRetrieveReq();
JsonConvert.PopulateObject(para.Item("json"), req);
var memoryStream = new MemoryStream();
using (var document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
#region Sheet1
SheetData sheetData = new SheetData();
Row row;
#region header
row = new Row(); //header
row.Append(
new Cell()
{
CellValue = new CellValue("序號")
},
new Cell()
{
CellValue = new CellValue("群組名稱")
},
new Cell()
{
CellValue = new CellValue("狀態")
},
new Cell()
{
CellValue = new CellValue("建檔時間")
},
new Cell()
{
CellValue = new CellValue("建檔人員")
},
new Cell()
{
CellValue = new CellValue("異動時間")
},
new Cell()
{
CellValue = new CellValue("異動人員")
}
);
sheetData.AppendChild(row);
#endregion
#region data
RolesRetrieveRes res = new Roles("KYL").ReportData(req);
foreach (ROLES data in res.ROLES) //data
{
row = new Row();
row.Append(
new Cell()
{
CellValue = new CellValue(data.SN.ToString())
},
new Cell()
{
CellValue = new CellValue(data.NAME.ToString())
},
new Cell()
{
CellValue = new CellValue(data.MODE.ToString())
},
new Cell()
{
CellValue = new CellValue(data.CDATE.ToString())
},
new Cell()
{
CellValue = new CellValue(data.CUSER.ToString())
},
new Cell()
{
CellValue = new CellValue(data.MDATE.ToString())
},
new Cell()
{
CellValue = new CellValue(data.MUSER.ToString())
}
);
sheetData.AppendChild(row);
}
#endregion
Worksheet worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet; //add a Worksheet to the WorksheetPart
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(document.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = "工作表1"
});
#endregion
}
memoryStream.Seek(0, SeekOrigin.Begin);
return(File(memoryStream.ToArray(), "application/vnd.ms-excel", "TEST.xlsx"));
}
public static void CreateDoc(Info info)
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
SharedStringTablePart shareStringPart =
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
?
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
:
spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets =
spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 1U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "E1"
});
uint rowIndex = 2;
var mas = new[] { "A", "B", "C", "D", "E" };
int i = 0;
foreach (var itle in info.Colon)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = mas[i],
RowIndex = rowIndex,
Text = itle,
StyleIndex = 0U
});
i++;
}
rowIndex++;
foreach (var date in info.Clients)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = date.Pasport,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = date.ClientFIO,
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = date.PhoneNumber,
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = rowIndex,
Text = date.Email,
StyleIndex = 1U
});
rowIndex++;
}
workbookpart.Workbook.Save();
}
}
public FileStreamResult ToExcel(IQueryable query)
{
var columns = GetProperties(query.ElementType);
var stream = new MemoryStream();
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
var workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
var workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
GenerateWorkbookStylesPartContent(workbookStylesPart);
var sheets = workbookPart.Workbook.AppendChild(new Sheets());
var sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
var sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
var headerRow = new Row();
foreach (var column in columns)
{
headerRow.Append(new Cell()
{
CellValue = new CellValue(column.Key),
DataType = new EnumValue <CellValues>(CellValues.String)
});
}
sheetData.AppendChild(headerRow);
foreach (var item in query)
{
var row = new Row();
foreach (var column in columns)
{
var value = GetValue(item, column.Key);
var stringValue = $"{value}".Trim();
var cell = new Cell();
var underlyingType = column.Value.IsGenericType &&
column.Value.GetGenericTypeDefinition() == typeof(Nullable <>) ?
Nullable.GetUnderlyingType(column.Value) : column.Value;
var typeCode = Type.GetTypeCode(underlyingType);
if (typeCode == TypeCode.DateTime)
{
if (stringValue != string.Empty)
{
cell.CellValue = new CellValue()
{
Text = DateTime.Parse(stringValue).ToOADate().ToString()
};
cell.StyleIndex = (UInt32Value)1U;
}
}
else if (typeCode == TypeCode.Boolean)
{
cell.CellValue = new CellValue(stringValue.ToLower());
cell.DataType = new EnumValue <CellValues>(CellValues.Boolean);
}
else if (IsNumeric(typeCode))
{
cell.CellValue = new CellValue(stringValue);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else
{
cell.CellValue = new CellValue(stringValue);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
}
row.Append(cell);
}
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
}
if (stream?.Length > 0)
{
stream.Seek(0, SeekOrigin.Begin);
}
var result = new FileStreamResult(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
result.FileDownloadName = "Export.xlsx";
return(result);
}
static void Main(string[] args)
{
string path = @"C:\Temp\";
var fileName = Path.Combine(path, @"test.xlsx");
if (File.Exists(fileName))
{
File.Delete(fileName);
}
var headerList = new string[] { "Header 1", "Header 2", "Header 3", "Header 4" };
//sheet1
var boolList = new bool[] { true, false, true, false };
var intList = new int[] { 1, 2, 3, -4 };
var dateList = new DateTime[] { DateTime.Now, DateTime.Today, DateTime.Parse("1/1/2014"), DateTime.Parse("2/2/2014") };
var sharedStringList = new string[] { "shared string", "shared string", "cell 3", "cell 4" };
var inlineStringList = new string[] { "inline string", "inline string", "3>", "<4" };
using (var spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// create the workbook
var workbookPart = spreadSheet.AddWorkbookPart();
var openXmlExportHelper = new OpenXmlWriterHelper();
//openXmlExportHelper.SaveCustomStylesheet(workbookPart);
var style = new XlsxStyleSheet(workbookPart);
var dataStyle = new XlsxCellFormat()
{
Name = "Data", ForegroundColor = new XlsxColor("bb4055")
};
style.AddCellFormat(dataStyle);
var headerStyle = new XlsxCellFormat()
{
Name = "Header",
ForegroundColor = new XlsxColor("C8EEFF"),
Border = XlsxBorder.CreateBox(BorderStyleValues.Medium, new XlsxColor("bb4055"))
};
style.AddCellFormat(headerStyle);
style.AddCellFormat(new XlsxCellFormat("DataDate", dataStyle)
{
NumberFormat = @"[$-409]m/d/yy\ h:mm\ AM/PM;@"
});
style.AddCellFormat(new XlsxCellFormat()
{
Name = "DataDateWhite", NumberFormat = @"[$-409]m/d/yy\ h:mm\ AM/PM;@"
});
style.Save();
var workbook = workbookPart.Workbook = new Workbook();
var sheets = workbook.AppendChild <Sheets>(new Sheets());
// create worksheet 1
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
using (var worksheet = new XlsxWorksSheet(style, workbookPart, worksheetPart, new List <XlsxColumn>()
{
new XlsxColumn()
{
ColumnNumber = 1, Width = 25
},
new XlsxColumn()
{
ColumnNumber = 2, Width = 25
}
}))
{
worksheet.WriteRow(new List <XlsxCell>()
{
new XlsxSharedStringCell("Id", "Header"),
new XlsxSharedStringCell("Name", "Header"),
new XlsxSharedStringCell("Lastname", "Header"),
new XlsxSharedStringCell("DocumentNumber", "Header"),
new XlsxSharedStringCell("Birthdate", "Header")
});
var odd = true;
foreach (var data in GenerateData())
{
var format = "";
var formatDate = "DataDateWhite";
if (!odd)
{
format = "Data";
formatDate = "DataDate";
odd = true;
}
else
{
odd = false;
}
worksheet.WriteRow(new List <XlsxCell>()
{
new XlsxNumberCell(data.Id, format),
new XlsxSharedStringCell(data.Name, format),
new XlsxSharedStringCell(data.Lastname, format),
new XlsxSharedStringCell(data.DocumentNumber, format),
new XlsxDateCell(data.Birthdate.ToOADate().ToString(CultureInfo.InvariantCulture), formatDate)
});
}
}
}
}
public byte[] EstraiMovimentiPreziosi(List <Movimenti> movimenti, List <SaldoCasseforti> saldi, string dataInizio, string dataFine)
{
// StringBuilder sb = new StringBuilder();
byte[] content;
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart wsCassaforteGrande = workbookPart.AddNewPart <WorksheetPart>();
wsCassaforteGrande.Worksheet = new Worksheet();
WorksheetPart wsCassafortePiccola = workbookPart.AddNewPart <WorksheetPart>();
wsCassafortePiccola.Worksheet = new Worksheet();
WorksheetPart wsSaldi = workbookPart.AddNewPart <WorksheetPart>();
wsSaldi.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
Columns colonneCassaforteGrande = new Columns();
for (int i = 0; i < 6; i++)
{
Column c = new Column();
UInt32Value u = new UInt32Value((uint)(i + 1));
c.Min = u;
c.Max = u;
c.Width = 25;
c.CustomWidth = true;
colonneCassaforteGrande.Append(c);
}
Columns colonneCassafortePiccola = new Columns();
for (int i = 0; i < 6; i++)
{
Column c = new Column();
UInt32Value u = new UInt32Value((uint)(i + 1));
c.Min = u;
c.Max = u;
c.Width = 25;
c.CustomWidth = true;
colonneCassafortePiccola.Append(c);
}
Columns colonneSaldi = new Columns();
for (int i = 0; i < 3; i++)
{
Column c = new Column();
UInt32Value u = new UInt32Value((uint)(i + 1));
c.Min = u;
c.Max = u;
c.Width = 25;
c.CustomWidth = true;
colonneSaldi.Append(c);
}
wsCassaforteGrande.Worksheet.AppendChild(colonneCassaforteGrande);
wsCassafortePiccola.Worksheet.AppendChild(colonneCassafortePiccola);
wsSaldi.Worksheet.AppendChild(colonneSaldi);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sCassaforteGrande = new Sheet()
{
Id = workbookPart.GetIdOfPart(wsCassaforteGrande), SheetId = 1, Name = "Cassaforte grande"
};
Sheet sCassafortePiccola = new Sheet()
{
Id = workbookPart.GetIdOfPart(wsCassafortePiccola), SheetId = 2, Name = "Cassaforte piccola"
};
Sheet sSaldi = new Sheet()
{
Id = workbookPart.GetIdOfPart(wsSaldi), SheetId = 3, Name = "Saldi"
};
sheets.Append(sCassaforteGrande);
sheets.Append(sCassafortePiccola);
sheets.Append(sSaldi);
workbookPart.Workbook.Save();
SheetData sheetDataCassaforteGrande = wsCassaforteGrande.Worksheet.AppendChild(new SheetData());
SheetData sheetDataCassafortePiccola = wsCassafortePiccola.Worksheet.AppendChild(new SheetData());
SheetData sheetDataSaldi = wsSaldi.Worksheet.AppendChild(new SheetData());
Row rowHeaderGrande = new Row();
rowHeaderGrande.Append(ConstructCell("Giorno", CellValues.String, 2));
rowHeaderGrande.Append(ConstructCell("Materiale", CellValues.String, 2));
rowHeaderGrande.Append(ConstructCell("Dare (gr.)", CellValues.String, 2));
rowHeaderGrande.Append(ConstructCell("Avere", CellValues.String, 2));
rowHeaderGrande.Append(ConstructCell("Utente", CellValues.String, 2));
rowHeaderGrande.Append(ConstructCell("Causale", CellValues.String, 2));
sheetDataCassaforteGrande.AppendChild(rowHeaderGrande);
Row rowHeaderPiccola = new Row();
rowHeaderPiccola.Append(ConstructCell("Giorno", CellValues.String, 2));
rowHeaderPiccola.Append(ConstructCell("Materiale", CellValues.String, 2));
rowHeaderPiccola.Append(ConstructCell("Dare (gr.)", CellValues.String, 2));
rowHeaderPiccola.Append(ConstructCell("Avere", CellValues.String, 2));
rowHeaderPiccola.Append(ConstructCell("Utente", CellValues.String, 2));
rowHeaderPiccola.Append(ConstructCell("Causale", CellValues.String, 2));
sheetDataCassafortePiccola.AppendChild(rowHeaderPiccola);
Row rowHeaderSaldi = new Row();
rowHeaderSaldi.Append(ConstructCell("Materiale", CellValues.String, 2));
rowHeaderSaldi.Append(ConstructCell("Cassaforte grande", CellValues.String, 2));
rowHeaderSaldi.Append(ConstructCell("Cassaforte piccola", CellValues.String, 2));
sheetDataSaldi.AppendChild(rowHeaderSaldi);
foreach (Movimenti movimento in movimenti.Where(x => x.Cassaforte == "A"))//grande
{
Row rowCassaforte = new Row();
rowCassaforte.Append(ConstructCell(movimento.Giorno.ToString("dd/MM/yyyy"), CellValues.String, 1));
rowCassaforte.Append(ConstructCell(movimento.Materiale, CellValues.String, 1));
rowCassaforte.Append(ConstructCell(movimento.Dare, CellValues.String, 1));
rowCassaforte.Append(ConstructCell(movimento.Avere, CellValues.String, 1));
rowCassaforte.Append(ConstructCell(movimento.Utente, CellValues.String, 1));
rowCassaforte.Append(ConstructCell(movimento.Causale, CellValues.String, 1));
sheetDataCassaforteGrande.AppendChild(rowCassaforte);
}
foreach (Movimenti movimento in movimenti.Where(x => x.Cassaforte == "B"))//piccola
{
Row rowCassaforte = new Row();
rowCassaforte.Append(ConstructCell(movimento.Giorno.ToString("dd/MM/yyyy"), CellValues.String, 1));
rowCassaforte.Append(ConstructCell(movimento.Materiale, CellValues.String, 1));
rowCassaforte.Append(ConstructCell(movimento.Dare, CellValues.String, 1));
rowCassaforte.Append(ConstructCell(movimento.Avere, CellValues.String, 1));
rowCassaforte.Append(ConstructCell(movimento.Utente, CellValues.String, 1));
rowCassaforte.Append(ConstructCell(movimento.Causale, CellValues.String, 1));
sheetDataCassafortePiccola.AppendChild(rowCassaforte);
}
foreach (SaldoCasseforti saldo in saldi)
{
Row rowSaldo = new Row();
rowSaldo.Append(ConstructCell(saldo.Materiale, CellValues.String, 1));
rowSaldo.Append(ConstructCell(saldo.SaldoA, CellValues.String, 1));
rowSaldo.Append(ConstructCell(saldo.SaldoB, CellValues.String, 1));
sheetDataSaldi.AppendChild(rowSaldo);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
private MemoryStream CreateExcelFile(IEnumerable <object> data)
{
var ms = new MemoryStream();
using (SpreadsheetDocument spreedDoc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
//openxml stuff
var wbPart = spreedDoc.AddWorkbookPart();
wbPart.Workbook = new Workbook();
var worksheetPart = wbPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
wbPart.Workbook.AppendChild <Sheets>(new Sheets());
var sheet = new Sheet()
{
Id = wbPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet1"
};
var workingSheet = ((WorksheetPart)wbPart.GetPartById(sheet.Id)).Worksheet;
//get model properties
var props = new List <PropertyInfo>(data.First().GetType().GetProperties());
//header
var headerRow = new Row();
foreach (var prop in props)
{
headerRow.AppendChild(
GetCell(prop.Name)
);
}
sheetData.AppendChild(headerRow);
//body
foreach (var record in data)
{
var row = new Row();
foreach (var prop in props)
{
var propValue = prop.GetValue(record, null);
if (propValue != null)
{
var str = propValue.ToString();
row.AppendChild(
GetCell(str)
);
}
else
{
row.AppendChild(
GetCell(string.Empty));
}
}
sheetData.AppendChild(row);
}
wbPart.Workbook.Sheets.AppendChild(sheet);
wbPart.Workbook.Save();
}
return(ms);
}
public byte[] CreaExcelFattureRitardate(FattureRitardateModel FattureRitardate)
{
byte[] content;
MemoryStream ms = new MemoryStream();
//string filename = @"c:\temp\mancanti.xlsx";
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
// Setting up columns
Columns columns = new Columns(
new Column
{
Min = 1,
Max = 1,
Width = 40,
CustomWidth = true
},
new Column
{
Min = 2,
Max = 2,
Width = 20,
CustomWidth = false
},
new Column
{
Min = 3,
Max = 3,
Width = 20,
CustomWidth = true
},
new Column
{
Min = 4,
Max = 4,
Width = 20,
CustomWidth = false
},
new Column
{
Min = 5,
Max = 5,
Width = 20,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Fatture Ritardate"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("ODL", CellValues.String, 2),
ConstructCell("Data Creazione", CellValues.String, 2),
ConstructCell("Utente Inserimento", CellValues.String, 2),
ConstructCell("Lavorante", CellValues.String, 2),
ConstructCell("Data Scadenza", CellValues.String, 2));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
foreach (FatturaRitardataModel FatturaRitardata in FattureRitardate.FattureRitardate)
{
row = new Row();
row.Append(
ConstructCell(FatturaRitardata.ODL, CellValues.String, 1),
ConstructCell(FatturaRitardata.DATA_CREAZIONE.ToShortDateString(), CellValues.String, 1),
ConstructCell(FatturaRitardata.UIDUSER_INSERIMENTO, CellValues.String, 1),
ConstructCell(FatturaRitardata.LAVORANTE, CellValues.String, 1),
ConstructCell(FatturaRitardata.DATA_SCADENZA.ToShortDateString(), CellValues.String, 1));
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
public async Task WriteDocument(DataSet dataSet, string outputPath)
{
outputPath = await PreparePath(outputPath);
using (var workbook = SpreadsheetDocument.Create(outputPath, SpreadsheetDocumentType.Workbook))
{
uint sheetId = 1;
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook();
workbook.WorkbookPart.Workbook.Sheets = new Sheets();
foreach (DataTable table in dataSet.Tables)
{
var sheetData = new SheetData();
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
sheetPart.Worksheet = new Worksheet(sheetData);
var sheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>();
var relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
UInt32 rowIdex = 0;
var cellIdex = 0;
var headerRow = new Row {
RowIndex = ++rowIdex
};
var columns = new List <string>();
var sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = table.TableName
};
sheets.Append(sheet);
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
headerRow.AppendChild(await CreateTextCell(await GetColumnLetter(cellIdex++),
rowIdex, column.ColumnName ?? string.Empty));
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
cellIdex = 0;
var newRow = new Row {
RowIndex = ++rowIdex
};
foreach (String col in columns)
{
newRow.AppendChild(await CreateTextCell(await GetColumnLetter(cellIdex++),
rowIdex, dsrow[col].ToString() ?? string.Empty));
}
sheetData.AppendChild(newRow);
}
}
}
}
public byte[] CreaExcelGiacenzeMagazzino(List <ModelloGiacenzaModel> giacenze)
{
byte[] content;
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
// Setting up columns
Columns columns = new Columns(
new Column
{
Min = 1,
Max = 1,
Width = 35,
CustomWidth = true
},
new Column
{
Min = 2,
Max = 2,
Width = 60,
CustomWidth = false
},
new Column
{
Min = 3,
Max = 3,
Width = 15,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Giacenze"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Modello", CellValues.String, 2),
ConstructCell("Dettaglio", CellValues.String, 2),
ConstructCell("Giacenze", CellValues.String, 2));
sheetData.AppendChild(row);
foreach (ModelloGiacenzaModel elemento in giacenze)
{
row = new Row();
row.Append(
ConstructCell(elemento.Modello, CellValues.String, 1),
ConstructCell(elemento.Descrizione, CellValues.String, 1),
ConstructCell(elemento.Giacenza, CellValues.String, 1));
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
public bool WriteReports(IEnumerable <CaseReportForListing> reports, string[] fields, Stream stream)
{
var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
WorkbookPart workbook = document.AddWorkbookPart();
workbook.Workbook = new Workbook();
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
// Create a sheet in the document
var data = new SheetData();
WorksheetPart worksheet = workbook.AddNewPart <WorksheetPart>();
worksheet.Worksheet = new Worksheet(data);
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheet), SheetId = 1, Name = "Case Reports"
};
sheets.Append(sheet);
uint rowIndex = 0;
// Add some headers
{
var row = new Row {
RowIndex = ++rowIndex
};
data.Append(row);
var headers = new SortedDictionary <string, string>
{
{ "A", "Date" },
{ "B", "Time" },
{ "C", "Status" },
{ "D", "Data Collector" },
{ "E", "Region" },
{ "F", "District" },
{ "G", "Village" },
{ "H", "Health Risk" },
{ "I", "Males < 5" },
{ "J", "Males ≥ 5" },
{ "K", "Females < 5" },
{ "L", "Females ≥ 5" },
{ "M", "Lat. / Long." },
{ "N", "Message" },
{ "O", "Errors" }
};
foreach (var header in headers)
{
var cell = new Cell {
CellReference = header.Key + rowIndex
};
row.Append(cell);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
cell.CellValue = new CellValue(header.Value);
}
}
// Insert data
foreach (var report in reports.OrderByDescending(e => e.Timestamp))
{
var row = new Row {
RowIndex = ++rowIndex
};
data.Append(row);
var date = new Cell {
CellReference = "A" + rowIndex
};
row.Append(date);
date.DataType = new EnumValue <CellValues>(CellValues.Date);
date.CellValue = new CellValue(report.Timestamp.ToString("yyyy MMMM dd"));
var timestamp = new Cell {
CellReference = "B" + rowIndex
};
row.Append(timestamp);
timestamp.DataType = new EnumValue <CellValues>(CellValues.String);
timestamp.CellValue = new CellValue(report.Timestamp.ToString("HH:mm:ss"));
var status = new Cell {
CellReference = "C" + rowIndex
};
row.Append(status);
status.DataType = new EnumValue <CellValues>(CellValues.String);
var origin = new Cell {
CellReference = "D" + rowIndex
};
row.Append(origin);
origin.DataType = new EnumValue <CellValues>(CellValues.String);
origin.CellValue = new CellValue(report.DataCollectorId != null ? report.DataCollectorDisplayName : "Origin: " + report.Origin);
var region = new Cell {
CellReference = "E" + rowIndex
};
row.Append(region);
region.DataType = new EnumValue <CellValues>(CellValues.String);
region.CellValue = new CellValue(report.DataCollectorId != null ? report.DataCollectorRegion : "");
var district = new Cell {
CellReference = "F" + rowIndex
};
row.Append(district);
district.DataType = new EnumValue <CellValues>(CellValues.String);
district.CellValue = new CellValue(report.DataCollectorId != null ? report.DataCollectorDistrict : "");
var village = new Cell {
CellReference = "G" + rowIndex
};
row.Append(village);
village.DataType = new EnumValue <CellValues>(CellValues.String);
village.CellValue = new CellValue(report.DataCollectorId != null ? report.DataCollectorVillage : "");
var healthrisk = new Cell {
CellReference = "H" + rowIndex
};
row.Append(healthrisk);
healthrisk.DataType = new EnumValue <CellValues>(CellValues.String);
var malesUnder5 = new Cell {
CellReference = "I" + rowIndex
};
row.Append(malesUnder5);
malesUnder5.DataType = new EnumValue <CellValues>(CellValues.Number);
var malesOver5 = new Cell {
CellReference = "J" + rowIndex
};
row.Append(malesOver5);
malesOver5.DataType = new EnumValue <CellValues>(CellValues.Number);
var femalesUnder5 = new Cell {
CellReference = "K" + rowIndex
};
row.Append(femalesUnder5);
femalesUnder5.DataType = new EnumValue <CellValues>(CellValues.Number);
var femalesOver5 = new Cell {
CellReference = "L" + rowIndex
};
row.Append(femalesOver5);
femalesOver5.DataType = new EnumValue <CellValues>(CellValues.Number);
var location = new Cell {
CellReference = "M" + rowIndex
};
row.Append(location);
location.DataType = new EnumValue <CellValues>(CellValues.String);
location.CellValue = new CellValue(report.Location != null ? report.Location.Latitude + "/" + report.Location.Longitude : "");
var message = new Cell {
CellReference = "N" + rowIndex
};
row.Append(message);
message.DataType = new EnumValue <CellValues>(CellValues.String);
message.CellValue = new CellValue(report.Message);
var error = new Cell {
CellReference = "O" + rowIndex
};
row.Append(error);
error.DataType = new EnumValue <CellValues>(CellValues.String);
if (report.HealthRiskId != null)
{
status.CellValue = new CellValue("Success");
healthrisk.CellValue = new CellValue(report.HealthRisk);
malesUnder5.CellValue = new CellValue(report.NumberOfMalesUnder5.ToString());
malesOver5.CellValue = new CellValue(report.NumberOfMalesAged5AndOlder.ToString());
femalesUnder5.CellValue = new CellValue(report.NumberOfFemalesUnder5.ToString());
femalesOver5.CellValue = new CellValue(report.NumberOfFemalesAged5AndOlder.ToString());
error.CellValue = new CellValue("");
}
else
{
status.CellValue = new CellValue("Error");
healthrisk.CellValue = new CellValue("");
malesUnder5.CellValue = new CellValue("");
malesOver5.CellValue = new CellValue("");
femalesUnder5.CellValue = new CellValue("");
femalesOver5.CellValue = new CellValue("");
error.CellValue = new CellValue(report.ParsingErrorMessage != null ? string.Join(".", report.ParsingErrorMessage) : "");
}
}
// Save the document in memory, and serve to client
workbook.Workbook.Save();
document.Close();
return(true);
}