//Экспортв MS Excel
private void bt_Eport_Excel_Click(object sender, RoutedEventArgs e)
{
Microsoft.Office.Interop.Excel.Application app = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
var process = Process.GetProcessesByName("EXCEL");
Microsoft.Win32.SaveFileDialog openDlg = new Microsoft.Win32.SaveFileDialog();
openDlg.FileName = DBConnection.Name_File;
openDlg.Filter = "Excel (.xls)|*.xls |Excel (.xlsx)|*.xlsx |All files (*.*)|*.*";
openDlg.FilterIndex = 2;
openDlg.RestoreDirectory = true;
string path = openDlg.FileName;
if (openDlg.ShowDialog() == true)
{
app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
app.DisplayAlerts = false;
wb = app.Workbooks.Add();
ws = wb.ActiveSheet;
dgClient.SelectAllCells();
dgClient.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
ApplicationCommands.Copy.Execute(null, dgClient);
ws.Paste();
ws.Range["A1", "E1"].Font.Bold = true;
int number1 = ws.UsedRange.Rows.Count;
Microsoft.Office.Interop.Excel.Range myRange = ws.Range["A1", "E" + number1];
myRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
myRange.WrapText = false;
ws.Columns.EntireColumn.AutoFit();
//wb.SaveAs(path);
}
}
internal static void exportarDataTableToExcel(DataTable tabla, string encabezado)
{
try
{
int columnas = tabla.Columns.Count;
if (tabla == null)
{
MessageBox.Show("No se encontró información en la tabla para exportación. Contacta a Aseguramiento de calidad.");
return;
}
ClassUtilidades.CopyDataTableToClipboard(tabla, true);
//Creamos una nueva aplicación de excel.
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
//Abrimos la plantilla de reportes y creamos un nuevo workbook para mostrar ahí el reporte.
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Add();
//Obtenemos todas las hojas de la plantilla
Microsoft.Office.Interop.Excel.Sheets sheets = xlWorkBook.Worksheets;
//Obtenemos la primera hoja de la plantilla
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = xlApp.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
int inicio_y_tabla = 4;
int inicio_x_tabla = 2;
//Colocamos la fecha de eleboración del reporte.
xlWorkSheet.Cells[1, inicio_x_tabla + columnas - 1] = "'" + DateTime.Now.ToString("dd/MM/yyyy");
xlWorkSheet.Cells[1, inicio_x_tabla + columnas - 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
//Combinamos la celda del encabezado.
xlWorkSheet.Range[xlWorkSheet.Cells[2, inicio_x_tabla], xlWorkSheet.Cells[2, columnas + inicio_x_tabla - 1]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[2, inicio_x_tabla], xlWorkSheet.Cells[2, columnas + inicio_x_tabla - 1]].Font.size = 15;
xlWorkSheet.Range[xlWorkSheet.Cells[2, inicio_x_tabla], xlWorkSheet.Cells[2, columnas + inicio_x_tabla - 1]].Font.bold = true;
xlWorkSheet.Range[xlWorkSheet.Cells[2, inicio_x_tabla], xlWorkSheet.Cells[2, columnas + inicio_x_tabla - 1]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[2, inicio_x_tabla] = encabezado;
//Pegamos nuestra tabla para la generación del reporte.
Microsoft.Office.Interop.Excel.Range CR = xlWorkSheet.Cells[inicio_y_tabla, inicio_x_tabla] as Microsoft.Office.Interop.Excel.Range;
CR.Select();
xlWorkSheet.Paste();
//Colocamos los bordes de las celdas
xlWorkSheet.Range[xlWorkSheet.Cells[inicio_y_tabla, inicio_x_tabla], xlWorkSheet.Cells[tabla.Rows.Count + inicio_y_tabla, columnas + inicio_x_tabla - 1]].borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
xlWorkSheet.Range[xlWorkSheet.Cells[inicio_y_tabla, inicio_x_tabla], xlWorkSheet.Cells[tabla.Rows.Count + inicio_y_tabla, columnas + inicio_x_tabla - 1]].borders.Weight = 2d;
//Se ponen todas las columnas del mismo ancho.
xlWorkSheet.Range[xlWorkSheet.Cells[inicio_y_tabla, 1], xlWorkSheet.Cells[tabla.Rows.Count + inicio_y_tabla, columnas + inicio_x_tabla]].ColumnWidth = 25;
//Coloreamos los encabezados de las celdas.
xlWorkSheet.Range[xlWorkSheet.Cells[inicio_y_tabla, inicio_x_tabla], xlWorkSheet.Cells[inicio_y_tabla, columnas + inicio_x_tabla - 1]].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
xlWorkSheet.Range[xlWorkSheet.Cells[inicio_y_tabla, inicio_x_tabla], xlWorkSheet.Cells[inicio_y_tabla, columnas + inicio_x_tabla - 1]].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
string letra = obtenerLetraDeRango(columnas + inicio_x_tabla);
//Establecemos los márgenes para la impresión y hacemos autoFit
xlWorkSheet.PageSetup.PrintArea = "";
xlWorkSheet.PageSetup.PrintArea = string.Format("A1:{0}{1}", letra, tabla.Rows.Count + inicio_y_tabla);
Microsoft.Office.Interop.Excel.Range aRange = xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[tabla.Rows.Count + inicio_y_tabla, columnas + inicio_x_tabla]];
aRange.Rows.AutoFit();
string rutaPDF = System.Windows.Forms.Application.StartupPath + "\\ultimoReporte.pdf";
//MessageBox.Show("Guardado en " + rutaPDF);
xlWorkSheet.ExportAsFixedFormat(
Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
rutaPDF,
Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard,
true,
false,
Type.Missing,
Type.Missing,
false);
xlApp.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
xlApp.Visible = true;
xlApp.DisplayAlerts = true;
//xlWorkBook.WindowDeactivate += cerrarExcel;
}
catch (Exception e)
{
ClassUtilidades.mostrarMensajeValidacion(e.Message.ToString());
}
}
public static void ExportExcel(List <Vehicle> list)
{
System.Data.DataTable dt = new System.Data.DataTable();
// 列
dt.Columns.Add("VehNr", typeof(string));
dt.Columns.Add("Queue", typeof(string));
dt.Columns.Add("QTim", typeof(string));
dt.Columns.Add("t", typeof(string));
dt.Columns.Add("RworldldX", typeof(string));
// 行
for (int i = 0; i < list.Count; i++)
{
dt.Rows.Add(list[i].VehNr, list[i].Queue, list[i].QTim, list[i].t, list[i].RworldldX);
}
// 写入标题
StringBuilder sb = new StringBuilder();
for (int i = 0; i < dt.Columns.Count; i++)
{
sb.Append(dt.Columns[i].ColumnName.ToString() + "\t");
}
// 加入换行符
sb.Append(Environment.NewLine);
// 写入内容
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
sb.Append(dt.Rows[i][j].ToString() + "\t");
}
sb.Append(Environment.NewLine);
}
System.Windows.Forms.Clipboard.SetText(sb.ToString());
// 新建excel应用
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
if (excelApp == null)
{
return;
}
//设置为不可见，操作在后台执行，为 true 的话会打开 Excel
excelApp.Visible = false;
//初始化工作簿
Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
//新增加一个工作簿，Add（）方法也可以直接传入参数 true
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//同样是新增一个工作簿，但是会弹出保存对话框
//Excel.Workbook workbook = workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Paste();
//新建一个 Excel 文件
//string filePath = @"C:\Users\Lenovo\Desktop\" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
string filePath = @"C:\Users\Summerki\Desktop\活\test.xlsx";
//创建文件
FileStream file = new FileStream(filePath, FileMode.CreateNew);
//关闭释放流，不然没办法写入数据
file.Close();
file.Dispose();
//保存写入的数据，这里还没有保存到磁盘
workbook.Saved = true;
//保存到指定的路径
workbook.SaveCopyAs(filePath);
}
private void PopulateBody(bool Use2Pos, IList <IGrouping <string, DeviceItemWithParams> > Data, int PageCount)
{
var actualNumOfItemsInFirstPage = Use2Pos
? NUM_OF_ITEMS_IN_FIRST_PAGE / 2
: NUM_OF_ITEMS_IN_FIRST_PAGE;
var actualNumOfItemsRestPage = Use2Pos
? NUM_OF_ITEMS_IN_THE_REST_OF_THE_PAGES / 2
: NUM_OF_ITEMS_IN_THE_REST_OF_THE_PAGES;
var mpy = Use2Pos ? 2 : 1;
var k = Data.Count;
if (k >= actualNumOfItemsInFirstPage)
{
k = actualNumOfItemsInFirstPage;
}
else
{
m_XlWorkSheet.Range[
"A" + (FIRST_ROW_INDEX + Data.Count * mpy), "O" + (FIRST_ROW_INDEX + actualNumOfItemsInFirstPage * mpy)]
.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftToLeft);
}
for (var i = 0; i < k; i++)
{
PopulateOneBlock(Use2Pos, Data[i], i, FIRST_ROW_INDEX);
}
if (Data.Count > actualNumOfItemsInFirstPage)
{
var body = m_XlWorkSheet.Range["A50", "O99"];
body.Copy();
if (PageCount > 2)
{
for (var p = 0; p < PageCount - 2; p++)
{
int a = THIRD_PAGE_1 + ROWS_ON_PAGE * p, t = THIRD_ROW_2 + ROWS_ON_PAGE * p;
m_XlWorkSheet.Range["A" + a, "O" + t].PasteSpecial(
Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
false, false);
for (var i = a; i <= t; i++)
{
m_XlWorkSheet.Range["A" + i, "O" + i].RowHeight =
body.Range["A" + (1 + i - a), "O" + (1 + i - a)].RowHeight;
}
}
}
var rest = (Data.Count - actualNumOfItemsInFirstPage) % actualNumOfItemsRestPage;
if (rest > 0)
{
m_XlWorkSheet.Range["A" + (ROWS_ON_PAGE * (PageCount - 1) + 4 + rest * mpy), "O" + (ROWS_ON_PAGE * PageCount)]
.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftToLeft);
}
for (var p = 0; p < PageCount - 1; p++)
{
if (m_PrivateDocument)
{
var shape = m_XlWorkSheet.Shapes.Item("WaterMark");
shape.Copy();
m_XlWorkSheet.Range["A" + (SHAPE_CONST_1 + ROWS_ON_PAGE * p)].Select();
m_XlWorkSheet.Paste();
}
for (var i = 0; i < rest; i++)
{
var item = actualNumOfItemsInFirstPage + actualNumOfItemsRestPage * p + i;
PopulateOneBlock(Use2Pos, Data[item], i, SHAPE_CONST_2 + ROWS_ON_PAGE * p);
}
}
}
}