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