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);
}
}
}
protected void PopulateExcelTable(DataTable dataTable, excel.SheetData sheetData)
{
excel.Row excelRow = new excel.Row();
foreach (DataColumn dataColumn in dataTable.Columns)
{
excelRow.Append(new excel.Cell()
{
CellValue = new excel.CellValue(GetColumnName(dataColumn))
});
}
sheetData.AppendChild(excelRow);
foreach (DataRow row in dataTable.Rows)
{
excelRow = new excel.Row();
foreach (object cellItem in row.ItemArray)
{
excelRow.Append(new excel.Cell()
{
CellValue = new excel.CellValue(cellItem.ToString())
});
}
sheetData.AppendChild(excelRow);
}
}
//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);
}
}
}
}
public void CreateExcel(Stream stream)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Employees"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
//List<Employee> employees = Employees.EmployeesList;
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Id", CellValues.String),
ConstructCell("Name", CellValues.String),
ConstructCell("Birth Date", CellValues.String),
ConstructCell("Salary", CellValues.String));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Inserting each employee
for (int i = 0; i < 5; i++)
{
row = new Row();
row.Append(
ConstructCell(14.ToString(), CellValues.Number),
ConstructCell("Employee", CellValues.String),
ConstructCell(DateTime.Now.ToShortDateString(), CellValues.String),
ConstructCell((20000).ToString(), CellValues.Number));
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.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
// Create Excel Sheet
public void UpdateCells(string docName, IList <ExcelSheetInput> excelSheetInputs)
{
using (SpreadsheetDocument createdDocument = SpreadsheetDocument.Create(docName, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart createdWorkbookPart = createdDocument.AddWorkbookPart();
createdWorkbookPart.Workbook = new spd.Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart createdWorksheetPart = createdWorkbookPart.AddNewPart <WorksheetPart>();
createdWorksheetPart.Worksheet = new spd.Worksheet();
spd.Sheets createdSheets = createdWorkbookPart.Workbook.AppendChild(new spd.Sheets());
spd.Sheet createdSheet = new spd.Sheet()
{
Id = createdWorkbookPart.GetIdOfPart(createdWorksheetPart),
SheetId = 1,
Name = "Sheet 1"
};
createdSheets.Append(createdSheet);
createdWorkbookPart.Workbook.Save();
spd.SheetData sheetData = createdWorksheetPart.Worksheet.AppendChild(new spd.SheetData());
var rowsCount = excelSheetInputs.Last().RowNumber;
for (var rowsCounter = 1; rowsCounter <= rowsCount; rowsCounter++)
{
var rowItems = excelSheetInputs.Where(e => e.RowNumber == rowsCounter).ToList();
// Constructing header
spd.Row createdRow = new spd.Row();
foreach (ColumnName c in Enum.GetValues(typeof(ColumnName)))
{
try
{
var cellValue = rowItems.First(e => e.ColumnName.Equals(c.ToString()));
createdRow.Append(ConstructCell(cellValue.Text, spd.CellValues.String));
}
catch (Exception)
{
createdRow.Append(ConstructCell("", spd.CellValues.String));
}
}
sheetData.AppendChild(createdRow);
}
createdWorkbookPart.Workbook.Save();
}
}
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 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);
}
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);
}
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";
}
}//экспорт в 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 с учетом типа данных
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);
}
}
}
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");
}
}
}
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);
}
//------ 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);
}
}
}
