public void BuildBorder(Range aRange)
{
Microsoft.Office.Interop.Excel.Borders border = aRange.Borders;
border[XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
private void BorderCellRange(Microsoft.Office.Interop.Excel.Range excelCellrange)
{
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
// Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeLeft].LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeTop].LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeRight].LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
public static void CreateExcel()
{
Excel.Application excel;
Excel.Workbook worKbooK;
Excel.Worksheet worKsheeT;
Excel.Range celLrangE;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
worKbooK = excel.Workbooks.Add(Type.Missing);
worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
worKsheeT.Name = "Redirects";
worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 8]].Merge();
worKsheeT.Cells[1, 1] = "Redirects";
worKsheeT.Cells.Font.Size = 15;
int rowcount = 2;
foreach (DataRow datarow in redirectsTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= redirectsTable.Columns.Count; i++)
{
if (rowcount == 3)
{
worKsheeT.Cells[2, i] = redirectsTable.Columns[i - 1].ColumnName;
}
worKsheeT.Cells[rowcount, i] = datarow[i - 1].ToString();
if (rowcount > 3)
{
if (i == redirectsTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
celLrangE = worKsheeT.Range[worKsheeT.Cells[rowcount, 1], worKsheeT.Cells[rowcount, redirectsTable.Columns.Count]];
}
}
}
}
}
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[rowcount, redirectsTable.Columns.Count]];
celLrangE.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[2, redirectsTable.Columns.Count]];
worKbooK.SaveAs("Dynamics Page Redirects");
worKbooK.Close();
excel.Quit();
}
catch (Exception ex)
{
}
finally
{
worKsheeT = null;
celLrangE = null;
worKbooK = null;
}
}
public bool WriteDataTableToExcel(DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
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
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
//excel.Visible = false;
//excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = ReporType;
excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();
// loop through each row and add values to our sheet
int rowcount = 2;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 3)
{
excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 3)
{
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);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(saveAsLocation);
excelworkBook.Close();
excel.Quit();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
public static void ExportToExcel(int date, string ruangan)
{
initInputDataTable(date, ruangan);
FolderBrowserDialog folderFD = new FolderBrowserDialog();
Excel.Application exl;
Excel.Workbook exlWorkBook;
Excel.Worksheet exlSheet;
Excel.Range exlRange;
string sheetName = "Rekapitulasi_" + ruangan + "_" + date.ToString();
string filename;
folderFD.SelectedPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
folderFD.ShowNewFolderButton = true;
if (folderFD.ShowDialog() == DialogResult.OK)
{
exl = new Microsoft.Office.Interop.Excel.Application();
exl.Visible = false;
exl.DisplayAlerts = false;
exlWorkBook = exl.Workbooks.Add(Type.Missing);
exlSheet = (Excel.Worksheet)exlWorkBook.ActiveSheet;
exlSheet.Name = sheetName;
int rowCount = 1;
exlSheet.Cells[1, 1] = "Data Rekapitulasi " + ruangan;
exlSheet.Cells[1, 2] = "Tahun: " + date;
rowCount++;
for (int i = 0; i < dtRekap.Columns.Count; i++)
{
exlSheet.Cells[rowCount, i + 3] = dtRekap.Columns[i].ColumnName;
}
rowCount++;
for (int j = 0; j < dtRekap.Rows.Count; j++)
{
for (int k = 0; k < dtRekap.Columns.Count; k++)
{
exlSheet.Cells[rowCount, k + 3] = dtRekap.Rows[j].ItemArray[k].ToString();
}
rowCount++;
}
exlSheet.Cells[rowCount + 1, 1] = "Data Indikator";
rowCount += 2;
for (int i = 0; i < dtIndi.Columns.Count; i++)
{
exlSheet.Cells[rowCount, i + 3] = dtIndi.Columns[i].ColumnName;
}
rowCount++;
for (int j = 0; j < dtIndi.Rows.Count; j++)
{
for (int k = 0; k < dtIndi.Columns.Count; k++)
{
exlSheet.Cells[rowCount, k + 3] = dtIndi.Rows[j].ItemArray[k].ToString();
}
rowCount++;
}
exlRange = exlSheet.Range[exlSheet.Cells[2, 1], exlSheet.Cells[rowCount, dtRekap.Columns.Count]];
exlRange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = exlRange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
filename = folderFD.SelectedPath + @"\Rekapitulasi_" + ruangan + "_" + date;
exlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing,
Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlUserResolution, true,
Type.Missing, Type.Missing, Type.Missing);
exlWorkBook.Close();
exl.Quit();
MessageBox.Show("Table telah diekspor ke Excel!", "Export to Excel", MessageBoxButtons.OK);
}
}
