How to extract data to Microsoft Excel Sheet from database

1.Declaration

MX.Application xl = null;

MX.Workbook wb = null;

MX.Worksheet wsheet = null;

xl = new MX.Application();

wb = Microsoft.Office.Interop.Excel.Workbook)(xl.Workbooks.Add(Missing.Value));

wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

wsheet = (Microsoft.Office.Interop.Excel.Worksheet)(wb.Sheets[1]);

wsheet.Cells.Font.Name = “VERDANA”;

wsheet.Cells.Font.Size = 9;

wsheet.Cells.HorizontalAlignment = MX.XlHAlign.xlHAlignCenter;

wsheet.get_Range(“A1”, “A1”).ColumnWidth = 9;

wsheet.get_Range(“B1”, “B1”).ColumnWidth = 13;

wsheet.get_Range(“C1”, “C1”).ColumnWidth = 28;

wsheet.get_Range(“D1”, “D1”).ColumnWidth = 12;

wsheet.Cells[4, 1] = ” SL NO. “;

wsheet.Cells[4, 2] = ” EMP NO “;

wsheet.Cells[4, 3] = ” REMARKS “;

wsheet.get_Range(“A4”, “J4”).AutoFilter(1, Missing.Value, MX.XlAutoFilterOperator.xlAnd, Missing.Value, true);

2.SQL Query Statement:

cmd.CommandText = “select * from employeedetails”;

cmd.Connection = con; dr = cmd.ExecuteReader();

while (dr.Read())

{

wsheet.Cells[r, 2] = dr[“empno”].ToString();

wsheet.Cells[r, 3] = dr[“empname”].ToString();

}

dr.Close();

3.Formatting and Alignment

String fir = “A” + 1;

String las = “J” + (r – 1).ToString();

wsheet.get_Range(fir, las).Select();

wsheet.get_Range(fir, las).HorizontalAlignment = MX.XlHAlign.xlHAlignCenter; wsheet.get_Range(fir, las).VerticalAlignment = MX.XlVAlign.xlVAlignJustify;
wsheet.get_Range(fir, las).Borders.LineStyle = MX.XlLineStyle.xlContinuous;

wsheet.get_Range(fir, las).Borders.Weight = MX.XlBorderWeight.xlThin;
wsheet.get_Range(fir, las).Borders.ColorIndex = MX.XlColorIndex.xlColorIndexAutomatic;

wsheet.get_Range(fir, las).RowHeight = 20;

wsheet.get_Range(“A4”, “J4”).Interior.ColorIndex = 35;

wsheet.get_Range(“F1”, “F1”).Columns.AutoFit();

wsheet.get_Range(“A1”, “J4”).RowHeight = 22.5;

wsheet.get_Range(“A1”, “J4”).HorizontalAlignment = MX.XlHAlign.xlHAlignCenter;
wsheet.get_Range(“F1”, “F1”).HorizontalAlignment = MX.XlHAlign.xlHAlignLeft;

4.Save Excel File

SaveFileDialog sv = new SaveFileDialog();

sv.FileName = “EMPLOYEEREPORT” + “.xls”;

sv.InitialDirectory = “C:\”; sv.ShowDialog();

wb.SaveAs(sv.FileName, MX.XlFileFormat.xlWorkbookNormal, null, null, null, null, MX.XlSaveAsAccessMode.xlExclusive, null, null, null, null, null);

xl.Quit();

MessageBox.Show(“Report is Generated “, “ABC”, MessageBoxButtons.OK, MessageBoxIcon.Information);

How to copy or duplicate the structure and data of table in the database in MySql

How to use alter command – SQL