private void ExportDatatableToExcel(DataTable table, string destination)
{
using (var workbook = SpreadsheetDocument.Create(destination, 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();
uint sheetId = 1;
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);
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 (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 (DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
//new
public void ExportDSToExcel(DataSet ds, string destination)
{
using (var workbook = SpreadsheetDocument.Create(destination, 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();
uint sheetId = 1;
foreach (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);
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 (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 (DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
}
private static void GenerateExcel(string excelFilePath, DataTable dataTable)
{
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(excelFilePath, true))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable <Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
Sheet sheet = sheets.SingleOrDefault(s => s.Name == "EC 2 SAP Analysis");
if (sheet == null)
{
return;
}
string relationshipId = sheet.Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
//DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in dataTable.Columns)
{
columns.Add(column.ColumnName);
//DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
//cell.DataType = CellValues.String;
//cell.CellValue = new CellValue(column.ColumnName);
//headerRow.AppendChild(cell);
}
//sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in dataTable.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookPart.Workbook.Save();
}
}
}//end method
public static MemoryStream ExportDataSet(DataSet ds) {
MemoryStream ms = new MemoryStream();
using (var workbook = SpreadsheetDocument.Create(ms, 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() > 1) {
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);
}//end loop
sheetData.AppendChild(headerRow);
foreach (System.Data.DataRow dsrow in table.Rows) {
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns) {
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}//end loop
sheetData.AppendChild(newRow);
}//end loop
}//end loop
workbookPart.Workbook.Save();
workbook.Close();
}//end using
ms.Position = 0;
return ms;
}//end method
private MSOpenXML.Row CreateContentRow(MSOpenXML.SheetData ws, int row, object[] values, Type[] types)
{
//Create cells that contain data
MSOpenXML.Row r = ws.AppendChild(new MSOpenXML.Row());
r.RowIndex = (uint)row;
for (int i = 0; i < values.Length; i++)
{
MSOpenXML.Cell c = r.AppendChild(new MSOpenXML.Cell());
c.CellReference = CellName(row, i);
//if (values[i] != null)
Type t = types[i];
if (t == null)
{
c.DataType = MSOpenXML.CellValues.String;
c.CellValue = new MSOpenXML.CellValue();
}
else if (t.In(typeof(int), typeof(int?), typeof(long), typeof(long?)))
{
c.StyleIndex = 2; //DataType = dos.CellValues.Number;
c.CellValue = new MSOpenXML.CellValue(values[i] == null ? null : ((long)Convert.ChangeType(values[i], typeof(long))).ToString());
}
else if (t.In(typeof(decimal), typeof(decimal?), typeof(float), typeof(float?), typeof(double), typeof(double?)))
{
c.StyleIndex = 3; //DataType = dos.CellValues.Number;
c.CellValue = new MSOpenXML.CellValue(values[i] == null ? null : ((decimal)Convert.ChangeType(values[i], typeof(decimal))).ToString(_en_us_ci.NumberFormat));
}
else if (t.In(typeof(DateTime), typeof(DateTime?)))
{
c.StyleIndex = 1; //.DataType = dos.CellValues.Date;
c.CellValue = new MSOpenXML.CellValue(values[i] == null ? null : ((DateTime)values[i]).ToOADate().ToString(_en_us_ci.NumberFormat));
}
else if (t.In(typeof(bool), typeof(bool?)))
{
c.DataType = MSOpenXML.CellValues.Boolean;
c.CellValue = new MSOpenXML.CellValue(values[i].ToString());
}
else
{
c.DataType = MSOpenXML.CellValues.String;
c.CellValue = new MSOpenXML.CellValue(values[i] == null ? null : values[i].ToString());
}
}
return(r);
}
public static System.IO.MemoryStream createExcelReport(DataSet ds)
{
using (System.IO.MemoryStream mem = new System.IO.MemoryStream())
{
SpreadsheetDocument workbook = SpreadsheetDocument.
Create(mem, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = workbook.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
foreach (System.Data.DataTable table in ds.Tables)
{
var sheetPart = workbookpart.AddNewPart <WorksheetPart>();
sheetPart.Worksheet = new Worksheet(new SheetData());
var sheetData = sheetPart.Worksheet.GetFirstChild <SheetData>();
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());// workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
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();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
if (col.GetType() == typeof(Decimal))
{
cell.DataType = CellValues.Number;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = table.TableName
};
sheets.Append(sheet);
}
workbookpart.Workbook.Save();
workbook.Close();
return(mem);
}
}
public override void Write()
{
ExportFileName = PopulatedName(ExportFileName);
if (!String.IsNullOrWhiteSpace(ExportFileName))
{
DocProperties["FileName"] = ExportFileName;
DocProperties["TableCount"] = _dataSet.Tables.Count.ToString();
if (PopulatePropertiesOnly)
{
if (_dataSet != null)
{
foreach (DataTable dTable in _dataSet.Tables)
{
if (dTable.Rows.Count > 0)
{
foreach (DataColumn dColumn in dTable.Columns)
{
DocProperties[dColumn.ColumnName] = dTable.Rows[0][dColumn].ToString();
}
}
}
}
}
switch (DestinationType)
{
case OfficeFileType.WordDocument:
WordprocessingDocument doc;
if (File.Exists(TemplateFileName))
{
doc = WordprocessingDocument.CreateFromTemplate(TemplateFileName);
doc = (WordprocessingDocument)doc.SaveAs(ExportFileName);
}
else
{
doc = WordprocessingDocument.Create(ExportFileName, WordprocessingDocumentType.Document);
}
CustomFilePropertiesPart customProp = doc.CustomFilePropertiesPart;
if (customProp == null)
{
customProp = doc.AddCustomFilePropertiesPart();
}
SetFileProperties(customProp);
MainDocumentPart mainDoc = doc.MainDocumentPart;
if (mainDoc == null)
{
mainDoc = doc.AddMainDocumentPart();
}
DocumentSettingsPart settingsPart = mainDoc.GetPartsOfType <DocumentSettingsPart>().First();
UpdateFieldsOnOpen updateFields = new UpdateFieldsOnOpen
{
Val = new OnOffValue(true)
};
settingsPart.Settings.PrependChild <UpdateFieldsOnOpen>(updateFields);
settingsPart.Settings.Save();
if (!PopulatePropertiesOnly)
{
if (mainDoc.Document == null)
{
mainDoc.Document = new word.Document();
}
word.Body body = new word.Body();
bool firstTable = true;
foreach (DataTable dt in _dataSet.Tables)
{
if (!firstTable)
{
body.Append(GetPageBreak());
}
else
{
firstTable = false;
}
body.Append(GetParagraph(dt.TableName));
body.Append(GetWordTable(dt));
}
mainDoc.Document.Append(body);
}
mainDoc.Document.Save();
doc.Dispose();
break;
case OfficeFileType.ExcelWorkbook:
SpreadsheetDocument spreadSheet;
if (File.Exists(TemplateFileName))
{
spreadSheet = SpreadsheetDocument.CreateFromTemplate(TemplateFileName);
spreadSheet = (SpreadsheetDocument)spreadSheet.SaveAs(ExportFileName);
}
else
{
spreadSheet = SpreadsheetDocument.Create(ExportFileName, SpreadsheetDocumentType.Workbook);
spreadSheet.Save();
}
using (SpreadsheetDocument workbook = spreadSheet)
{
CustomFilePropertiesPart excelCustomProp = workbook.AddCustomFilePropertiesPart();
SetFileProperties(excelCustomProp);
if (workbook.WorkbookPart == null)
{
workbook.AddWorkbookPart();
}
if (workbook.WorkbookPart.Workbook == null)
{
workbook.WorkbookPart.Workbook = new excel.Workbook();
}
if (workbook.WorkbookPart.Workbook.Sheets == null)
{
workbook.WorkbookPart.Workbook.Sheets = new excel.Sheets();
}
excel.Sheets sheets = workbook.WorkbookPart.Workbook.Sheets;
foreach (DataTable table in _dataSet.Tables)
{
excel.SheetData sheetData = null;
WorksheetPart sheetPart = null;
excel.Sheet sheet = null;
foreach (OpenXmlElement element in sheets.Elements())
{
if (element is Sheet)
{
sheet = (Sheet)element;
if (sheet.Name.Value.Equals(table.TableName, StringComparison.CurrentCultureIgnoreCase))
{
//Assign the sheetPart
sheetPart = (WorksheetPart)workbook.WorkbookPart.GetPartById(sheet.Id.Value);
sheetData = sheetPart.Worksheet.GetFirstChild <SheetData>();
break;
}
}
sheet = null;
}
if (sheet == null)
{
sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>(); //Create a new WorksheetPart
sheetData = new excel.SheetData(); //create a new SheetData
sheetPart.Worksheet = new excel.Worksheet(sheetData); /// Create a new Worksheet with the sheetData and link it to the sheetPart...
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); //get the ID of the sheetPart.
sheet = new excel.Sheet()
{
Id = relationshipId, SheetId = 1, Name = table.TableName
}; //create a new sheet
sheets.Append(sheet); //append the sheet to the sheets.
}
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
}
if (PrintTableHeader)
{
excel.Row headerRow = new excel.Row();
foreach (string column in columns)
{
excel.Cell cell = new excel.Cell
{
DataType = excel.CellValues.String,
CellValue = new excel.CellValue(GetColumnName(table.Columns[column]))
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
}
foreach (DataRow dsrow in table.Rows)
{
excel.Row newRow = new excel.Row();
foreach (String col in columns)
{
excel.Cell cell = new excel.Cell
{
DataType = excel.CellValues.String,
CellValue = new excel.CellValue(dsrow[col].ToString()) //
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
sheetPart.Worksheet.Save();
}
workbook.WorkbookPart.Workbook.Save();
workbook.Save();
workbook.Close();
}
break;
}
}
}
// ''' <summary>
// ''' Generates excel sheet on stream
// ''' </summary>
// ''' <param name="datTables">List of datatables to pass</param>
// ''' <param name="sheetNames">Sheetnames to use for each datatables</param>
// ''' <param name="lstColumnNames">Custom Column Header names.If used, specify column headers of all datatables, or specify upto needed datatable. For example if custom headers are needed for 3rd datatable- specify for 1st and 2nd too</param>
// ''' <param name="lstCustomIndexes">Custom Columns, use when all the datatable columns are not needed.Usage is same like Custom Column Names</param>
// ''' <param name="lstColumnType">Specify Column is string or number. it can automatically detect </param>
// ''' <param name="makeHeaderRowBold">Header row should be bold and centered</param>
// ''' <param name="alignCenterColumnIndexesZeroBased">Pass the indexes to be centered, usage like column names. For each datatable pass only required indices</param>
// ''' <returns></returns>
// ''' <remarks></remarks>
//Public Shared Function GetMultisheetExcelStream(datTables As List(Of DataTable), sheetNames As List(Of String), Optional lstColumnNames As List(Of List(Of String)) = Nothing, Optional lstCustomIndexes As List(Of List(Of Integer)) = Nothing, Optional lstColumnType As List(Of List(Of CellValues)) = Nothing, Optional makeHeaderRowBold As Boolean = True, Optional alignCenterColumnIndexesZeroBased As List(Of List(Of Integer)) = Nothing) As MemoryStream
// Dim strm As New MemoryStream
// Dim sheetDoc As SpreadsheetDocument = SpreadsheetDocument.Create(strm, SpreadsheetDocumentType.Workbook)
// ' Add a WorkbookPart to the document.
// 'Dim sheetId As Integer = 1
// Dim workbookpart As WorkbookPart
// Dim worksheetPart As WorksheetPart
// Dim ws As Worksheet
// Dim shetData As SheetData
// Dim sheets As Sheets
// Dim sheetId As Integer = 1
// Dim sheet As Sheet
// Dim headerRow As DocumentFormat.OpenXml.Spreadsheet.Row
// Dim cell As DocumentFormat.OpenXml.Spreadsheet.Cell
// Dim newRow As DocumentFormat.OpenXml.Spreadsheet.Row
// workbookpart = sheetDoc.AddWorkbookPart
// workbookpart.Workbook = New Workbook
// AddStyleSheet(sheetDoc)
// workbookpart.Workbook.Sheets = New Sheets()
// worksheetPart = workbookpart.AddNewPart(Of WorksheetPart)()
// shetData = New SheetData
// ws = New Worksheet(shetData)
// worksheetPart.Worksheet = ws
// sheets = sheetDoc.WorkbookPart.Workbook.GetFirstChild(Of Spreadsheet.Sheets)()
// 'sheetDoc.WorkbookPart.Workbook.GetFirstChild(Of Spreadsheet.Sheets)()
// Dim datTable As DataTable
// Dim columnNames As List(Of String) = Nothing
// Dim customIndexes As List(Of Integer) = Nothing
// Dim columnType As List(Of CellValues) = Nothing
// Dim alignCenterColumns As List(Of Integer) = Nothing
// Dim idx As Integer = 0
// For Each SheetName As String In sheetNames
// 'Get data from list
// datTable = datTables(idx)
// If lstCustomIndexes Is Nothing Then
// customIndexes = Nothing
// Else
// If lstCustomIndexes.Count > idx Then
// customIndexes = lstCustomIndexes(idx)
// End If
// End If
// If lstColumnNames Is Nothing Then
// columnNames = Nothing
// Else
// If lstColumnNames.Count > idx Then
// columnNames = lstColumnNames(idx)
// End If
// End If
// If lstColumnType Is Nothing Then
// columnType = Nothing
// Else
// If lstColumnType.Count > idx Then
// columnType = lstColumnType(idx)
// End If
// End If
// alignCenterColumns = Nothing
// If Not (alignCenterColumnIndexesZeroBased Is Nothing) Then
// If alignCenterColumnIndexesZeroBased.Count > idx Then
// alignCenterColumns = alignCenterColumnIndexesZeroBased(idx)
// End If
// End If
// idx = idx + 1
// 'prepare excel sheet
// If idx > 0 Then
// worksheetPart = workbookpart.AddNewPart(Of WorksheetPart)()
// shetData = New SheetData
// worksheetPart.Worksheet = New Worksheet(shetData)
// End If
// sheet = New Sheet
// sheet.Id = sheetDoc.WorkbookPart.GetIdOfPart(worksheetPart)
// sheet.SheetId = sheetId
// sheetId = sheetId + 1
// sheet.Name = SheetName
// sheets.Append(sheet)
// headerRow = New DocumentFormat.OpenXml.Spreadsheet.Row()
// '' Create Grid Header Row
// 'If Not columnNames Is Nothing Then
// ' If columnNames.Count <> datTable.Columns.Count Then columnNames = Nothing
// 'End If
// If columnNames Is Nothing Then
// columnNames = New List(Of String)()
// Dim clt As Boolean = False
// If columnType Is Nothing Then
// columnType = New List(Of CellValues)
// clt = True
// End If
// For Each column As System.Data.DataColumn In datTable.Columns
// columnNames.Add(column.ColumnName.Replace("_", " "))
// If clt Then
// columnType.Add(GetColumnType(column.DataType))
// End If
// Next
// End If
// '' top row columns
// For Each column As String In columnNames
// cell = New DocumentFormat.OpenXml.Spreadsheet.Cell()
// If makeHeaderRowBold Then
// cell.StyleIndex = STYLE_BOLD_CENTERED ' Convert.ToUInt32(1)
// End If
// cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
// cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(column)
// headerRow.AppendChild(cell)
// Next
// 'If columnNames Is Nothing Then
// ' columnNames = New List(Of String)()
// ' For Each column As System.Data.DataColumn In datTable.Columns
// ' ' columnNames.Add(column.ColumnName.Replace("_", " "))
// ' cell = New DocumentFormat.OpenXml.Spreadsheet.Cell()
// ' cell.DataType = GetColumnType(column.DataType)
// ' cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName.Replace("_", " "))
// ' headerRow.AppendChild(cell)
// ' Next
// 'Else
// ' For Each column As String In columnNames
// ' cell = New DocumentFormat.OpenXml.Spreadsheet.Cell()
// ' cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
// ' cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(column)
// ' headerRow.AppendChild(cell)
// ' Next
// 'End If
// 'Create DataRow
// If Not columnType Is Nothing Then
// If datTable.Columns.Count <> columnType.Count Then
// columnType = Nothing
// End If
// End If
// shetData.AppendChild(headerRow)
// If Not customIndexes Is Nothing Then
// Dim ix As Integer = 0
// For Each dsrow As System.Data.DataRow In datTable.Rows
// newRow = New Row()
// ix = 0 'col index
// For Each col As Integer In customIndexes
// cell = New Cell()
// If columnType Is Nothing Then
// cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
// Else
// cell.DataType = columnType(ix)
// End If
// If Not (alignCenterColumns Is Nothing) Then
// If alignCenterColumns.Contains(ix) Then
// cell.StyleIndex = STYLE_CENTERED
// End If
// End If
// ix = ix + 1 'increment col index
// cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow(col).ToString())
// newRow.AppendChild(cell)
// Next
// shetData.AppendChild(newRow)
// Next
// Else
// Dim maxc As Integer = datTable.Columns.Count - 1
// Dim ix As Integer = 0
// For Each dsrow As System.Data.DataRow In datTable.Rows
// newRow = New Row()
// ix = 0
// For col = 0 To maxc
// cell = New Cell()
// If columnType Is Nothing Then
// cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
// Else
// cell.DataType = columnType(ix)
// End If
// If Not (alignCenterColumns Is Nothing) Then
// If alignCenterColumns.Contains(ix) Then
// cell.StyleIndex = STYLE_CENTERED
// End If
// End If
// ix = ix + 1 'increment col index
// cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow(col).ToString())
// newRow.AppendChild(cell)
// Next
// shetData.AppendChild(newRow)
// Next
// End If
// workbookpart.Workbook.Save()
// Next 'end of looping
// ' workbookpart.Workbook.Save()
// ' Close the document.
// sheetDoc.Close()
// strm.Position = 0
// Return strm
//End Function
/// <summary>
/// Generates excel sheet on stream
/// </summary>
/// <param name="datTables">List of datatables to pass</param>
/// <param name="sheetNames">Sheetnames to use for each datatables</param>
/// <param name="lstColumnNames">Custom Column Header names.If used, specify column headers of all datatables, or specify upto needed datatable. For example if custom headers are needed for 3rd datatable- specify for 1st and 2nd too</param>
/// <param name="lstCustomIndexes">Custom Columns, use when all the datatable columns are not needed.Usage is same like Custom Column Names</param>
/// <param name="lstColumnType">Specify Column is string or number. it can automatically detect </param>
/// <param name="makeHeaderRowBold">Header row should be bold and centered</param>
/// <param name="alignCenterColumnIndexesZeroBased">Pass the indexes to be centered, usage like column names. For each datatable pass only required indices</param>
/// <returns></returns>
/// <remarks></remarks>
public static MemoryStream GetMultisheetExcelStream(List <DataTable> datTables, List <string> sheetNames, List <List <string> > lstColumnNames = null, List <List <int> > lstCustomIndexes = null, List <List <CellValues> > lstColumnType = null, bool makeHeaderRowBold = true, List <List <int> > alignCenterColumnIndexesZeroBased = null, List <List <List <string> > > dataBeforeEachTable = null, List <List <List <string> > > dataAfterEachTable = null)
{
MemoryStream strm = new MemoryStream();
SpreadsheetDocument sheetDoc = SpreadsheetDocument.Create(strm, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
//Dim sheetId As Integer = 1
WorkbookPart workbookpart = default(WorkbookPart);
WorksheetPart worksheetPart = default(WorksheetPart);
Worksheet ws = default(Worksheet);
SheetData shetData = default(SheetData);
Sheets sheets = default(Sheets);
UInt32Value sheetId = 1;
Sheet sheet = default(Sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = default(DocumentFormat.OpenXml.Spreadsheet.Row);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = default(DocumentFormat.OpenXml.Spreadsheet.Cell);
DocumentFormat.OpenXml.Spreadsheet.Row newRow = default(DocumentFormat.OpenXml.Spreadsheet.Row);
workbookpart = sheetDoc.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
AddStyleSheet(sheetDoc);
workbookpart.Workbook.Sheets = new Sheets();
worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
shetData = new SheetData();
ws = new Worksheet(shetData);
worksheetPart.Worksheet = ws;
sheets = sheetDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>();
//sheetDoc.WorkbookPart.Workbook.GetFirstChild(Of Spreadsheet.Sheets)()
DataTable datTable = default(DataTable);
List <string> columnNames = null;
List <int> customIndexes = null;
List <CellValues> columnType = null;
List <int> alignCenterColumns = null;
List <List <string> > dataAfterTable = null;
List <List <string> > dataBeforeTable = null;
bool columnNamesNotProvided = true;
bool columnTypeNotProvided = true;
int idx = 0;
foreach (string SheetName in sheetNames)
{
//Get data from list
datTable = datTables[idx];
customIndexes = null;
if ((lstCustomIndexes != null))
{
if (lstCustomIndexes.Count > idx)
{
customIndexes = lstCustomIndexes[idx];
}
}
columnNames = null;
columnNamesNotProvided = true;
if ((lstColumnNames != null))
{
if (lstColumnNames.Count > idx)
{
columnNames = lstColumnNames[idx];
if (columnNames != null)
{
columnNamesNotProvided = false;
}
}
}
columnType = null;
columnTypeNotProvided = true;
if ((lstColumnType != null))
{
if (lstColumnType.Count > idx)
{
columnType = lstColumnType[idx];
if (columnType != null)
{
columnTypeNotProvided = false;
}
}
}
alignCenterColumns = null;
if ((alignCenterColumnIndexesZeroBased != null))
{
if (alignCenterColumnIndexesZeroBased.Count > idx)
{
alignCenterColumns = alignCenterColumnIndexesZeroBased[idx];
}
}
dataAfterTable = null;
if ((dataAfterEachTable != null) && dataAfterEachTable.Count > idx)
{
dataAfterTable = dataAfterEachTable[idx];
}
dataBeforeTable = null;
if ((dataBeforeEachTable != null) && dataBeforeEachTable.Count > idx)
{
dataBeforeTable = dataBeforeEachTable[idx];
}
idx = idx + 1;
//prepare excel sheet
if (idx > 0)
{
worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
shetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(shetData);
}
sheet = new Sheet();
sheet.Id = sheetDoc.WorkbookPart.GetIdOfPart(worksheetPart);
sheet.SheetId = sheetId;
sheetId = sheetId + 1;
sheet.Name = SheetName;
sheets.Append(sheet);
// Append data before table - above heading
if ((dataBeforeTable != null))
{
//'append data before table
int ix = 0;
foreach (List <string> cusRow in dataBeforeTable)
{
if ((cusRow != null) && cusRow.Count > 0)
{
newRow = new Row();
ix = 0;
//col index
foreach (string col in cusRow)
{
cell = new Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
//If Not (alignCenterColumns Is Nothing) Then
// If alignCenterColumns.Contains(ix) Then
// cell.StyleIndex = STYLE_CENTERED
// End If
//End If
ix = ix + 1;
//increment col index
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(col);
newRow.AppendChild(cell);
}
shetData.AppendChild(newRow);
}
}
}
//if ((dataBeforeTable != null))
//{
// //'append data before table
// int ix = 0;
// foreach (List<string> cusRow in dataBeforeTable)
// {
// if ((cusRow != null) && cusRow.Count > 0)
// {
// newRow = new Row();
// ix = 0;
// //col index
// foreach (string col in cusRow)
// {
// cell = new Cell();
// cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
// //If Not (alignCenterColumns Is Nothing) Then
// // If alignCenterColumns.Contains(ix) Then
// // cell.StyleIndex = STYLE_CENTERED
// // End If
// //End If
// ix = ix + 1;
// //increment col index
// cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(col);
// newRow.AppendChild(cell);
// }
// shetData.AppendChild(newRow);
// }
// }
//}
headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
//' Create Grid Header Row
//If Not columnNames Is Nothing Then
// If columnNames.Count <> datTable.Columns.Count Then columnNames = Nothing
//End If
if (columnNames == null)
{
columnNames = new List <string>();
bool clt = false;
if (columnType == null)
{
columnType = new List <CellValues>();
clt = true;
}
foreach (System.Data.DataColumn column in datTable.Columns)
{
columnNames.Add(column.ColumnName.Replace("_", " "));
if (clt)
{
columnType.Add(GetColumnType(column.DataType));
}
}
}
//' top row columns
if (customIndexes != null && columnNamesNotProvided)
{
foreach (int clmn in customIndexes)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
if (makeHeaderRowBold)
{
cell.StyleIndex = STYLE_BOLD_CENTERED;
// Convert.ToUInt32(1)
}
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(columnNames[clmn]);
headerRow.AppendChild(cell);
}
}
else
{
foreach (string column in columnNames)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
if (makeHeaderRowBold)
{
cell.StyleIndex = STYLE_BOLD_CENTERED;
// Convert.ToUInt32(1)
}
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column);
headerRow.AppendChild(cell);
}
}
shetData.AppendChild(headerRow);
//If columnNames Is Nothing Then
// columnNames = New List(Of String)()
// For Each column As System.Data.DataColumn In datTable.Columns
// ' columnNames.Add(column.ColumnName.Replace("_", " "))
// cell = New DocumentFormat.OpenXml.Spreadsheet.Cell()
// cell.DataType = GetColumnType(column.DataType)
// cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName.Replace("_", " "))
// headerRow.AppendChild(cell)
// Next
//Else
// For Each column As String In columnNames
// cell = New DocumentFormat.OpenXml.Spreadsheet.Cell()
// cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
// cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(column)
// headerRow.AppendChild(cell)
// Next
//End If
//Create DataRow
if ((columnType != null))
{
if (customIndexes == null & datTable.Columns.Count != columnType.Count)
{
columnType = null;
}
else if (customIndexes != null && customIndexes.Count != columnType.Count)
{
columnType = null;
}
}
if (customIndexes != null)
{
int ix = 0;
foreach (System.Data.DataRow dsrow in datTable.Rows)
{
newRow = new Row();
ix = 0;
//col index
foreach (int col in customIndexes)
{
cell = new Cell();
if (columnType == null)
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
}
else
{
if (columnTypeNotProvided)
{
cell.DataType = columnType[col];
}
else
{
cell.DataType = columnType[ix];
}
}
if ((alignCenterColumns != null))
{
if (alignCenterColumns.Contains(col))
{
cell.StyleIndex = STYLE_CENTERED;
}
}
ix = ix + 1;
//increment col index
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
shetData.AppendChild(newRow);
}
}
else
{
int maxc = datTable.Columns.Count - 1;
int ix = 0;
foreach (System.Data.DataRow dsrow in datTable.Rows)
{
newRow = new Row();
ix = 0;
for (var col = 0; col <= maxc; col++)
{
cell = new Cell();
if (columnType == null)
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
}
else
{
cell.DataType = columnType[ix];
}
if ((alignCenterColumns != null))
{
if (alignCenterColumns.Contains(ix))
{
cell.StyleIndex = STYLE_CENTERED;
}
}
ix = ix + 1;
//increment col index
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
shetData.AppendChild(newRow);
}
}
if ((dataAfterTable != null))
{
//'append data after table
int ix = 0;
foreach (List <string> cusRow in dataAfterTable)
{
if ((cusRow != null) && cusRow.Count > 0)
{
newRow = new Row();
ix = 0;
//col index
foreach (string col in cusRow)
{
cell = new Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
//If Not (alignCenterColumns Is Nothing) Then
// If alignCenterColumns.Contains(ix) Then
// cell.StyleIndex = STYLE_CENTERED
// End If
//End If
ix = ix + 1;
//increment col index
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(col);
newRow.AppendChild(cell);
}
shetData.AppendChild(newRow);
}
}
}
workbookpart.Workbook.Save();
}
//end of looping
// workbookpart.Workbook.Save()
// Close the document.
sheetDoc.Close();
strm.Position = 0;
return(strm);
}
public static MemoryStream GetExcelStream(DataTable datTable, string sheetName, List <string> columnNames = null, List <int> customIndexes = null, List <CellValues> columnType = null)
{
MemoryStream strm = new MemoryStream();
SpreadsheetDocument sheetDoc = SpreadsheetDocument.Create(strm, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
//Dim sheetId As Integer = 1
WorkbookPart workbookpart = default(WorkbookPart);
WorksheetPart worksheetPart = default(WorksheetPart);
Worksheet ws = default(Worksheet);
SheetData shetData = default(SheetData);
Sheets sheets = default(Sheets);
Sheet sheet = default(Sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = default(DocumentFormat.OpenXml.Spreadsheet.Row);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = default(DocumentFormat.OpenXml.Spreadsheet.Cell);
DocumentFormat.OpenXml.Spreadsheet.Row newRow = default(DocumentFormat.OpenXml.Spreadsheet.Row);
workbookpart = sheetDoc.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
workbookpart.Workbook.Sheets = new Sheets();
worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
shetData = new SheetData();
ws = new Worksheet(shetData);
worksheetPart.Worksheet = ws;
sheets = sheetDoc.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
//sheetDoc.WorkbookPart.Workbook.GetFirstChild(Of Spreadsheet.Sheets)()
sheet = new Sheet();
sheet.Id = sheetDoc.WorkbookPart.GetIdOfPart(worksheetPart);
sheet.SheetId = 1;
sheet.Name = sheetName;
sheets.Append(sheet);
headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
//' Create Grid Header Row
//If Not columnNames Is Nothing Then
// If columnNames.Count <> datTable.Columns.Count Then columnNames = Nothing
//End If
if (columnNames == null)
{
columnNames = new List <string>();
foreach (System.Data.DataColumn column in datTable.Columns)
{
columnNames.Add(column.ColumnName.Replace("_", " "));
}
}
foreach (string column in columnNames)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column);
headerRow.AppendChild(cell);
}
//Create DataRow
if ((columnType != null))
{
if (datTable.Columns.Count != columnType.Count)
{
columnType = null;
}
}
shetData.AppendChild(headerRow);
if ((customIndexes != null))
{
int ix = 0;
foreach (System.Data.DataRow dsrow in datTable.Rows)
{
newRow = new Row();
ix = 0;
foreach (int col in customIndexes)
{
cell = new Cell();
if (columnType == null)
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
}
else
{
cell.DataType = columnType[ix];
ix = ix + 1;
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
shetData.AppendChild(newRow);
}
}
else
{
int maxc = datTable.Columns.Count - 1;
int ix = 0;
foreach (System.Data.DataRow dsrow in datTable.Rows)
{
newRow = new Row();
ix = 0;
for (int col = 0; col <= maxc; col++)
{
cell = new Cell();
if (columnType == null)
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
}
else
{
cell.DataType = columnType[ix];
ix = ix + 1;
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
shetData.AppendChild(newRow);
}
}
workbookpart.Workbook.Save();
// Close the document.
sheetDoc.Close();
strm.Position = 0;
return(strm);
}
private void CreateExcelFile(DataTable table, string destination)
{
// hfFileName.Value = destination;
// lblFileName.Text = string.Empty;
using (var workbook = SpreadsheetDocument.Create(destination, 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();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
//}
}
// btnDownloadExcel.Visible = true;
// lblFileName.Text = "Servicing file created successfully";
}
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 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 async override Task ExecuteResultAsync(ActionContext context)
{
var response = context.HttpContext.Request;
var enumerable = _data as System.Collections.IEnumerable;
if (enumerable == null)
{
throw new ArgumentException("IEnumerable type required");
}
byte[] FileContents = null;
using (MemoryStream mem = new MemoryStream()) {
using (var workbook = SpreadsheetDocument.Create(mem, 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 = "Sheet1"
};
sheets.Append(sheet);
//add logo
string imgPath = "assets/logo.png";
var drawingsPart = sheetPart.AddNewPart <DrawingsPart> ();
if (!sheetPart.Worksheet.ChildElements.OfType <Drawing> ().Any())
{
sheetPart.Worksheet.Append(new Drawing {
Id = sheetPart.GetIdOfPart(drawingsPart)
});
}
if (drawingsPart.WorksheetDrawing == null)
{
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
}
var worksheetDrawing = drawingsPart.WorksheetDrawing;
var imagePart = drawingsPart.AddImagePart(ImagePartType.Png);
using (var stream = new FileStream(imgPath, FileMode.Open)) {
imagePart.FeedData(stream);
}
Bitmap bm = new Bitmap(imgPath);
DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
var extentsCx = (long)bm.Width * (long)((float)31440 / bm.HorizontalResolution);
var extentsCy = (long)bm.Height * (long)((float)31440 / bm.VerticalResolution);
bm.Dispose();
var colOffset = 2;
var rowOffset = 0;
int colNumber = 4;
int rowNumber = 1;
var nvps = worksheetDrawing.Descendants <DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties> ();
var nvpId = nvps.Count() > 0 ?
(UInt32Value)worksheetDrawing.Descendants <DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties> ().Max(p => p.Id.Value) + 1 :
1U;
var oneCellAnchor = new DocumentFormat.OpenXml.Drawing.Spreadsheet.OneCellAnchor(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker {
ColumnId = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ColumnId((colNumber - 1).ToString()),
RowId = new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowId((rowNumber - 1).ToString()),
ColumnOffset = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ColumnOffset(colOffset.ToString()),
RowOffset = new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowOffset(rowOffset.ToString())
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.Extent {
Cx = extentsCx, Cy = extentsCy
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties {
Id = nvpId, Name = "Picture " + nvpId, Description = imgPath
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties(new DocumentFormat.OpenXml.Drawing.PictureLocks {
NoChangeAspect = true
})
),
new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill(
new DocumentFormat.OpenXml.Drawing.Blip {
Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print
},
new DocumentFormat.OpenXml.Drawing.Stretch(new DocumentFormat.OpenXml.Drawing.FillRectangle())
),
new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties(
new DocumentFormat.OpenXml.Drawing.Transform2D(
new DocumentFormat.OpenXml.Drawing.Offset {
X = 0, Y = 0
},
new DocumentFormat.OpenXml.Drawing.Extents {
Cx = extentsCx, Cy = extentsCy
}
),
new DocumentFormat.OpenXml.Drawing.PresetGeometry {
Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle
}
)
),
new DocumentFormat.OpenXml.Drawing.Spreadsheet.ClientData()
);
worksheetDrawing.Append(oneCellAnchor);
sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
//company name
DocumentFormat.OpenXml.Spreadsheet.Row company = new DocumentFormat.OpenXml.Spreadsheet.Row();
DocumentFormat.OpenXml.Spreadsheet.Cell cellCompany = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
StyleIndex = (UInt32Value)1U
};
cellCompany.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cellCompany.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("FOOD, MEDICINE AND HEALTH CARE ADMINISTRATION AND CONTROL AUTHORITY OF ETHIOPIA");
company.AppendChild(cellCompany);
sheetData.AppendChild(company);
sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
//header
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (var column in _columns)
{
if (!(bool)column["IsVisible"])
{
continue;
}
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["Title"].ToString());
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (var item in enumerable)
{
IDictionary <string, object> row = (IDictionary <string, object>)item;
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (var header in _columns)
{
if (!(bool)header["IsVisible"])
{
continue;
}
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
var value = row[header["FieldName"].ToString()];
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value?.ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
//geenrated by
sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
DocumentFormat.OpenXml.Spreadsheet.Row footer = new DocumentFormat.OpenXml.Spreadsheet.Row();
DocumentFormat.OpenXml.Spreadsheet.Cell footerCell = new DocumentFormat.OpenXml.Spreadsheet.Cell(new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowOffset("4"));
footerCell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
footerCell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("Generated By eRIS");
footer.AppendChild(footerCell);
sheetData.AppendChild(footer);
sheetPart.Worksheet.Save();
workbook.WorkbookPart.Workbook.Save();
workbook.Close();
FileContents = mem.ToArray();
var contentResult = new FileContentResult(FileContents, this.ContentType);
await contentResult.ExecuteResultAsync(context);
//await response.Body.WriteAsync(FileContents, 0, FileContents.Length);
}
}
}
// 08/25/2012 Change Excel export type to use Open XML as the previous format is not supported on Office 2010.
public static void ExportExcelOpenXMLByCustomer(Stream stmResponse, DataView vw, string sModuleName, int nStartRecord, int nEndRecord)
{
// http://msdn.microsoft.com/en-us/library/office/ff478153.aspx
// http://msdn.microsoft.com/en-us/library/office/cc850837
using (MemoryStream stm = new MemoryStream())
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stm, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
worksheetPart.Worksheet.Save();
// http://www.codeproject.com/Articles/371203/Creating-basic-Excel-workbook-with-Open-XML
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
workbookStylesPart.Stylesheet = OpenXML_CreateStylesheet();
workbookStylesPart.Stylesheet.Save();
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sModuleName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
DataTable tbl = vw.Table;
L10N L10n = HttpContext.Current.Items["L10n"] as L10N;
SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
UInt32Value numberStyleId = OpenXML_CreateCellFormat(workbookStylesPart.Stylesheet, null, null, UInt32Value.FromUInt32(3));
UInt32Value doubleStyleId = OpenXML_CreateCellFormat(workbookStylesPart.Stylesheet, null, null, UInt32Value.FromUInt32(4));
UInt32Value dateStyleId = OpenXML_CreateCellFormat(workbookStylesPart.Stylesheet, null, null, UInt32Value.FromUInt32(14));
int rowIndex = 1;
Dictionary<string, int> dictStringToInt = new Dictionary<string, int>();
DocumentFormat.OpenXml.Spreadsheet.Cell cell = null;
DocumentFormat.OpenXml.Spreadsheet.Row xRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
xRow.RowIndex = (uint)rowIndex;
sheetData.Append(xRow);
for (int nColumn = 0; nColumn < tbl.Columns.Count; nColumn++)
{
DataColumn col = tbl.Columns[nColumn];
// 11/29/2013 Use dictionary to increase performance.
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, col.ColumnName.ToLower(), dictStringToInt);
xRow.AppendChild(cell);
}
rowIndex++;
// 12/02/2013 Add a blank string to the shared array so that there is at least one.
OpenXML_InsertSharedStringItem(shareStringPart, String.Empty, dictStringToInt);
for (int i = nStartRecord; i < nEndRecord; i++, rowIndex++)
{
xRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
xRow.RowIndex = (uint)rowIndex;
sheetData.Append(xRow);
DataRowView row = vw[i];
for (int nColumn = 0; nColumn < tbl.Columns.Count; nColumn++)
{
DataColumn col = tbl.Columns[nColumn];
if (row[nColumn] != DBNull.Value)
{
switch (col.DataType.FullName)
{
case "System.Boolean":
//xw.WriteAttributeString("ss:Type", "String");
cell = OpenXML_CreateBoolean(nColumn + 1, rowIndex, Sql.ToBoolean(row[nColumn]) ? "1" : "0");
xRow.AppendChild(cell);
break;
case "System.Single":
//xw.WriteAttributeString("ss:Type", "Number");
cell = OpenXML_CreateNumber(nColumn + 1, rowIndex, Sql.ToDouble(row[nColumn]).ToString(), doubleStyleId);
xRow.AppendChild(cell);
break;
case "System.Double":
//xw.WriteAttributeString("ss:Type", "Number");
cell = OpenXML_CreateNumber(nColumn + 1, rowIndex, Sql.ToDouble(row[nColumn]).ToString(), doubleStyleId);
xRow.AppendChild(cell);
break;
case "System.Int16":
//xw.WriteAttributeString("ss:Type", "Number");
cell = OpenXML_CreateNumber(nColumn + 1, rowIndex, Sql.ToInteger(row[nColumn]).ToString(), numberStyleId);
xRow.AppendChild(cell);
break;
case "System.Int32":
//xw.WriteAttributeString("ss:Type", "Number");
cell = OpenXML_CreateNumber(nColumn + 1, rowIndex, Sql.ToInteger(row[nColumn]).ToString(), numberStyleId);
xRow.AppendChild(cell);
break;
case "System.Int64":
//xw.WriteAttributeString("ss:Type", "Number");
cell = OpenXML_CreateNumber(nColumn + 1, rowIndex, Sql.ToLong(row[nColumn]).ToString(), numberStyleId);
xRow.AppendChild(cell);
break;
case "System.Decimal":
//xw.WriteAttributeString("ss:Type", "Number");
cell = OpenXML_CreateNumber(nColumn + 1, rowIndex, Sql.ToDecimal(row[nColumn]).ToString(), doubleStyleId);
xRow.AppendChild(cell);
break;
case "System.DateTime":
//xw.WriteAttributeString("ss:Type", "DateTime");
cell = OpenXML_CreateDate(nColumn + 1, rowIndex, Sql.ToDateTime(row[nColumn]), dateStyleId);
xRow.AppendChild(cell);
break;
case "System.Guid":
//xw.WriteAttributeString("ss:Type", "String");
// 11/29/2013 Use dictionary to increase performance.
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, Sql.ToGuid(row[nColumn]).ToString().ToUpper(), dictStringToInt);
xRow.AppendChild(cell);
break;
case "System.String":
//xw.WriteAttributeString("ss:Type", "String");
// 11/29/2013 Catch and ignore bad data exceptions. This can happen with imported unicode data.
// '', hexadecimal value 0x13, is an invalid character.
try
{
// 11/29/2013 Use dictionary to increase performance.
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, Sql.ToString(row[nColumn]), dictStringToInt);
}
catch
{
// 11/29/2013 After exception, the item still remains in the list and causes future save operations to fail.
// 11/29/2013 Use dictionary to increase performance.
OpenXML_RemoveText(shareStringPart, Sql.ToString(row[nColumn]), dictStringToInt);
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, String.Empty, dictStringToInt);
}
xRow.AppendChild(cell);
break;
case "System.Byte[]":
{
//xw.WriteAttributeString("ss:Type", "String");
//byte[] buffer = Sql.ToByteArray((System.Array) row[nColumn]);
//xw.WriteBase64(buffer, 0, buffer.Length);
// 11/29/2013 Use dictionary to increase performance.
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, String.Empty, dictStringToInt);
xRow.AppendChild(cell);
break;
}
default:
// throw(new Exception("Unsupported field type: " + rdr.GetFieldType(nColumn).FullName));
// 08/25/2012 We need to write the type even for empty cells.
//xw.WriteAttributeString("ss:Type", "String");
// 11/29/2013 Use dictionary to increase performance.
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, String.Empty, dictStringToInt);
xRow.AppendChild(cell);
break;
}
}
else
{
// 08/25/2012 We need to write the type even for empty cells.
// 11/29/2013 Use dictionary to increase performance.
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, String.Empty, dictStringToInt);
xRow.AppendChild(cell);
}
}
}
workbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
stm.WriteTo(stmResponse);
}
}
private void Export()
{
if (ResultModel != null)
{
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Title = "Save Result";
saveDialog.Filter = "Excel 97-2003 WorkBook|*.xls|Excel WorkBook|*.xlsx|All Excel Files|*.xls;*.xlsx|All Files|*.*";
if ((bool)saveDialog.ShowDialog())
{
using (var workbook = SpreadsheetDocument.Create(saveDialog.FileName, 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 table = ResultModel;
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();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
MessageBox.Show("File Saved Successfully");
}
}
}
}//экспорт в XLSX
private void ExportDataSet(DataTable table, string destination)
{
using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
//режем тэйбл от мусора
table.Columns.Remove("Код");
table.Columns.Remove("ID");
//создание workbook+sheets
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
//собственный формат для даты
//styles
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId3");
Stylesheet stylesheet = new Stylesheet();
// Date Time Display Format when s="1" is applied to cell
NumberingFormats numberingFormats = new NumberingFormats()
{
Count = (UInt32Value)1U
};
NumberingFormat numberingFormat = new NumberingFormat()
{
NumberFormatId = (UInt32Value)164U, FormatCode = "dd.MM.yyyy"
};
numberingFormats.Append(numberingFormat);
// Cell font
Fonts fonts = new Fonts()
{
Count = (UInt32Value)1U
};
DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontSize fontSize = new FontSize()
{
Val = 11D
};
FontName fontName = new FontName()
{
Val = "Calibri"
};
font.Append(fontSize);
font.Append(fontName);
fonts.Append(font);
// empty, but mandatory
Fills fills = new Fills()
{
Count = (UInt32Value)1U
};
Fill fill = new Fill();
fills.Append(fill);
Borders borders = new Borders()
{
Count = (UInt32Value)1U
};
Border border = new Border();
borders.Append(border);
// cellFormat1 for text cell cellFormat2 for Datetime cell
CellFormats cellFormats = new CellFormats()
{
Count = (UInt32Value)2U
};
CellFormat cellFormat1 = new CellFormat()
{
FontId = (UInt32Value)0U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)164U, FontId = (UInt32Value)0U, ApplyNumberFormat = true
};
cellFormats.Append(cellFormat1);
cellFormats.Append(cellFormat2);
// Save as styles
stylesheet.Append(numberingFormats);
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellFormats);
workbookStylesPart.Stylesheet = stylesheet;
{
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 = "Отчет"
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
//создаем header в xlsx
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);
//Перенос из datatable
foreach (System.Data.DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
//проверка типа входных данных
Type dtype = dsrow[col].GetType();
switch (dtype.Name.ToString())
{
case "DateTime":
DateTime dt = Convert.ToDateTime(dsrow[col].ToString());
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dt.ToOADate().ToString());
cell.StyleIndex = 1;
break;
case "String":
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
break;
case "Int32":
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
break;
default:
break;
}
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
}//вывод dataTable в xlsx с учетом типа данных
/// <summary>
/// 创建excel,并且把dataTable导入到excel中
/// </summary>
/// <param name="destination">保存路径</param>
/// <param name="dataTables">数据源</param>
/// <param name="sheetNames">excel中sheet的名称</param>
public void CreateExcel(string destination, DataTable[] dataTables, string[] sheetNames = null)
{
using (var workbook = SpreadsheetDocument.Create(destination, 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();
uint sheetId = 1;
foreach (DataTable table in dataTables)
{
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
sheetPart.Worksheet = new Worksheet();
Columns headColumns = CrateColunms(table);
sheetPart.Worksheet.Append(headColumns);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = string.Empty;
if (sheetNames != null)
{
if (sheetNames.Length >= sheetId)
{
sheetName = sheetNames[sheetId - 1].ToString();
}
}
else
{
sheetName = table.TableName ?? sheetId.ToString();
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
foreach (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.StyleIndex = 11;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.StyleIndex = 10;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
sheetPart.Worksheet.Append(sheetData);
}
workbook.Close();
}
}
// Creates Excel sheet with exisiting Template
public void PopulateDataTableToExcelTemplate(string FilePath, DataTable dt)
{
try
{
// Will not overwrite if the destination file already exists.
string SourcePath = System.Web.HttpContext.Current.Server.MapPath("~/Content/Dashboards/ExcelPackageTemplate.xlsx");
File.Copy(SourcePath, FilePath);
Thread.Sleep(5000);
}
// Catch exception if the file was already copied.
catch (IOException copyError)
{
Console.WriteLine(copyError.Message);
}
using (SpreadsheetDocument ssd = SpreadsheetDocument.Open(FilePath, true))
{
WorkbookPart wbPart = ssd.WorkbookPart;
WorksheetPart worksheetPart = wbPart.WorksheetParts.First();
SheetData sheetdata = worksheetPart.Worksheet.GetFirstChild <SheetData>();
string[] headerColumns = new string[] { dt.Columns[0].ColumnName, dt.Columns[1].ColumnName, dt.Columns[2].ColumnName };
DocumentFormat.OpenXml.Spreadsheet.Row r = new DocumentFormat.OpenXml.Spreadsheet.Row();
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
int RowIndexer = 1;
int ColumnIndexer = 1;
r.RowIndex = (UInt32)RowIndexer;
foreach (DataColumn dc in dt.Columns)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.CellReference = ColumnName(ColumnIndexer) + RowIndexer;
cell.DataType = CellValues.InlineString;
cell.InlineString = new InlineString(new Text(dc.ColumnName.ToString()));
// consider using cell.CellValue. Then you don't need to use InlineString.
// Because it seems you're not using any rich text so you're just bloating up
// the XML.
r.AppendChild(cell);
ColumnIndexer++;
}
// here's the missing part you needed
sheetdata.Append(r);
RowIndexer = 2;
foreach (DataRow dr in dt.Rows)
{
r = new DocumentFormat.OpenXml.Spreadsheet.Row();
r.RowIndex = (UInt32)RowIndexer;
// this follows the same starting column index as your column header.
// I'm assuming you start with column 1. Change as you see fit.
ColumnIndexer = 1;
foreach (object value in dr.ItemArray)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
// I moved it here so it's consistent with the above part
// Also, the original code was using the row index to calculate
// the column name, which is weird.
cell.CellReference = ColumnName(ColumnIndexer) + RowIndexer;
cell.DataType = CellValues.InlineString;
cell.InlineString = new InlineString(new Text(value.ToString()));
r.AppendChild(cell);
ColumnIndexer++;
}
RowIndexer++;
// missing part
sheetdata.Append(r);
}
worksheetPart.Worksheet.Save();
wbPart.Workbook.Save();
}
}
public static String ExportHportIDXToOpenXMLCSV(GridView gv, string fileName, string sheetName = "")
{
string filePath = HttpContext.Current.Server.MapPath(fileName + sheetName.ToString() + ".xlsx");
StringBuilder sbCSVString = new StringBuilder();
try
{
using (var workbook = SpreadsheetDocument.Create(filePath, 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();
sheet.Id = relationshipId;
sheet.SheetId = sheetId;
if (sheetName != "")
{
sheet.Name = sheetName;
}
else
{
sheet.Name = fileName;
}
WorksheetPart newWorksheetPart;
newWorksheetPart = sheetPart.Worksheet.WorksheetPart;
SheetProperties sp = new SheetProperties(new PageSetupProperties());
sheetPart.Worksheet.SheetProperties = sp;
sheetPart.Worksheet.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(true);
DocumentFormat.OpenXml.Spreadsheet.PageSetup pgOr = new DocumentFormat.OpenXml.Spreadsheet.PageSetup();
pgOr.Orientation = DocumentFormat.OpenXml.Spreadsheet.OrientationValues.Portrait;
pgOr.FitToHeight = 3;
pgOr.FitToWidth = 1;
sheetPart.Worksheet.AppendChild(pgOr);
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
int rowCounter = 0;
int columnCounter = 0;
DocumentFormat.OpenXml.Spreadsheet.Row columnRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (DataColumn col in ((DataTable)gv.DataSource).Columns)
{
String cellValue = col.ColumnName;
DocumentFormat.OpenXml.Spreadsheet.Cell cellCol = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cellCol.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cellCol.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
columnRow.AppendChild(cellCol);
sbCSVString.Append(cellValue);
columnCounter++;
if (columnCounter < ((DataTable)gv.DataSource).Columns.Count)
{
sbCSVString.Append(",");
}
}
sheetData.AppendChild(columnRow);
sbCSVString.Append(Environment.NewLine);
foreach (GridViewRow dataSourceRow in gv.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newSheetRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
//DataRow dataSourceRow = rowView.Row;
string cellValue = dataSourceRow.Cells[0].Text.ToString();
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
newSheetRow.AppendChild(cell);
sbCSVString.Append(cellValue);
sbCSVString.Append(",");
cellValue = dataSourceRow.Cells[1].Text.ToString();
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
newSheetRow.AppendChild(cell);
sbCSVString.Append(cellValue);
sbCSVString.Append(",");
cellValue = dataSourceRow.Cells[2].Text.ToString();
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
newSheetRow.AppendChild(cell);
sbCSVString.Append(cellValue);
sbCSVString.Append(",");
cellValue = dataSourceRow.Cells[3].Text.ToString();
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
newSheetRow.AppendChild(cell);
sbCSVString.Append(cellValue);
sbCSVString.Append(",");
cellValue = dataSourceRow.Cells[4].Text.ToString();
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
newSheetRow.AppendChild(cell);
sbCSVString.Append(cellValue);
sbCSVString.Append(",");
cellValue = dataSourceRow.Cells[5].Text.ToString();
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
newSheetRow.AppendChild(cell);
sbCSVString.Append(cellValue);
sheetData.AppendChild(newSheetRow);
sbCSVString.Append(Environment.NewLine);
rowCounter = rowCounter + 1;
}
}
File.WriteAllText(filePath.Replace("xlsx", "csv"), sbCSVString.ToString());
}
catch (System.Exception ex)
{
string sError;
sError = ex.ToString();
throw ex;
}
return(filePath);
}
// Generates content of worksheetPart1.
private void GenerateWorksheetPart1Content <T>(WorksheetPart worksheetPart1, IEnumerable <T> data)
{
Worksheet worksheet1 = new Worksheet();
SheetData sheetData1 = new SheetData();
var props = (typeof(T)).GetProperties().Where(f =>
!f.IsDefined(typeof(ScaffoldColumnAttribute), true) || ((ScaffoldColumnAttribute)f.GetCustomAttributes(typeof(ScaffoldColumnAttribute), true).First()).Scaffold == true
).OrderBy(f => {
if (f.IsDefined(typeof(DisplayAttribute), true))
{
return(((DisplayAttribute)f.GetCustomAttributes(typeof(DisplayAttribute), true).First()).GetOrder());
}
else
{
return(default(int));
}
});
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
UInt32Value rowIndex = 1;
headerRow.RowIndex = rowIndex++;
List <String> columns = new List <string>();
foreach (var prop in props)
{
var headerName = prop.Name;
if (prop.IsDefined(typeof(DisplayNameAttribute), true))
{
headerName = ((DisplayNameAttribute)prop.GetCustomAttributes(typeof(DisplayNameAttribute), true).First()).DisplayName;
}
if (prop.IsDefined(typeof(DisplayAttribute), true))
{
var da = ((DisplayAttribute)prop.GetCustomAttributes(typeof(DisplayAttribute), true).First());
headerName = da.GetShortName() ?? da.GetName() ?? prop.Name;
}
columns.Add(prop.Name);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = headerRow.Create();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(headerName);
headerRow.AppendChild(cell);
}
sheetData1.AppendChild(headerRow);
foreach (var item in data)
{
int cellIndex = 0;
var row = sheetData1.Create();
foreach (var prop in props)
{
var val = prop.GetValue(item, null);
string str = null;
if (val == null)
{
str = null;
}
else if ((prop.PropertyType == typeof(DateTime) || prop.PropertyType == typeof(Nullable <DateTime>)) && prop.IsDefined(typeof(DisplayFormatAttribute), true))
{
var df = ((DisplayFormatAttribute)prop.GetCustomAttributes(typeof(DisplayFormatAttribute), true).First());
if (df != null)
{
var date = (val as DateTime?).Value;
str = date.ToString(df.DataFormatString);
}
}
else
{
str = val.ToString();
}
var cell = new Cell()
{
CellReference = XlsxExtensions.Column(cellIndex++) + rowIndex.ToString()
};
if (prop.PropertyType == typeof(int) || prop.PropertyType == typeof(Nullable <int>) ||
prop.PropertyType == typeof(decimal) || prop.PropertyType == typeof(Nullable <decimal>))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
}
else
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(str);
row.AppendChild(cell);
}
sheetData1.AppendChild(row);
rowIndex++;
}
worksheet1.Append(sheetData1);
worksheetPart1.Worksheet = worksheet1;
}
public void generarReporteGenerico(DataSet dsDatosReporte, string nombreReporte)
{
string directorio = @"C:\Reportes\";
string pathArchivo = directorio + nombreReporte + ".xlsx";
if (!Directory.Exists(directorio))
{
Directory.CreateDirectory(directorio);
}
try
{
var workbook = SpreadsheetDocument.Create((pathArchivo), 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 dsDatosReporte.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();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
workbook.WorkbookPart.Workbook.Save();
workbook.Close();
}
}
catch (Exception)
{
throw;
}
System.Diagnostics.Process.Start(pathArchivo);
}
//------ NumberFormatId upto 164 is "In-Built"---------------//
//public void ExportDataSet(DataSet ds, string destination, string fileName)
//{
// try
// {
// if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
// {
// using (var workbook = SpreadsheetDocument.Create(destination, 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();
// WorkbookStylesPart stylesPart = workbook.WorkbookPart.AddNewPart<WorkbookStylesPart>();
// stylesPart.Stylesheet = GenerateStyleSheet();
// stylesPart.Stylesheet.Save();
// 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);
// cell.StyleIndex = 3;
// headerRow.AppendChild(cell);
// }
// sheetData.AppendChild(headerRow);
// foreach (System.Data.DataRow dsrow in table.Rows)
// {
// DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
// foreach (String col in columns)
// {
// DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
// cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
// cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
// newRow.AppendChild(cell);
// }
// sheetData.AppendChild(newRow);
// }
// }
// }
// downloadfile(destination, fileName);
// }
// }
// catch (Exception ex)
// {
// }
//}
/// <summary>
///
/// </summary>
/// <param name="dsHeader">Custom Header DataSet</param>
/// <param name="ds">Actual dataset with values</param>
/// <param name="customHeader">True: Use custom header dataset, False: used table header</param>
/// <param name="destination">File location with name</param>
public void ExportDataSet(DataSet dsHeader, DataSet ds, bool customHeader, string filename, string exportFileName)
{
string dbFilePath = "/DownloadExcel/";
string strFileName = GenerateFileName(filename) + "xlsx";
if (!Directory.Exists(System.Web.HttpContext.Current.Server.MapPath("~" + dbFilePath)))
{
Directory.CreateDirectory(System.Web.HttpContext.Current.Server.MapPath("~" + dbFilePath));
}
string destination = System.Web.HttpContext.Current.Server.MapPath("~" + dbFilePath) + strFileName;
try
{
if ((dsHeader.Tables.Count == ds.Tables.Count && customHeader) || !customHeader)
{
List <KeyValuePair <string, string> > mergecellsdictionary = new List <KeyValuePair <string, string> >();
using (var workbook = SpreadsheetDocument.Create(destination, 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();
WorkbookStylesPart stylesPart = workbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
for (int i = 0; i < ds.Tables.Count; i++)
{
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 = ds.Tables[i].TableName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
if (customHeader)
{
foreach (System.Data.DataColumn column in dsHeader.Tables[i].Columns)
{
columns.Add(column.ColumnName);
}
int rIndex = 1;
foreach (System.Data.DataRow dsrow in dsHeader.Tables[i].Rows)
{
headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
string[] strval = Convert.ToString(dsrow[col]).Split(',');
if (strval.Length == 4)
{
// Check for rowspan
if (!string.IsNullOrEmpty(strval[2]) && strval[2].All(Char.IsDigit))
{
if (Convert.ToInt32(strval[2]) > 1)
{
int rpos = (columns.IndexOf(col)) + 1;
mergecellsdictionary.Add(new KeyValuePair <string, string>(sheet.Name, getColumnNameFromIndex(rpos) + rIndex.ToString() + "," + getColumnNameFromIndex(rpos) + (rIndex + Convert.ToInt32(strval[2]) - 1).ToString()));
}
}
// Check for colspan
if (!string.IsNullOrEmpty(strval[3]) && strval[3].All(Char.IsDigit))
{
if (Convert.ToInt32(strval[3]) > 1)
{
int cpos = (columns.IndexOf(col)) + 1;
mergecellsdictionary.Add(new KeyValuePair <string, string>(sheet.Name, getColumnNameFromIndex(cpos) + rIndex.ToString() + "," + getColumnNameFromIndex(cpos + Convert.ToInt32(strval[3]) - 1) + rIndex.ToString()));
}
}
}
else if (strval.Length == 1)
{
string str1 = strval[0];
strval = new string[] { str1, "1" };
}
int indexNum = (strval[1].All(Char.IsDigit)) ? Convert.ToInt32(strval[1]) : 1;
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(strval[0]);
cell.StyleIndex = (UInt32)indexNum;
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
rIndex = rIndex + 1;
}
}
else
{
foreach (System.Data.DataColumn column in ds.Tables[i].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);
cell.StyleIndex = 1; // The light gray Fill
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
}
foreach (System.Data.DataRow dsrow in ds.Tables[i].Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
//cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
if (IsDouble(dsrow[col].ToString()))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
}
else
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
if (mergecellsdictionary.Count > 0)
{
foreach (KeyValuePair <string, string> entry in mergecellsdictionary)
{
string[] strcells = entry.Value.Split(',');
mergeCell(destination, entry.Key, strcells[0], strcells[1]);
}
}
downloadfile(destination, exportFileName);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (System.IO.File.Exists(destination))
{
System.IO.File.Delete(destination);
}
}
}
public async Task <bool> Export(IEnumerable <T> _list)
{
bool exported = false;
string className = typeof(T).Name;
this.fileName = string.Concat(System.IO.Path.GetFileNameWithoutExtension(this.fileName), "_", className.ToUpper(), System.IO.Path.GetExtension(this.fileName));
bool IsFileAvailable = await CheckIfFileCanBeUsed(this.fileName);
if (IsFileAvailable)
{
PropertyInfo[] properties = typeof(T).GetProperties();
string sheetName = "SHEET " + className.ToUpper();
string randomPath = this.RandomPath;
this.oldPath = randomPath;
using (var workbook = SpreadsheetDocument.Create(randomPath, 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();
WorkbookStylesPart workStylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
workStylePart.Stylesheet = GenerateStyleSheet();
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 = sheetName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
headerRow.Height = new DoubleValue()
{
Value = 32
};
foreach (PropertyInfo propertyInfo in properties)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(propertyInfo.Name.ToUpper());
cell.StyleIndex = 1;
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (T _obj in _list)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (PropertyInfo propertyInfo in properties)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
object propertyValue = propertyInfo.GetValue(_obj, null);
string cellValue = (propertyValue != null ? propertyValue.ToString() : "");
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
cell.StyleIndex = 0;
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbook.Save();
exported = true;
}
}
return(exported);
}
public static MemoryStream GetExcelStream(DataTable datTable, string sheetName, bool hasHeaderRow = true, List <string> columnNames = null, List <int> customIndexes = null, List <CellValues> columnType = null)
{
MemoryStream strm = new MemoryStream();
SpreadsheetDocument sheetDoc = SpreadsheetDocument.Create(strm, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
//Dim sheetId As Integer = 1
WorkbookPart workbookpart = default(WorkbookPart);
WorksheetPart worksheetPart = default(WorksheetPart);
Worksheet ws = default(Worksheet);
SheetData shetData = default(SheetData);
Sheets sheets = default(Sheets);
Sheet sheet = default(Sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = default(DocumentFormat.OpenXml.Spreadsheet.Row);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = default(DocumentFormat.OpenXml.Spreadsheet.Cell);
DocumentFormat.OpenXml.Spreadsheet.Row newRow = default(DocumentFormat.OpenXml.Spreadsheet.Row);
workbookpart = sheetDoc.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
workbookpart.Workbook.Sheets = new Sheets();
worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
shetData = new SheetData();
ws = new Worksheet(shetData);
worksheetPart.Worksheet = ws;
sheets = sheetDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>();
//sheetDoc.WorkbookPart.Workbook.GetFirstChild(Of Spreadsheet.Sheets)()
sheet = new Sheet();
sheet.Id = sheetDoc.WorkbookPart.GetIdOfPart(worksheetPart);
sheet.SheetId = 1;
sheet.Name = sheetName;
sheets.Append(sheet);
headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
//' Create Grid Header Row
//If Not columnNames Is Nothing Then
// If columnNames.Count <> datTable.Columns.Count Then columnNames = Nothing
//End If
if (hasHeaderRow)
{
if (columnNames == null)
{
columnNames = new List <string>();
foreach (System.Data.DataColumn column in datTable.Columns)
{
columnNames.Add(column.ColumnName.Replace("_", " "));
}
}
foreach (string column in columnNames)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column);
headerRow.AppendChild(cell);
}
shetData.AppendChild(headerRow);
}
//Create DataRow
if (!ReferenceEquals(columnType, null))
{
if (datTable.Columns.Count != columnType.Count)
{
columnType = null;
}
}
if (!ReferenceEquals(customIndexes, null))
{
int ix = 0;
foreach (System.Data.DataRow dsrow in datTable.Rows)
{
newRow = new Row();
ix = 0;
foreach (int col in customIndexes)
{
cell = new Cell();
if (ReferenceEquals(columnType, null))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
}
else
{
cell.DataType = columnType[ix];
ix++;
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
shetData.AppendChild(newRow);
}
}
else
{
int maxc = System.Convert.ToInt32(datTable.Columns.Count - 1);
int ix = 0;
if (columnType == null)
{
columnType = new List <CellValues>();
foreach (DataColumn cm in datTable.Columns)
{
if (cm.DataType == typeof(decimal) || cm.DataType == typeof(double) || cm.DataType == typeof(int) || cm.DataType == typeof(long))
{
columnType.Add(CellValues.Number);
}
//else if (cm.DataType == System.Type.GetType("System.DateTime")) // has some issues better to avoid
//{
// columnType.Add(CellValues.Date);
//}
else
{
columnType.Add(CellValues.String);
}
}
}
foreach (System.Data.DataRow dsrow in datTable.Rows)
{
newRow = new Row();
ix = 0;
for (var col = 0; col <= maxc; col++)
{
cell = new Cell();
if (ReferenceEquals(columnType, null))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
}
else
{
cell.DataType = columnType[ix];
ix++;
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
shetData.AppendChild(newRow);
}
}
workbookpart.Workbook.Save();
// Close the document.
sheetDoc.Close();
strm.Position = 0;
return(strm);
}