protected bool setCellFormula(Excel.Worksheet targetSheet, String cell, String value) { bool result = true; try { // try, because it might fail Excel.Range targetCell = targetSheet.get_Range(cell); targetCell.Value2 = ""; targetCell.Formula = value; int row = parseRow(cell); if ((row % 2) != 0) { if ((row % 2) != 0) { targetSheet.Cells.Range[cell, Type.Missing].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightGray; } else { targetSheet.Cells.Range[cell, Type.Missing].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbWhiteSmoke; } } targetCell.Calculate(); } catch (Exception e) { System.Windows.Forms.MessageBox.Show("Error code:\n" + e.Message, "File Import Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); result = false; } return(result); }
private void RunExcelCalculations() { //<Snippet30> Excel.Range rng = this.Application.get_Range("A1"); //</Snippet30> //<Snippet31> rng.Calculate(); //</Snippet31> }
public static void NewWorksheet(object book, object sheet) { Excel.Worksheet ws = (Excel.Worksheet)sheet; Globals.Worksheets.Add(ws); ws.Change += new Excel.DocEvents_ChangeEventHandler(ChangeWatcher.Change); DeactivateSheetWatcher deactivateWatcher = new DeactivateSheetWatcher(ws); ws.Deactivate += new Excel.DocEvents_DeactivateEventHandler(deactivateWatcher.Deactivate); Excel.Range constrainedCell = ws.Cells.Find("CONSTRAIN", Type.Missing, Excel.XlFindLookIn.xlFormulas, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, true, Type.Missing, Type.Missing); Excel.Range firstFind = constrainedCell; while (constrainedCell != null) { constrainedCell.Calculate(); System.Windows.Forms.MessageBox.Show("Inside the loop!"); constrainedCell = ws.Cells.FindNext(constrainedCell); if (constrainedCell.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing) == firstFind.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing)) { break; } } }
private void ExecuteNotify(ExcelNotityPropertyContext context) { if (isDisposed || context.ContextItem.IsDisposed || !context.View.IsRendered) { return; } ExcelInterop.Worksheet worksheet = null; ExcelInterop.Range range = null; bool enableEvent = ExcelApplication.Application.EnableEvents; try { worksheet = context.View.FirstOutputCell.Worksheet; KeyValuePair <int, int> kvp = context.Param; range = worksheet.Cells[context.View.FirstOutputCell.Row + kvp.Key, context.View.FirstOutputCell.Column + kvp.Value]; if (range != null) { object value = context.ContextItem.ResolveBinding(); if (value is Enum) { value = ((Enum)value).ToString(); } if (!object.Equals(range.Value2, value)) { if (enableEvent) { ExcelApplication.Application.EnableEvents = false; } range.Value2 = value; if (context.ContextItem is ExcelContextItemWithFormula) { range.Calculate(); ((ExcelContextItemWithFormula)context.ContextItem).UpdateTarget(range.Value2); } context.View.CurrentSelectedCell?.Select(); } context.ContextItem.BindingDefinition.DecoratorDefinition?.Resolve(range, context.ContextItem); } sleepTime = 0; } catch (COMException comEx) { waitExcelBusy = true; NotifyPropertyChanged(context); if (sleepTime < 1000) { sleepTime += 10; } } catch (Exception ex) { string message = $"'ExecuteNotify' failed.{ex.Message}"; Logger.Instance.LogException(LogType.Error, ex, message); } finally { if (worksheet != null) { ExcelApplication.ReleaseComObject(worksheet); worksheet = null; } try { if (ExcelApplication.Application.EnableEvents != enableEvent) { ExcelApplication.Application.EnableEvents = enableEvent; } } catch (COMException comEx) { } } range = null; }
private void export_excel(string reffnumber) { string msg = ""; try { #region start excel Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBookReputasi; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheetLaporan; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheetReputasi; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheetTenor; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBookReputasi = xlApp.Workbooks.Add(misValue); #endregion #region content excel #region looping sheet //looping sheet DataTable dtsheet; dtsheet = conn.GetDataTable("exec SP_VW_SHEET_LIST @1", new object[] { reffnumber }, dbtimeout); for (int j = 0; j < dtsheet.Rows.Count; j++) { string appid = dtsheet.Rows[j][0].ToString(); #region sheet tenor //sheet tenor string sheetTenorName = dtsheet.Rows[j][2].ToString(); xlWorkSheetTenor = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBookReputasi.Worksheets.Add(misValue, misValue, misValue, misValue); xlWorkSheetTenor.Name = sheetTenorName; //xlWorkSheetTenor.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //xlWorkSheetTenor.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //xlWorkSheetTenor.PageSetup.LeftMargin = 0.1; //xlWorkSheetTenor.PageSetup.RightMargin = 0.1; //xlWorkSheetTenor.PageSetup.TopMargin = 0.1; //xlWorkSheetTenor.PageSetup.BottomMargin = 0.1; //xlWorkSheetTenor.PageSetup.HeaderMargin = 0.1; //xlWorkSheetTenor.PageSetup.FooterMargin = 0.1; ((Excel.Range)xlWorkSheetTenor.Cells[1, 1]).EntireColumn.ColumnWidth = 3; ((Excel.Range)xlWorkSheetTenor.Cells[1, 2]).EntireColumn.ColumnWidth = 20; ((Excel.Range)xlWorkSheetTenor.Cells[1, 3]).EntireColumn.ColumnWidth = 20; ((Excel.Range)xlWorkSheetTenor.Cells[1, 4]).EntireColumn.ColumnWidth = 20; ((Excel.Range)xlWorkSheetTenor.Cells[1, 5]).EntireColumn.ColumnWidth = 12; ((Excel.Range)xlWorkSheetTenor.Cells[1, 6]).EntireColumn.ColumnWidth = 14; ((Excel.Range)xlWorkSheetTenor.Cells[1, 7]).EntireColumn.ColumnWidth = 12; ((Excel.Range)xlWorkSheetTenor.Cells[1, 8]).EntireColumn.ColumnWidth = 10; ((Excel.Range)xlWorkSheetTenor.Cells[1, 9]).EntireColumn.ColumnWidth = 14; ((Excel.Range)xlWorkSheetTenor.Cells[1, 10]).EntireColumn.ColumnWidth = 10; //get sheet content object[] paramTenor = { reffnumber, appid }; DataTable dtTenor = conn.GetDataTable("EXEC sp_vw_excel_tenor @1,@2", paramTenor, dbtimeout); //row dttenor for (int i = 0; i <= dtTenor.Rows.Count - 1; i++) { int startRow = int.Parse(dtTenor.Rows[i][0].ToString()); int startColumn = int.Parse(dtTenor.Rows[i][1].ToString()); int EndRow = int.Parse(dtTenor.Rows[i][2].ToString()); int EndColumn = int.Parse(dtTenor.Rows[i][3].ToString()); if (EndRow == 0 || EndColumn == 0) { EndRow = startRow; EndColumn = startColumn; } bool isMerge = bool.Parse(dtTenor.Rows[i][4].ToString()); string bgColor = dtTenor.Rows[i][5].ToString(); bool isbold = bool.Parse(dtTenor.Rows[i][6].ToString()); string valueType = dtTenor.Rows[i][7].ToString(); string valueColumn = dtTenor.Rows[i][8].ToString(); float valueColumn_numeric = float.Parse(dtTenor.Rows[i][9].ToString()); string datatype = dtTenor.Rows[i][10].ToString(); //Excel.Range range = (Excel.Range)xlWorkSheetReputasi.Cells[i + 2, j + 1]; Excel.Range range = (Excel.Range)xlWorkSheetTenor.Range[xlWorkSheetTenor.Cells[startRow, startColumn], xlWorkSheetTenor.Cells[EndRow, EndColumn]]; //range.ColumnWidth = 7; if (startColumn == 2) { range.Cells.WrapText = true; } else { range.Cells.WrapText = false; } if (valueType.Trim() == "int" || valueType.Trim() == "formula_int" || valueType.Trim() == "formula_int_border") { range.NumberFormat = "0"; } else if (valueType.Trim() == "formula_decimal" || valueType.Trim() == "formula_decimal_border") { //range.NumberFormat = "#,##0.00"; range.NumberFormat = "0.00%"; } else if (valueType.Trim() == "date" || valueType.Trim() == "date_border") { range.NumberFormat = "mm/dd/yyyy"; } else if (datatype == "string" && valueType.Trim() != "formula") { range.NumberFormat = "@"; } else if (datatype == "numeric" || valueType.Trim() == "formula") { range.NumberFormat = "Rp #,##0.00"; } //if (valueType.Trim() == "text_center") //{ // range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignJustify; // range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify; //} if (isMerge) { range.Merge(); } //if (valueType.Trim().Contains("border")) //{ // range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; // range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; // range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; // range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; // //range.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = 1d; // //range.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = 1d; // //range.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 1d; // //range.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 1d; //} //bgColor isbold //bgColor if (bgColor == "Old Green") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); } else if (bgColor == "Pink") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Moccasin); } else if (bgColor == "Yellow") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow); } else if (bgColor == "Young Green") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Honeydew); } else if (bgColor == "blue") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSteelBlue); } else { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); } ////isbold //if (isbold) //{ // range.Font.Bold = true; //} if (valueType.Trim() == "formula" || valueType.Trim() == "formula_int" || valueType.Trim() == "formula_decimal" || valueType.Trim() == "formula_decimal_border" || valueType.Trim() == "formula_int_border") { string formula = valueColumn; range.Formula = formula; range.Calculate(); } else { if (valueType.Trim() == "int") { int[] intArray = new int[] { int.Parse(valueColumn_numeric.ToString()) }; range.Value = intArray; } else if (datatype == "numeric") { float[] floatArray = new float[] { valueColumn_numeric }; range.Value = floatArray; } else { valueColumn = valueColumn.TrimEnd(new char[] { '\r', '\n' }); string[] stringArray = new string[] { valueColumn }; range.Value = stringArray; } } } //end row dttenor //end sheet tenor #endregion #region sheet reputasi //sheet reputasi string sheetReputasiName = dtsheet.Rows[j][1].ToString(); xlWorkSheetReputasi = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBookReputasi.Worksheets.Add(misValue, misValue, misValue, misValue); xlWorkSheetReputasi.Name = sheetReputasiName; //xlWorkSheetReputasi.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //xlWorkSheetReputasi.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //xlWorkSheetReputasi.PageSetup.LeftMargin = 0.1; //xlWorkSheetReputasi.PageSetup.RightMargin = 0.1; //xlWorkSheetReputasi.PageSetup.TopMargin = 0.1; //xlWorkSheetReputasi.PageSetup.BottomMargin = 0.1; //xlWorkSheetReputasi.PageSetup.HeaderMargin = 0.1; //xlWorkSheetReputasi.PageSetup.FooterMargin = 0.1; ((Excel.Range)xlWorkSheetReputasi.Cells[1, 1]).EntireColumn.ColumnWidth = 3; ((Excel.Range)xlWorkSheetReputasi.Cells[1, 2]).EntireColumn.ColumnWidth = 20; ((Excel.Range)xlWorkSheetReputasi.Cells[1, 3]).EntireColumn.ColumnWidth = 20; ((Excel.Range)xlWorkSheetReputasi.Cells[1, 4]).EntireColumn.ColumnWidth = 10; ((Excel.Range)xlWorkSheetReputasi.Cells[1, 5]).EntireColumn.ColumnWidth = 10; ((Excel.Range)xlWorkSheetReputasi.Cells[1, 6]).EntireColumn.ColumnWidth = 12; ((Excel.Range)xlWorkSheetReputasi.Cells[1, 7]).EntireColumn.ColumnWidth = 10; //get sheet content object[] paramReputasi = { reffnumber, appid }; DataTable dtReputasi = conn.GetDataTable("EXEC sp_vw_excel_reputasi @1,@2", paramReputasi, dbtimeout); //row dtreputasi for (int i = 0; i <= dtReputasi.Rows.Count - 1; i++) { int startRow = int.Parse(dtReputasi.Rows[i][0].ToString()); int startColumn = int.Parse(dtReputasi.Rows[i][1].ToString()); int EndRow = int.Parse(dtReputasi.Rows[i][2].ToString()); int EndColumn = int.Parse(dtReputasi.Rows[i][3].ToString()); if (EndRow == 0 || EndColumn == 0) { EndRow = startRow; EndColumn = startColumn; } bool isMerge = bool.Parse(dtReputasi.Rows[i][4].ToString()); string bgColor = dtReputasi.Rows[i][5].ToString(); bool isbold = bool.Parse(dtReputasi.Rows[i][6].ToString()); string valueType = dtReputasi.Rows[i][7].ToString(); string valueColumn = dtReputasi.Rows[i][8].ToString(); float valueColumn_numeric = float.Parse(dtReputasi.Rows[i][9].ToString()); string datatype = dtReputasi.Rows[i][10].ToString(); //Excel.Range range = (Excel.Range)xlWorkSheetReputasi.Cells[i + 2, j + 1]; Excel.Range range = (Excel.Range)xlWorkSheetReputasi.Range[xlWorkSheetReputasi.Cells[startRow, startColumn], xlWorkSheetReputasi.Cells[EndRow, EndColumn]]; //range.ColumnWidth = 7; if (startColumn == 2) { range.Cells.WrapText = true; } else { range.Cells.WrapText = false; } if (valueType.Trim() == "int" || valueType.Trim() == "formula_int" || valueType.Trim() == "formula_int_border") { range.NumberFormat = "0"; } else if (valueType.Trim() == "formula_decimal" || valueType.Trim() == "formula_decimal_border") { //range.NumberFormat = "#,##0.00"; range.NumberFormat = "0.00%"; } else if (valueType.Trim() == "date" || valueType.Trim() == "date_border") { range.NumberFormat = "mm/dd/yyyy"; } else if (datatype == "string" && valueType.Trim() != "formula") { range.NumberFormat = "@"; } else if (datatype == "numeric" || valueType.Trim() == "formula") { range.NumberFormat = "Rp #,##0.00"; } if (valueType.Trim() == "text_center") { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignJustify; range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify; } if (isMerge) { range.Merge(); } //if (valueType.Trim().Contains("border")) //{ // range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; // range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; // range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; // range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; // //range.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = 1d; // //range.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = 1d; // //range.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 1d; // //range.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 1d; //} //bgColor if (bgColor == "Old Green") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); } else if (bgColor == "Pink") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Moccasin); } else if (bgColor == "Yellow") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow); } else if (bgColor == "Young Green") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Honeydew); } else if (bgColor == "blue") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSteelBlue); } else { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); } ////isbold //if (isbold) //{ // range.Font.Bold = true; //} if (valueType.Trim() == "formula" || valueType.Trim() == "formula_int" || valueType.Trim() == "formula_decimal" || valueType.Trim() == "formula_decimal_border" || valueType.Trim() == "formula_int_border") { string formula = valueColumn; range.Formula = formula; range.Calculate(); } else { if (valueType.Trim() == "int") { int[] intArray = new int[] { int.Parse(valueColumn_numeric.ToString()) }; range.Value = intArray; } else if (datatype == "numeric") { float[] floatArray = new float[] { valueColumn_numeric }; range.Value = floatArray; } else { valueColumn = valueColumn.TrimEnd(new char[] { '\r', '\n' }); string[] stringArray = new string[] { valueColumn }; range.Value = stringArray; } } } //end row dtreputasi //sheet reputasi #endregion } //end looping sheet #endregion #region sheet laporan //laporan string sheetlaporanName = "Laporan"; xlWorkSheetLaporan = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBookReputasi.Worksheets.Add(misValue, misValue, misValue, misValue); xlWorkSheetLaporan.Name = sheetlaporanName; //xlWorkSheetLaporan.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //xlWorkSheetLaporan.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //xlWorkSheetLaporan.PageSetup.LeftMargin = 0.1; //xlWorkSheetLaporan.PageSetup.RightMargin = 0.1; //xlWorkSheetLaporan.PageSetup.TopMargin = 0.1; //xlWorkSheetLaporan.PageSetup.BottomMargin = 0.1; //xlWorkSheetLaporan.PageSetup.HeaderMargin = 0.1; //xlWorkSheetLaporan.PageSetup.FooterMargin = 0.1; //get sheet content object[] paramLaporan = { reffnumber }; DataTable dtLaporan = conn.GetDataTable("EXEC sp_vw_excel_laporan @1", paramLaporan, dbtimeout); //row dtLaporan for (int i = 0; i <= dtLaporan.Rows.Count - 1; i++) { int startRow = int.Parse(dtLaporan.Rows[i][0].ToString()); int startColumn = int.Parse(dtLaporan.Rows[i][1].ToString()); int EndRow = int.Parse(dtLaporan.Rows[i][2].ToString()); int EndColumn = int.Parse(dtLaporan.Rows[i][3].ToString()); if (EndRow == 0 || EndColumn == 0) { EndRow = startRow; EndColumn = startColumn; } bool isMerge = bool.Parse(dtLaporan.Rows[i][4].ToString()); string bgColor = dtLaporan.Rows[i][5].ToString(); bool isbold = bool.Parse(dtLaporan.Rows[i][6].ToString()); string valueType = dtLaporan.Rows[i][7].ToString(); string valueColumn = dtLaporan.Rows[i][8].ToString(); float valueColumn_numeric = float.Parse(dtLaporan.Rows[i][9].ToString()); string datatype = dtLaporan.Rows[i][10].ToString(); //Excel.Range range = (Excel.Range)xlWorkSheetReputasi.Cells[i + 2, j + 1]; Excel.Range range = (Excel.Range)xlWorkSheetLaporan.Range[xlWorkSheetLaporan.Cells[startRow, startColumn], xlWorkSheetLaporan.Cells[EndRow, EndColumn]]; range.ColumnWidth = 7; //range.Cells.WrapText = false; if (valueType.Trim() == "int" || valueType.Trim() == "formula_int" || valueType.Trim() == "formula_int_border") { range.NumberFormat = "0"; } else if (valueType.Trim() == "formula_decimal" || valueType.Trim() == "formula_decimal_border") { //range.NumberFormat = "#,##0.00"; range.NumberFormat = "0.00%"; } else if (valueType.Trim() == "date" || valueType.Trim() == "date_border") { range.NumberFormat = "mm/dd/yyyy"; } else if (datatype == "string" && valueType.Trim() != "formula") { range.NumberFormat = "@"; } else if (datatype == "numeric" || valueType.Trim() == "formula") { range.NumberFormat = "Rp #,##0.00"; } if (isMerge) { range.Merge(); } if (startColumn == 3) { range.Cells.WrapText = true; } //if (valueType.Trim() == "text_center") //{ // range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //} if (valueType.Trim() == "image") { string sFile = "logoreport.jpg"; string imagString = AppDomain.CurrentDomain.BaseDirectory + "\\image\\" + sFile; float Left = (float)((double)range.Left) - 40; float Top = (float)((double)range.Top) + 5; const float ImageSizeX = 140; const float ImageSizeY = 50; xlWorkSheetLaporan.Shapes.AddPicture(imagString, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSizeX, ImageSizeY); } //if (valueType.Trim().Contains("border")) //{ // range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; // range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; // range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; // range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; // //range.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = 1d; // //range.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = 1d; // //range.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 1d; // //range.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 1d; //} //bgColor if (bgColor == "Old Green") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); } else if (bgColor == "Pink") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Moccasin); } else if (bgColor == "Yellow") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow); } else if (bgColor == "Young Green") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Honeydew); } else if (bgColor == "blue") { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSteelBlue); } else { range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); } ////isbold //if (isbold) //{ // range.Font.Bold = true; //} if (valueType.Trim() == "formula" || valueType.Trim() == "formula_int" || valueType.Trim() == "formula_decimal" || valueType.Trim() == "formula_decimal_border" || valueType.Trim() == "formula_int_border") { string formula = valueColumn; range.Formula = formula; range.Calculate(); } else { if (valueType.Trim() == "int") { int[] intArray = new int[] { int.Parse(valueColumn_numeric.ToString()) }; range.Value = intArray; } else if (datatype == "numeric") { float[] floatArray = new float[] { valueColumn_numeric }; range.Value = floatArray; } else { valueColumn = valueColumn.TrimEnd(new char[] { '\r', '\n' }); string[] stringArray = new string[] { valueColumn }; range.Value = stringArray; } } } //end row dtLaporan //end laporan #endregion #endregion #region end excel string filename = reffnumber + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; string flnm = AppDomain.CurrentDomain.BaseDirectory + "Download\\excel\\" + filename; msg = flnm; xlApp.DisplayAlerts = false; xlWorkBookReputasi.CheckCompatibility = false; xlWorkBookReputasi.DoNotPromptForConvert = true; xlWorkBookReputasi.SaveAs(flnm, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBookReputasi.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkBookReputasi); releaseObject(xlApp); mainPanel.JSProperties["cp_redirect"] = "../Download/excel/" + filename; //mainPanel.JSProperties["cp_target"] = "_blank"; //mainPanel.JSProperties["cp_alert"] = "Export Excel Success " + msg; #endregion } catch (Exception ex) { mainPanel.JSProperties["cp_alert"] = ex.Message; } }