/**/
/// <summary>/// 导出到 Excel 文件/// </summary>/// <param name="fileName">含完整路径</param>/// <param name="dataTable">含字段标题名</param>
public
void
ExpExcel(
string
fileName ,DataTable dataTable)
{ Excel.ApplicationClass apc =new Excel.ApplicationClass(); apc.Visible = false ; Excel.Workbook wkbook = apc.Workbooks.Add( true ) ; Excel.Worksheet wksheet = (Excel.Worksheet)wkbook.ActiveSheet; int rowIndex = 2; int colIndex = 1; wksheet.get_Range(apc.Cells[1,1],apc.Cells[dataTable.Rows.Count,dataTable.Columns.Count]).NumberFormat = "@"; //取得列标题 foreach (DataColumn dc in dataTable.Columns) { colIndex ++; wksheet.Cells[1,colIndex] = dc.ColumnName; } //取得表格中数据 foreach (DataRow dr in dataTable.Rows) { colIndex = 1; foreach (DataColumn dc in dataTable.Columns) { if(dc.DataType == System.Type.GetType("System.DateTime")) { apc.Cells[rowIndex,colIndex] = "'"+(Convert.ToDateTime(dr[dc.ColumnName].ToString())).ToString("yyyy-MM-dd"); } else if(dc.DataType == System.Type.GetType("System.String")) { apc.Cells[rowIndex,colIndex] = "'"+dr[dc.ColumnName].ToString(); } else { apc.Cells[rowIndex,colIndex] = "'"+dr[dc.ColumnName].ToString(); } wksheet.get_Range(apc.Cells[rowIndex,colIndex],apc.Cells[rowIndex,colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; colIndex++; } rowIndex++; } //设置表格样式 wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Interior.ColorIndex = 20; wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Font.ColorIndex = 3; wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Borders.Weight = 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 = null; apc = null; GC.Collect();}
/**/
/// <summary>/// 从Excel导入帐户(逐单元格读取)/// </summary>/// <param name="fileName">完整路径名</param>
public
IList ImpExcel(
string
fileName)
{ IList alExcel = new ArrayList(); UserInfo userInfo = new UserInfo(); Excel.Application app; Excel.Workbooks wbs; Excel.Worksheet ws; app = new Excel.Application(); wbs = app.Workbooks; wbs.Add(fileName); ws= (Excel.Worksheet)app.Worksheets.get_Item(1); int a = ws.Rows.Count; int b = ws.Columns.Count; for ( int i = 2; i < 4; i++) { for ( int j = 1; j < 21; j++) { Excel.Range range = ws.get_Range(app.Cells[i,j],app.Cells[i,j]); range.Select(); alExcel.Add( app.ActiveCell.Text.ToString() ); } } return alExcel;}
/**/
/// <summary>/// 从Excel导入帐户(新建oleDb连接,Excel整表读取,适于无合并单元格时)/// </summary>/// <param name="fileName">完整路径名</param>/// <returns></returns>
public
DataTable ImpExcelDt (
string
fileName)
{ string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0" ; OleDbConnection myConn = new OleDbConnection ( strCon ) ; string strCom = " SELECT * FROM [Sheet1$] " ; myConn.Open ( ) ; OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ; DataSet myDataSet = new DataSet ( ) ; myCommand.Fill ( myDataSet , "[Sheet1$]" ) ; myConn.Close ( ) ; DataTable dtUsers = myDataSet.Tables[0]; return dtUsers;}
dataGrid中显示：DataGrid1.DataMember
=
"
[Sheet1$]
"
;DataGrid1.DataSource
=
myDataSet ;
首先需要添加一个引用：
文件开头增加：
using Microsoft.Office.Interop.Excel;
private void readBt_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application app;
Workbooks wbs;
Worksheet ws;
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wbs.Add(@"d:/DataTableToExcel.xls");
ws = (Worksheet)app.Worksheets.get_Item(2);
int a = ws.Rows.Count;
int b = ws.Columns.Count;
MessageBox.Show(a.ToString() + "|" + b.ToString());
//for (int i = 1; i < 3; i++)
{
for (int j = 1; j <= 15; j++)
{
Range range = ws.get_Range(app.Cells[j, 1], app.Cells[j, 1]);
range.Select();
MessageBox.Show(app.ActiveCell.Text.ToString());
if (app.ActiveCell.Text.Equals("")) break;
range = ws.get_Range(app.Cells[j, 2], app.Cells[j, 2]);
range.Select();
MessageBox.Show(app.ActiveCell.Text.ToString());
}
}
}
private void writeBt_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.ApplicationClass apc = new ApplicationClass();
apc.Visible = false;
Workbook wkbook = apc.Workbooks.Add(true);
Worksheet wksheet = (Worksheet)wkbook.ActiveSheet;
int rowIndex = 2;
int colIndex = 1;
wksheet.get_Range(apc.Cells[1, 1], apc.Cells[5, 3]).NumberFormat = "@";
//取得列标题
for(int i=1;i<=3;i++)
{
wksheet.Cells[1, i] = "C" + i.ToString();
}
//写入行
for (int i = 1; i <= 5; i++)
{
apc.Cells[i, 1] = "L" + i.ToString();
apc.Cells[i, 2] = "L" + i.ToString();
apc.Cells[i, 3] = "L" + i.ToString();
wksheet.get_Range(apc.Cells[i, 1], apc.Cells[i, 3]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
wksheet.get_Range(apc.Cells[i, 2], apc.Cells[i, 3]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
wksheet.get_Range(apc.Cells[i, 3], apc.Cells[i, 3]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
//设置表格样式
wksheet.get_Range(apc.Cells[1, 1], apc.Cells[1, 3]).Interior.ColorIndex = 20;
wksheet.get_Range(apc.Cells[1, 1], apc.Cells[1, 3]).Font.ColorIndex = 3;
wksheet.get_Range(apc.Cells[1, 1], apc.Cells[1, 3]).Borders.Weight = XlBorderWeight.xlThin;
wksheet.get_Range(apc.Cells[1, 1], apc.Cells[5, 3]).Columns.AutoFit();
if (File.Exists(@"d:/test.xls"))
{
File.Delete(@"d:/test.xls");
}
wkbook.SaveAs(@"d:/test.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wkbook.Close(Type.Missing, Type.Missing, Type.Missing);
apc.Quit();
wkbook = null;
apc = null;
}
