public void copyPasteRangeExcel(string cellrng1, string cellrng2) { Microsoft.Office.Interop.Excel.Range sourceRange = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range("A17", "A17").EntireRow; Microsoft.Office.Interop.Excel.Range destinationRange = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(cellrng1, cellrng2); sourceRange.Copy(Type.Missing); // destinationRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); // destinationRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType., Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); destinationRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); //excelApp.get_Range("A1:A360,B1:E1", Type.Missing).Merge(Type.Missing); excelWorkbook.Save(); // excelWorkbook.Close(true, false, Type.Missing); }
private void ExportExcelWithInsertTemplate(string filename, string ext) { string templateFilename = string.Format("{0}\\{1}\\{2}.{3}", Application.StartupPath, UiUtility.ExcelTemplatePath, this.TPL_FILE_NAME, ext); try { //declare for using Ms.Excel Object Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application(); ObjExcel.DisplayAlerts = false; //for template excel Microsoft.Office.Interop.Excel.Workbooks objTempbooks = ObjExcel.Workbooks; //for current excel Microsoft.Office.Interop.Excel.Workbooks objWorkbooks = ObjExcel.Workbooks; //----------------------------------------------------------------------------------------------------------------------------- Microsoft.Office.Interop.Excel.Workbook book = objWorkbooks.Open(filename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Microsoft.Office.Interop.Excel.Sheets sheets = book.Worksheets; Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[this.SheetName]; workSheet.Select(Type.Missing); //Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange; Microsoft.Office.Interop.Excel.Range insertRange = workSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing); insertRange.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); //insert last row //Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange; //int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row; //string strRange = string.Format("A{0}", lastRow + 1); //Microsoft.Office.Interop.Excel.Range insertLastRange = workSheet.get_Range(strRange, Type.Missing); //insertLastRange.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow); //----------------------------------------------------------------------------------------------------------------------------- Microsoft.Office.Interop.Excel.Workbook tmpBook = objTempbooks.Open(templateFilename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Microsoft.Office.Interop.Excel.Sheets tmpSheets = tmpBook.Worksheets; Microsoft.Office.Interop.Excel.Worksheet tmpWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)tmpSheets[this.SheetName]; tmpWorkSheet.Select(Type.Missing); Microsoft.Office.Interop.Excel.Range copyRange = tmpWorkSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing); copyRange.EntireRow.Copy(Type.Missing); Microsoft.Office.Interop.Excel.Range pasteRange = workSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing); pasteRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); //----------------------------------------------------------------------------------------------------------------------------- //Microsoft.Office.Interop.Excel.Range copyLastRange = tmpWorkSheet.get_Range("A11", Type.Missing); //copyLastRange.EntireRow.Copy(Type.Missing); //Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange; //int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row; //string strRange = string.Format("A{0}", lastRow + 1); //Microsoft.Office.Interop.Excel.Range insertLastRange = workSheet.get_Range(strRange, Type.Missing); //insertLastRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAllUsingSourceTheme, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); //------------------------------------------------------------------------------------------------------------------------------------- book.Application.CutCopyMode = Microsoft.Office.Interop.Excel.XlCutCopyMode.xlCopy; //book.CheckCompatibility = false; tmpBook.Close(false, Type.Missing, Type.Missing); if (this._ShippingOrder_Hdr != null) { //Set Shipping Order Information. //SO_NO Microsoft.Office.Interop.Excel.Range sonoRange = workSheet.get_Range("K2", Type.Missing); sonoRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.SO_NO); //SO_DATE Microsoft.Office.Interop.Excel.Range sodateRange = workSheet.get_Range("K3", Type.Missing); if (this._ShippingOrder_Hdr.SO_DATE.HasValue) { sodateRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy}", this._ShippingOrder_Hdr.SO_DATE.Value)); } else { sodateRange.set_Value(System.Type.Missing, string.Empty); } //Customer Microsoft.Office.Interop.Excel.Range custRange = workSheet.get_Range("B4", Type.Missing); custRange.set_Value(System.Type.Missing, string.Format("{0} - {1}", this._ShippingOrder_Hdr.PARTY_ID, this._ShippingOrder_Hdr.PARTY_NAME)); //PO_REF_NO Microsoft.Office.Interop.Excel.Range porefRange = workSheet.get_Range("B5", Type.Missing); porefRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.REF_NO); //PO_REF_DATE Microsoft.Office.Interop.Excel.Range podateRange = workSheet.get_Range("G5", Type.Missing); if (this._ShippingOrder_Hdr.REF_DATE.HasValue) { podateRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy}", this._ShippingOrder_Hdr.REF_DATE.Value)); } else { podateRange.set_Value(System.Type.Missing, string.Empty); } //ETD Microsoft.Office.Interop.Excel.Range etdRange = workSheet.get_Range("K5", Type.Missing); if (this._ShippingOrder_Hdr.ETA.HasValue) { etdRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy HH:mm}", this._ShippingOrder_Hdr.ETA.Value)); } else { etdRange.set_Value(System.Type.Missing, string.Empty); } //REMARK Microsoft.Office.Interop.Excel.Range remarkRange = workSheet.get_Range("B6", Type.Missing); remarkRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.REMARK); var focusRange = workSheet.get_Range("A1", "A1").Select(); book.Save(); book.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(focusRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(sonoRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(sodateRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(custRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(porefRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(podateRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(etdRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(remarkRange); } else { var focusRange = workSheet.get_Range("A1", "A1").Select(); book.Save(); book.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(focusRange); } //------------------------------------------------------------------------------------- //System.Runtime.InteropServices.Marshal.ReleaseComObject(insertLastRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(copyRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(tmpSheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(tmpBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(insertRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(book); ObjExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ObjExcel); } catch (Exception ex) { // } }