1、创建DataChangeExcel方法文章来源:https://www.toymoban.com/news/detail-577816.html
public class DataChangeExcel
{
public static void DataSetToExcel(DataTable dataTable, string SaveFile)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel._Workbook workBook;
Microsoft.Office.Interop.Excel._Worksheet workSheet;
object misValue = System.Reflection.Missing.Value;
excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
workBook = excel.Workbooks.Add(misValue);
workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet;
int rowIndex = 1;
int colIndex = 0;
//取得标题
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
//取得表格中的数据
foreach (DataRow row in dataTable.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim().Insert(0,"'");
//设置表格内容居中对齐
workSheet.get_Range(excel.Cells[rowIndex, colIndex],
excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
}
excel.Visible = false;
workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
dataTable = null;
workBook.Close(true, misValue, misValue);
excel.Quit();
PublicMethod.kill(excel);//调用kill当前excel进程
releaseObject(workSheet);
releaseObject(workBook);
releaseObject(excel);
}
private static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch
{
obj = null;
}
finally
{
GC.Collect();
}
}
public class PublicMethod
{
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public static void kill(Microsoft.Office.Interop.Excel.Application excel)
{
try
{
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
catch
{
}
}
}
}
2、进行方法调用文章来源地址https://www.toymoban.com/news/detail-577816.html
public class DataBaseHelper
{
public static DataTable ExecuterQuery(string connectionString, string commandSql)
{
DataTable dataTable = new DataTable();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlDataAdapter adapter = new SqlDataAdapter(commandSql, conn))
{
adapter.Fill(dataTable);
}
conn.Close();
}
}
catch
{
return null;
}
return dataTable;
}
}
public class DataBaseDao
{
public static DataTable GetDataBaseTable()
{
string sql = "select POC_SN,POC_ADDRESS,POC_LAT,POC_LNG,METER_NO,DLMSPASSWORD,SIM_NO,CUSTOMER_NO,CUSTOMER_NAME,[1P/3P],GARDU from Excel1";
return DataBaseHelper.ExecuterQuery("server=172.16.100.59;DataBase=Load;uid=sa;pwd=123456",sql);
}
}
//生成Excel,path为文件存放路径
DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(), path);
到了这里,关于C#将数据库数据导出为Excel的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!