作者：c6643e7f36_253 | 来源：互联网 | 2024-10-08 12:01
/**////
/// 导出到 Excel 文件
///
/// 含完整路径
/// 含字段标题名
public void ExpExcel(string fileName ,DataTable dataTable)
{
Excel.ApplicationClass apc &＃61;new Excel.ApplicationClass();
apc.Visible &＃61; false ;
Excel.Workbook wkbook &＃61; apc.Workbooks.Add( true ) ;
Excel.Worksheet wksheet &＃61; (Excel.Worksheet)wkbook.ActiveSheet;
int rowIndex &＃61; 2;
int colIndex &＃61; 1;
wksheet.get_Range(apc.Cells[1,1],apc.Cells[dataTable.Rows.Count,dataTable.Columns.Count]).NumberFormat &＃61; "&＃64;";
//取得列标题
foreach (DataColumn dc in dataTable.Columns)
{
colIndex &＃43;&＃43;;
wksheet.Cells[1,colIndex] &＃61; dc.ColumnName;
}
//取得表格中数据
foreach (DataRow dr in dataTable.Rows)
{
colIndex &＃61; 1;
foreach (DataColumn dc in dataTable.Columns)
{
if(dc.DataType &＃61;&＃61; System.Type.GetType("System.DateTime"))
{
apc.Cells[rowIndex,colIndex] &＃61; "&＃39;"&＃43;(Convert.ToDateTime(dr[dc.ColumnName].ToString())).ToString("yyyy-MM-dd");
}
else
if(dc.DataType &＃61;&＃61; System.Type.GetType("System.String"))
{
apc.Cells[rowIndex,colIndex] &＃61; "&＃39;"&＃43;dr[dc.ColumnName].ToString();
}
else
{
apc.Cells[rowIndex,colIndex] &＃61; "&＃39;"&＃43;dr[dc.ColumnName].ToString();
}
wksheet.get_Range(apc.Cells[rowIndex,colIndex],apc.Cells[rowIndex,colIndex]).HorizontalAlignment &＃61; Excel.XlHAlign.xlHAlignLeft;
colIndex&＃43;&＃43;;
}
rowIndex&＃43;&＃43;;
}
//设置表格样式
wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Interior.ColorIndex &＃61; 20;
wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Font.ColorIndex &＃61; 3;
wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Borders.Weight &＃61; Excel.XlBorderWeight.xlThin;
wksheet.get_Range(apc.Cells[1,1],apc.Cells[dataTable.Rows.Count,dataTable.Columns.Count]).Columns.AutoFit();
if(File.Exists(fileName))
{
File.Delete(fileName);
}
wkbook.SaveAs( fileName ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
wkbook.Close(Type.Missing,Type.Missing,Type.Missing);
apc.Quit();
wkbook &＃61; null;
apc &＃61; null;
GC.Collect();
}
/**////
/// 从Excel导入帐户(逐单元格读取)
///
/// 完整路径名public IList ImpExcel(string fileName)
{
IList alExcel &＃61; new ArrayList();
UserInfo userInfo &＃61; new UserInfo();
Excel.Application app;
Excel.Workbooks wbs;
Excel.Worksheet ws;
app &＃61; new Excel.Application();
wbs &＃61; app.Workbooks;
wbs.Add(fileName);
ws&＃61; (Excel.Worksheet)app.Worksheets.get_Item(1);
int a &＃61; ws.Rows.Count;
int b &＃61; ws.Columns.Count;
for ( int i &＃61; 2; i < 4; i&＃43;&＃43;)
{
for ( int j &＃61; 1; j < 21; j&＃43;&＃43;)
{
Excel.Range range &＃61; ws.get_Range(app.Cells[i,j],app.Cells[i,j]);
range.Select();
alExcel.Add( app.ActiveCell.Text.ToString() );
}
}
return alExcel;
}
/**////
/// 从Excel导入帐户(新建oleDb连接,Excel整表读取,适于无合并单元格时)
///
/// 完整路径名
/// public DataTable ImpExcelDt (string fileName)
{
string strCon &＃61; " Provider &＃61; Microsoft.Jet.OLEDB.4.0 ; Data Source &＃61; " &＃43; fileName &＃43; ";Extended Properties&＃61;Excel 8.0" ;
OleDbConnection myConn &＃61; new OleDbConnection ( strCon ) ;
string strCom &＃61; " SELECT * FROM [Sheet1$] " ;
myConn.Open ( ) ;
OleDbDataAdapter myCommand &＃61; new OleDbDataAdapter ( strCom , myConn ) ;
DataSet myDataSet &＃61; new DataSet ( ) ;
myCommand.Fill ( myDataSet , "[Sheet1$]" ) ;
myConn.Close ( ) ;
DataTable dtUsers &＃61; myDataSet.Tables[0];
return dtUsers;
}
dataGrid中显示&＃xff1a;
DataGrid1.DataMember&＃61; "[Sheet1$]" ;
DataGrid1.DataSource &＃61; myDataSet ;转载于:https://www.cnblogs.com/liuzhixian/articles/851983.html
京公网安备 11010802041100号