public static void XlsUtil(DataTable table) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = true; for (int i = 0; i < table.Columns.Count; i++) { excel.Cells[1, i + 1] = table.Columns[i].Caption; } if (table.Rows.Count > 0) { for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < table.Columns.Count; j++) { if (table.Columns[j].ColumnName == "正味價格" || table.Columns[j].ColumnName == "納期") { string str = table.Rows[i][j].ToString(); excel.Cells[i + 2, j + 1] = str; } else { string str = "'" + table.Rows[i][j].ToString(); excel.Cells[i + 2, j + 1] = str; } } } } Microsoft.Office.Interop.Excel.Range oRange = excel.get_Range("A1", "A1"); oRange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Range iRange = excel.get_Range("A1", "A1"); iRange.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; }
public static void ExportDatagridview(DataGridView dgv) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = true; for (int i = 0; i < dgv.Columns.Count; i++) { if (dgv.Columns[i].Visible == true) { excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText; } } if (dgv.Rows.Count > 0) { for (int i = 0; i < dgv.Rows.Count; i++) { for (int j = 0; j < dgv.Columns.Count; j++) { if (dgv.Columns[j].Visible != false) { string str = "'" + dgv.Rows[i].Cells[j].Value.ToString(); excel.Cells[i + 2, j + 1] = str; } } } } Microsoft.Office.Interop.Excel.Range oRange = excel.get_Range("A1", "A1"); oRange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Range iRange = excel.get_Range("A1", "A1"); iRange.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; }
private void Display(dynamic d, Microsoft.Office.Interop.Excel.Range cell, Boolean isHomes) { List <object> a = d.collection; FormProgress bar = new FormProgress(); bar.bar.Maximum = a.Count; //bar.bar.Step = 1; bar.Show(); var fsdfsdf = (a[0] as RentMetrics.DynamicJsonConverter.DynamicJsonObject).ToDic(); Int32 _row = cell.Row, _col = cell.Column; foreach (var k in fsdfsdf) { if (k.Key == "neighborhood") { continue; } SortOrder.Add(k.Key); if (k.Key != "latest_prices") { Globals.ThisAddIn.Application.Cells[_row, _col++].Value = k.Key.Replace("_", " "); } else { Globals.ThisAddIn.Application.Cells[_row, _col++].Value = "bedrooms"; Globals.ThisAddIn.Application.Cells[_row, _col++].Value = "full bathrooms"; Globals.ThisAddIn.Application.Cells[_row, _col++].Value = "partial bathrooms"; Globals.ThisAddIn.Application.Cells[_row, _col++].Value = "sq ft"; Globals.ThisAddIn.Application.Cells[_row, _col++].Value = "rent"; Globals.ThisAddIn.Application.Cells[_row, _col++].Value = "rent per sq ft"; Globals.ThisAddIn.Application.Cells[_row, _col++].Value = "concession type"; Globals.ThisAddIn.Application.Cells[_row, _col++].Value = "concession value"; Globals.ThisAddIn.Application.Cells[_row, _col++].Value = "eff rent"; Globals.ThisAddIn.Application.Cells[_row, _col++].Value = "eff rent per sq ft"; Globals.ThisAddIn.Application.Cells[_row, _col++].Value = "rent posted date"; } } for (int i = 0; i < a.Count; i++) { bar.bar.PerformStep(); _row++; _col = cell.Column; var tmp = (a[i] as RentMetrics.DynamicJsonConverter.DynamicJsonObject).ToDic(); ArrayList s = new ArrayList(); foreach (var k in tmp) { if (!isHomes) { if (SortOrder.IndexOf(k.Key) > SortOrder.IndexOf("sq_ft_lot")) { _col = SortOrder.IndexOf(k.Key) - 1; } else if (SortOrder.IndexOf(k.Key) > SortOrder.IndexOf("latest_prices")) { _col = SortOrder.IndexOf(k.Key); } else { _col = SortOrder.IndexOf(k.Key) + 1; } } if (k.Value == null && (SortOrder.IndexOf(k.Key) != SortOrder.Count - 1)) { _col++; continue; } else if (k.Key == "latest_prices") { s = k.Value as ArrayList; _col++; } else if (k.Value is ArrayList) { var strings = (k.Value as ArrayList).Cast <string>().ToArray(); Globals.ThisAddIn.Application.Cells[_row, _col++].Value = String.Join(", ", strings); } else if (SortOrder.IndexOf(k.Key) == SortOrder.Count - 1) { //Globals.ThisAddIn.Application.Cells[_row, _col++].Value = FormatResponse(k); Int32 MaxCol = _col + 1; for (int j = 0; j < s.Count; j++) // for each latest_price { var ks = (s[j] as Dictionary <String, Object>).Keys; if (j > 0) { _row++; for (var t = 1; t <= MaxCol; t++) { if (!ks.Contains(SortOrder.AtIndex(t - 1))) { Globals.ThisAddIn.Application.Cells[_row, t].Value = Globals.ThisAddIn.Application.Cells[_row - 1, t].Value; } } } foreach (var t in (s[j] as Dictionary <String, Object>)) { if (t.Value == null) { continue; } Globals.ThisAddIn.Application.Cells[_row, SortOrder.IndexOf(t.Key)].Value = FormatResponse(t); } } } else { if (k.Key == "rent_posted_date") { Globals.ThisAddIn.Application.Cells[_row, _col].NumberFormat = "mm/dd/yyyy"; } Globals.ThisAddIn.Application.Cells[_row, _col++].Value = FormatResponse(k); } } } Globals.ThisAddIn.Application.ActiveWindow.SplitRow = 1; Globals.ThisAddIn.Application.ActiveWindow.FreezePanes = true; Microsoft.Office.Interop.Excel.Range firstRow = (Microsoft.Office.Interop.Excel.Range)Globals.ThisAddIn.Application.Rows[1]; firstRow.Select(); firstRow.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); Globals.ThisAddIn.Application.Columns.AutoFit(); bar.Hide(); }
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReportType) { Microsoft.Office.Interop.Excel.Application excel; Microsoft.Office.Interop.Excel.Workbook excelworkBook; Microsoft.Office.Interop.Excel.Worksheet excelSheet; Microsoft.Office.Interop.Excel.Range excelCellrange; try { excel = new Microsoft.Office.Interop.Excel.Application(); // Start Excel and get Application object. excel.Visible = false; // for making Excel visible excel.DisplayAlerts = false; excelworkBook = excel.Workbooks.Add(Type.Missing); // Creation a new Workbook excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet; // Workk sheet excelSheet.Name = worksheetName; excelSheet.Cells[1, 1] = ReportType; System.Drawing.ColorConverter cc = new System.Drawing.ColorConverter(); if (dataTable.Columns.Count == 14) { excelSheet.get_Range("A1", "N1").Merge(Type.Missing); excelSheet.get_Range("A1", "N1").Font.Size = 20; excelSheet.get_Range("A1", "N1").Font.Bold = true; excelSheet.get_Range("A1", "N1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excelSheet.get_Range("A1", "N1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excelCellrange = excelSheet.get_Range("A1", "N1"); excelCellrange.RowHeight = 25; int rowcount = 2; // loop through each row and add values to our sheet foreach (DataRow datarow in dataTable.Rows) { rowcount += 1; for (int i = 1; i <= dataTable.Columns.Count; i++) { if (rowcount == 3) // on the first iteration we add the column headers { excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName; excelCellrange.RowHeight = 25; excelSheet.Cells.Font.Color = System.Drawing.Color.Black; } excelCellrange = excelSheet.Range[excelSheet.Cells[2, i], excelSheet.Cells[2, dataTable.Columns.Count]]; FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true); excelSheet.Cells[1, i].Interior.Color = System.Drawing.ColorTranslator.ToOle((System.Drawing.Color)cc.ConvertFromString("#F0F8FF")); // First Cell excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString(); excelCellrange.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; if (rowcount > 3) //for alternate rows { if (i == dataTable.Columns.Count) { if (rowcount % 2 == 0) { excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false); } } } } } excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; // now we resize the columns excelCellrange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; //Namrata: 09/12/2017 excelSheet.EnableAutoFilter = true; //Enable Auto-filter. Microsoft.Office.Interop.Excel.Range range = excelSheet.get_Range("A1", "N1"); //Create the range. range.AutoFilter("1", "<>", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr, "", true); //Auto-filter the range. excelSheet.get_Range("A1", "N1").EntireColumn.AutoFit(); //Auto-fit the second column. } if (dataTable.Columns.Count == 21) { excelSheet.get_Range("A1", "U1").Merge(Type.Missing); excelSheet.get_Range("A1", "U1").Font.Size = 20; excelSheet.get_Range("A1", "U1").Font.Bold = true; excelSheet.get_Range("A1", "U1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excelSheet.get_Range("A1", "U1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excelCellrange = excelSheet.get_Range("A1", "U1"); excelCellrange.RowHeight = 25; int rowcount = 2; // loop through each row and add values to our sheet foreach (DataRow datarow in dataTable.Rows) { rowcount += 1; for (int i = 1; i <= dataTable.Columns.Count; i++) { if (rowcount == 3) // on the first iteration we add the column headers { excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName; excelCellrange.RowHeight = 25; excelSheet.Cells.Font.Color = System.Drawing.Color.Black; } excelCellrange = excelSheet.Range[excelSheet.Cells[2, i], excelSheet.Cells[2, dataTable.Columns.Count]]; FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true); excelSheet.Cells[1, i].Interior.Color = System.Drawing.ColorTranslator.ToOle((System.Drawing.Color)cc.ConvertFromString("#F0F8FF")); // First Cell excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString(); if (rowcount > 3) //for alternate rows { if (i == dataTable.Columns.Count) { if (rowcount % 2 == 0) { excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false); } } } } } excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; // now we resize the columns excelCellrange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; excelCellrange.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //Namrata: 09/12/2017 excelSheet.EnableAutoFilter = true; //Enable Auto-filter. Microsoft.Office.Interop.Excel.Range range = excelSheet.get_Range("A1", "U1"); //Create the range. range.AutoFilter("1", "<>", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr, "", true); //Auto-filter the range. excelSheet.get_Range("A1", "U1").EntireColumn.AutoFit(); //Auto-fit the second column. } if (dataTable.Columns.Count == 13) { excelSheet.get_Range("A1", "M1").Merge(Type.Missing); excelSheet.get_Range("A1", "M1").Font.Size = 20; excelSheet.get_Range("A1", "M1").Font.Bold = true; excelSheet.get_Range("A1", "M1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excelSheet.get_Range("A1", "M1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excelCellrange = excelSheet.get_Range("A1", "M1"); excelCellrange.RowHeight = 25; int rowcount = 2; // loop through each row and add values to our sheet foreach (DataRow datarow in dataTable.Rows) { rowcount += 1; for (int i = 1; i <= dataTable.Columns.Count; i++) { if (rowcount == 3) // on the first iteration we add the column headers { excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName; excelCellrange.RowHeight = 25; excelSheet.Cells.Font.Color = System.Drawing.Color.Black; } excelCellrange = excelSheet.Range[excelSheet.Cells[2, i], excelSheet.Cells[2, dataTable.Columns.Count]]; FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true); excelSheet.Cells[1, i].Interior.Color = System.Drawing.ColorTranslator.ToOle((System.Drawing.Color)cc.ConvertFromString("#F0F8FF")); // First Cell excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString(); if (rowcount > 3) //for alternate rows { if (i == dataTable.Columns.Count) { if (rowcount % 2 == 0) { excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false); } } } } } excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; // now we resize the columns excelCellrange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; excelCellrange.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //Namrata: 09/12/2017 excelSheet.EnableAutoFilter = true; //Enable Auto-filter. Microsoft.Office.Interop.Excel.Range range = excelSheet.get_Range("A1", "M1"); //Create the range. range.AutoFilter("1", "<>", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr, "", true); //Auto-filter the range. excelSheet.get_Range("A1", "M1").EntireColumn.AutoFit(); //Auto-fit the second column. } excelworkBook.SaveAs(saveAsLocation); //now save the workbook and exit Excel MessageBox.Show("File Exported Successfully", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information); excelworkBook.Close(); excel.Quit(); return(true); } catch (Exception ex) { MessageBox.Show(ex.Message); return(false); } finally { excelSheet = null; excelCellrange = null; excelworkBook = null; } }