1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113
| private void ExportDataSetToExcel(DataSet dataset, string path)
{
int inHeaderLength = 0, inColumn = 0, inRow = 0;
System.Reflection.Missing Default = System.Reflection.Missing.Value;
path = "...";
OfficeExcel.Application eApp = new OfficeExcel.Application();
OfficeExcel.Workbook ewbook = eApp.Workbooks.Add(1);
foreach (DataTable dt in dataset.Tables)
{
OfficeExcel.Worksheet sheet = ewbook.Sheets.Add(Default, ewbook.Sheets[ewbook.Sheets.Count], 1, Default);
sheet.Name = dt.TableName;
for (int i = 0; i < dt.Columns.Count; i++)
sheet.Cells[inHeaderLength + 1, i + 1] = dt.Columns[i].ColumnName;
sheet.Columns.AutoFit();
for (int m = 0; m < dt.Rows.Count; m++)
{
for (int n = 0; n < dt.Columns.Count; n++)
{
inColumn = n + 1;
inRow = inHeaderLength + 2 + m;
sheet.Cells[inRow, inColumn] = dt.Rows[m].ItemArray[n].ToString();
if (m % 2 == 0)
sheet.Cells[inRow, inColumn].Interior.Color = System.Drawing.ColorTranslator.FromHtml("#D9E1F2");
sheet.Cells[1, inColumn].Interior.Color = System.Drawing.ColorTranslator.FromHtml("#1F80C1");
sheet.Cells[1, inColumn].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
sheet.Cells[1, inColumn].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range firstRow = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[1];
firstRow.AutoFilter(1,
Type.Missing,
Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
}
}
sheet.UsedRange.Columns.AutoFit();
}
eApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ewbook.Worksheets[1];
lastWorkSheet.Delete();
eApp.DisplayAlerts = true;
(ewbook.Sheets[1] as OfficeExcel._Worksheet).Activate();
ewbook.SaveAs(path, Default, Default, Default, false, Default, OfficeExcel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default);
ewbook.Close();
eApp.Quit();
System.Diagnostics.Process.Start(path);
}
private DataTable getAllEmployeesList()
{
string constr = ConfigurationManager.ConnectionStrings["constr527"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Test1"))
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
DataTable dt = new DataTable();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
da.Fill(dt);
return dt;
}
}
}
}
private DataTable getAllEmployeesOrderList()
{
string constr = ConfigurationManager.ConnectionStrings["constr527"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Test2"))
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
DataTable dt = new DataTable();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
da.Fill(dt);
return dt;
}
}
}
}
private DataSet getDataSetExportToExcel()
{
DataSet ds = new DataSet();
DataTable dtEmp = new DataTable("Test1");
dtEmp = getAllEmployeesList();
DataTable dtEmpOrder = new DataTable("Test2");
dtEmpOrder = getAllEmployeesOrderList();
ds.Tables.Add(dtEmp);
ds.Tables.Add(dtEmpOrder);
return ds;
} |
Partager