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); } }