/// <summary> /// 插入行 /// </summary> /// <param name="sheet">worksheet</param> /// <param name="rowIndex">行数</param> public static void InsertRows(this Microsoft.Office.Interop.Excel.Worksheet sheet, int rowIndex) { Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[rowIndex, Type.Missing]; //object Range.Insert(object shift, object copyorigin); //shift: Variant类型,可选。指定单元格的调整方式。可以为下列 XlInsertShiftDirection 常量之一: //xlShiftToRight 或 xlShiftDown。如果省略该参数,Microsoft Excel 将根据区域形状确定调整方式。 range.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing); }
/// <summary> /// 插行(在指定行上面插入指定数量行) /// </summary> /// <param name="rowIndex"></param> /// <param name="count"></param> public void InsertRows(int rowIndex, int count) { try { range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[rowIndex, this.missing]; for (int i = 0; i < count; i++) { range.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, missing); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } }
/// <summary> /// 插列(在指定列右边插入指定数量列) /// </summary> /// <param name="columnIndex"></param> /// <param name="count"></param> public void InsertColumns(int columnIndex, int count) { try { range = (Microsoft.Office.Interop.Excel.Range)(workSheet.Columns[columnIndex, this.missing]); //注意:这里和VS的智能提示不一样,第一个参数是columnindex for (int i = 0; i < count; i++) { range.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, missing); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } }
/// <summary> /// Вставляет строку между строк в Excel /// </summary> /// <param name="rowNum"></param> public void InsertRow(int rowNum) { Microsoft.Office.Interop.Excel.Range cellRange = (Microsoft.Office.Interop.Excel.Range)ExcelApp.Cells[rowNum, 1]; Microsoft.Office.Interop.Excel.Range rowRange = cellRange.EntireRow; rowRange.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, false); }
private void CrearExcel(string filename, int periodo, List <RendimientoViewModel> listaRendimiento) { try { var oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.Visible = false; oXL.UserControl = false; var oWB = (Microsoft.Office.Interop.Excel._Workbook)oXL.Workbooks.Open(System.IO.Path.GetDirectoryName(new System.Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase).LocalPath) + "/PlantillaRendimiento.xls"); var oSheetEnBLanco = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets["PlantillaEnBlanco"]; var blendPorPeriodo = listaRendimiento .Select(x => x.Blend) .Distinct() .OrderBy(x => x.Periodo) .ThenBy(x => x.Descripcion) .ToList(); if (blendPorPeriodo.Count > 0) { foreach (var blend in blendPorPeriodo) { oSheetEnBLanco.Copy(Type.Missing, oWB.Sheets[oWB.Sheets.Count]); oWB.Sheets[oWB.Sheets.Count].Name = blend.Descripcion + "-" + blend.Periodo; var oSheetBlendPeriodo = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[blend.Descripcion + "-" + blend.Periodo]; var RngToCopy = oSheetBlendPeriodo.get_Range("A7", "L7").EntireRow.Copy(Type.Missing); // Celda B3 oSheetBlendPeriodo.Cells[3, 2] = "BLEND: " + blend.Descripcion; var registros = listaRendimiento.Where(x => x.Blend == blend).OrderBy(x => x.Fecha).ToList(); for (int i = 0; i < registros.Count; i++) { var row = (i + 7); Microsoft.Office.Interop.Excel.Range RngToInsert = oSheetBlendPeriodo.get_Range("A" + row, Type.Missing).EntireRow; RngToInsert.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, RngToCopy); oSheetBlendPeriodo.Cells[row, 01].Value2 = registros[i].BlendDescripcion; oSheetBlendPeriodo.Cells[row, 02].Value2 = registros[i].Fecha; oSheetBlendPeriodo.Cells[row, 03].Value2 = registros[i].OrdenDeProduccion; oSheetBlendPeriodo.Cells[row, 04].Value2 = registros[i].Corrida; oSheetBlendPeriodo.Cells[row, 05].Value2 = String.Empty; oSheetBlendPeriodo.Cells[row, 06].Value2 = registros[i].PrimeraCaja; oSheetBlendPeriodo.Cells[row, 07].Value2 = registros[i].UltimaCaja; oSheetBlendPeriodo.Cells[row, 08].Value2 = registros[i].NumeroCajas; oSheetBlendPeriodo.Cells[row, 09].Value2 = registros[i].Kilos; oSheetBlendPeriodo.Cells[row, 10].Value2 = String.Empty; } // borrar la ultima fila la cual esta vacia vacia ((Microsoft.Office.Interop.Excel.Range)oSheetBlendPeriodo.Rows[(registros.Count + 7), System.Reflection.Missing.Value]).Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); oSheetBlendPeriodo.get_Range("H5", "H5").Formula = "=SUM(H7:H9684)"; oSheetBlendPeriodo.get_Range("I5", "I5").Formula = "=SUM(I7:I9685)"; oSheetBlendPeriodo.get_Range("J5", "J5").Formula = "=SUM(J7:J9685)"; oSheetBlendPeriodo.get_Range("K5", "K5").Formula = "=SUM(K7:K685)"; oSheetBlendPeriodo.get_Range("L5", "L5").Formula = "=IF(ISERROR(+((K5)/(I5))*100), 0, +((K5)/(I5))*100)"; RngToCopy = oSheetBlendPeriodo.get_Range("B" + (registros.Count - 1 + 7 + 5), "D" + (registros.Count - 1 + 7 + 5)).EntireRow.Copy(Type.Missing); var taraActual = -1m; var taraActualRow = -1; for (int i = 0; i < registros.Count; i++) { if (taraActual != registros[i].Tara) { taraActual = registros[i].Tara; if (taraActualRow == -1) { taraActualRow = (registros.Count - 1 + 7 + 5); } else { taraActualRow++; } Microsoft.Office.Interop.Excel.Range RngToInsert = oSheetBlendPeriodo.get_Range("B" + taraActualRow, Type.Missing).EntireRow; RngToInsert.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, RngToCopy); oSheetBlendPeriodo.Cells[taraActualRow, 02].Value2 = registros[i].PrimeraCaja; oSheetBlendPeriodo.Cells[taraActualRow, 03].Value2 = registros[i].UltimaCaja; oSheetBlendPeriodo.Cells[taraActualRow, 04].Value2 = registros[i].Tara; } else { oSheetBlendPeriodo.Cells[taraActualRow, 03].Value2 = registros[i].UltimaCaja; } } if (taraActualRow != -1) { // borrar la ultima fila la cual esta vacia vacia ((Microsoft.Office.Interop.Excel.Range)oSheetBlendPeriodo.Rows[taraActualRow + 1, System.Reflection.Missing.Value]).Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); } oSheetBlendPeriodo.get_Range("K7", "K" + ((7 - 1) + registros.Count)).Formula = "=(H7*200)"; oSheetBlendPeriodo.get_Range("L7", "L" + ((7 - 1) + registros.Count)).Formula = "=IF(ISERROR(+((K7)/(I7))*100), 0, +((K7)/(I7))*100)"; } ((Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets["PlantillaEnBlanco"]).Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVeryHidden; oWB.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } else { throw new Exception("No hay registros"); } oWB.Close(); oXL.Quit(); } catch (Exception ex) { throw new Exception("Error Office.Interop", ex); } }
private bool Export(string filepath, bool isOpen) { try { Microsoft.Office.Interop.Excel.Application excel; Microsoft.Office.Interop.Excel._Workbook objBook; ultraGridExcelExporter1.Export(ultraGrid1, filepath); Microsoft.Office.Interop.Excel.Workbooks objBooks; //接口 workbooks Microsoft.Office.Interop.Excel.Sheets objSheets; // 接口 sheets Microsoft.Office.Interop.Excel.Worksheet objSheet; //接口 worksheet Microsoft.Office.Interop.Excel.Range range = null; excel = new Microsoft.Office.Interop.Excel.Application(); objBooks = excel.Workbooks; //Object miss = System.Reflection.Missing.Value; objBook = objBooks.Add(filepath); objSheets = objBook.Sheets; objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objSheets[1]; excel.Visible = false; //让后台执行设置为不可见,为true的话会看到打开一个Excel,然后数据在往里写 //自动换行, objSheet.Cells.WrapText = true; Microsoft.Office.Interop.Excel.Range tmpRange = (Microsoft.Office.Interop.Excel.Range)objSheet.Rows[1, System.Reflection.Missing.Value]; tmpRange.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, System.Reflection.Missing.Value); objSheet.get_Range("A1", "I1").Merge(objSheet.get_Range("A1", "I1").MergeCells); objSheet.get_Range("A1", "I1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; objSheet.get_Range("A1", "I1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; objSheet.get_Range("A1", "I1").Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[1, 1]).Font.Size = 18; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[1, 1]).RowHeight = 30; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[1, 1]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[1, 1]).Interior.Color = ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[3, 1]).Interior.Color; excel.Cells[1, 1] = "云南昆钢制管有限公司产成品入库单"; Microsoft.Office.Interop.Excel.Range tmpRange2 = (Microsoft.Office.Interop.Excel.Range)objSheet.Rows[2, System.Reflection.Missing.Value]; tmpRange2.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, System.Reflection.Missing.Value); objSheet.get_Range("A2", "I2").Merge(objSheet.get_Range("A2", "I2").MergeCells); objSheet.get_Range("A2", "I2").VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; objSheet.get_Range("A2", "I2").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; objSheet.get_Range("A2", "I2").Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[2, 1]).Font.Size = 9; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[2, 1]).RowHeight = 15; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[2, 1]).Font.Bold = false; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[2, 1]).Interior.Color = ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[1, 1]).Interior.Color; excel.Cells[2, 1] = "生产班次: " + lblShift.Text + " " + tbType.Text; Microsoft.Office.Interop.Excel.Range tmpRange3 = (Microsoft.Office.Interop.Excel.Range)objSheet.Rows[3, System.Reflection.Missing.Value]; tmpRange3.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, System.Reflection.Missing.Value); objSheet.get_Range("A3", "I3").Merge(objSheet.get_Range("A3", "I3").MergeCells); objSheet.get_Range("A3", "I3").VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; objSheet.get_Range("A3", "I3").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[3, 1]).Font.Size = 9; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[3, 1]).RowHeight = 15; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[3, 1]).Font.Bold = false; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[3, 1]).Interior.Color = ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[1, 1]).Interior.Color; excel.Cells[3, 1] = "产品名称:" + lblMaterialName.Text + " 牌号:" + lblSteelType.Text + " 规格(mm):" + textBox1.Text + " 执行标准:" + lblStandard.Text + " 入库单号:" + lblRKDH.Text; int sumIndex = objSheet.UsedRange.Cells.Rows.Count + 1; Microsoft.Office.Interop.Excel.Range tmpRangeSum = (Microsoft.Office.Interop.Excel.Range)objSheet.Rows[sumIndex, System.Reflection.Missing.Value]; tmpRangeSum.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, System.Reflection.Missing.Value); objSheet.get_Range("A" + sumIndex.ToString(), "D" + sumIndex.ToString()).Merge(objSheet.get_Range("A" + sumIndex.ToString(), "D" + sumIndex.ToString()).MergeCells); objSheet.get_Range("G" + sumIndex.ToString(), "I" + sumIndex.ToString()).Merge(objSheet.get_Range("G" + sumIndex.ToString(), "I" + sumIndex.ToString()).MergeCells); objSheet.get_Range("A" + sumIndex.ToString(), "D" + sumIndex.ToString()).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; objSheet.get_Range("A" + sumIndex.ToString(), "D" + sumIndex.ToString()).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; objSheet.get_Range("A" + sumIndex.ToString(), "D" + sumIndex.ToString()).Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; objSheet.get_Range("E" + sumIndex.ToString(), "E" + sumIndex.ToString()).Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; objSheet.get_Range("F" + sumIndex.ToString(), "F" + sumIndex.ToString()).Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; objSheet.get_Range("G" + sumIndex.ToString(), "I" + sumIndex.ToString()).Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[sumIndex, 1]).Font.Size = 9; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[sumIndex, 1]).RowHeight = 15; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[sumIndex, 1]).Font.Bold = false; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[sumIndex, 1]).Interior.Color = ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[1, 1]).Interior.Color; excel.Cells[sumIndex, 1] = "合 计"; double sumWeight = 0; double sumTheoryWeight = 0; foreach (DataRow dr in dataTable1.Rows) { if (dr["FN_WEIGHT"].ToString() != "") { sumWeight += double.Parse(dr["FN_WEIGHT"].ToString()); } if (dr["FN_THEORYWEIGHT"].ToString() != "") { sumTheoryWeight += double.Parse(dr["FN_THEORYWEIGHT"].ToString()); } } ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[sumIndex, 6]).Font.Size = 9; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[sumIndex, 6]).RowHeight = 15; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[sumIndex, 6]).Font.Bold = false; ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[sumIndex, 6]).Interior.Color = ((Microsoft.Office.Interop.Excel.Range)objSheet.UsedRange.Cells[1, 1]).Interior.Color; excel.Cells[sumIndex, 5] = sumTheoryWeight.ToString(); excel.Cells[sumIndex, 6] = sumWeight.ToString(); //页眉页脚 objSheet.PageSetup.RightHeader = "&P/&N页 "; objSheet.PageSetup.LeftFooter = "入库员: " + "质检员: " + "验收员: " + "日期: " + lblDate.Text; //objSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperB4; objBook.SaveCopyAs(filepath); //设置禁止弹出保存和覆盖的询问提示框 excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; //确保Excel进程关闭 objBooks.Close(); excel.Workbooks.Close(); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); GC.Collect(); System.GC.WaitForPendingFinalizers(); //MessageBox.Show("数据导出完成!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); if (System.IO.File.Exists(filepath) && isOpen) { System.Diagnostics.Process.Start(filepath); //保存成功后打开此文件 } return(true); } catch (Exception e) { MessageBox.Show(e.Message); return(false); } }