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