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);
}
}
public static bool ExportarDataGridAExcel(DataGridView dgv)
{
try
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Add(true);
int columnIndex = 0;
foreach (DataGridViewColumn column in dgv.Columns)
{
columnIndex++;
excel.Cells[1, columnIndex] = column.HeaderText;
excel.Columns[columnIndex].ColumnWidth = column.Width / 7;
var ran = excel.Columns[columnIndex];
}
int rowIndex = 1;
foreach (DataGridViewRow row in dgv.Rows)
{
rowIndex++;
columnIndex = 0;
for (columnIndex = 0; columnIndex < dgv.Columns.Count; columnIndex++)
{
//columnIndex++;
object obj = row.Cells[columnIndex].Value;
DataGridViewCell cell = row.Cells[columnIndex];
Microsoft.Office.Interop.Excel.Range range = excel.Cells[rowIndex + 1, columnIndex + 1];
int c;
if ((c = System.Drawing.ColorTranslator.ToOle(row.DefaultCellStyle.BackColor)) != 0)
{
range.Interior.Color = c;
range.Font.Color = System.Drawing.ColorTranslator.ToOle(row.DefaultCellStyle.ForeColor);
}
if (row.DefaultCellStyle.Font != null)
{
if (row.DefaultCellStyle.Font.Bold)
{
range.Font.FontStyle = "Bold";
}
}
if (obj == null)
{
continue;
}
string dato = obj.ToString();
DateTime val;
if (dato.Length >= 10 && DateTime.TryParse(dato, out val) == true)
{
dato = val.ToString("yyyy/MM/dd");
}
range.Value = dato;
if (dgv.Columns[columnIndex].DefaultCellStyle.Alignment == DataGridViewContentAlignment.MiddleRight)
{
range.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
}
else
{
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
}
//excel.Cells[rowIndex + 1, ColumnIndex]
}
}
excel.Visible = true;
return(true);
}
catch (Exception ex)
{
Utiles.WriteErrorLog(ex.Message);
Mensajes.msgErrorExcel();
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);
}
}