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);
}
}
}
public Spreadsheet.Sheet AddSheet(string sheetName)
{
uint maxSheetId = 1;
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = null;
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = null;
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = null;
wsPart = this.workbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.WorksheetPart>();
wsPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
sheets = this.workbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
maxSheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max();
maxSheetId++;
}
sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet();
sheet.Id = this.workbookPart.GetIdOfPart(wsPart);
sheet.SheetId = maxSheetId;
sheet.Name = sheetName;
//if (this.workbookPart.Workbook.Sheets == null)
//{
// sheets = this.workbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
//}
sheets.Append(sheet);
Spreadsheet.Sheet newSheet = new Spreadsheet.Sheet(ref wsPart, ref sheet);
this.sheetCollection.Add(newSheet);
return(newSheet);
}
}//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
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 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";
}
/// <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();
}
}
}//экспорт в 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 с учетом типа данных
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;
}
}
}
//------ 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);
}
}
}
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");
}
}
}