string dd1 =myds.Tables["jiarecordtemp"].DefaultView[j][0].ToString().Trim(); pcmd = new SqlCommand("select top 1 * from Needpepole where workno ='"+myds.Tables["jiarecordtemp"].DefaultView[j]["工號"].ToString().Trim()+"' and dateoccur='"+dd1+"'" );
StringBuilder sb = new StringBuilder(); string sal="declare @d datetime set @d=getdate();"; sb.Append(sal); // string workno=this.Session["user_id"].ToString().Trim();
for(j=0;j<i;j++) {
string Dmstatus=myds.Tables["jiarecordtemp"].DefaultView[j][3].ToString().Trim(); string status; string Dmcomment=myds.Tables["jiarecordtemp"].DefaultView[j][4].ToString().Trim(); string Dmno=myds.Tables["jiarecordtemp"].DefaultView[j]["工號"].ToString().Trim(); string dd =myds.Tables["jiarecordtemp"].DefaultView[j][0].ToString().Trim(); if (Dmstatus!="Y") {status="N"; sal=" update Needpepole set DMstatus='"+Dmstatus+ "', status='"+status+"', Dmcomment='" +Dmcomment+ "', batchID='" +filename+"',Lasteditby='"+u_name+"',lastedittime=@d where workno='"+Dmno+"' and dateoccur='"+dd+"';" ;} else { sal=" update Needpepole set DMstatus='"+Dmstatus+"', status=case when KQstatus='Y' and fabstatus='Y' then 'Y' else 'N' END, Dmcomment='" +Dmcomment+ "', batchID='" +filename+"',Lasteditby='"+u_name+"',lastedittime=@d where workno='"+Dmno+"' and dateoccur='"+dd+"' ; " ; }
sb.Append(sal); sal="insert into CheckRecord(Workno,CheckDate,KQstatus,Comment,Lastedittime,Lasteditby,BatchID) values('"+Dmno+"','" +dd+"','"+Dmstatus+ "','" +Dmcomment+ "',@d,'"+workno+"','"+filename+"');"; sb.Append(sal); } SqlConnection con2 = new SqlConnection(ConnectionString1); SqlCommand cmd = new SqlCommand(); cmd.Connection = con2 ; cmd.CommandTimeout=10000; sb.Append("update Needpepole set dmcomment=codename from TYPEDefine b where dmcomment=cast(b.code as varchar(3)) and TYPE='ETYPE' and batchID='" +filename+ "';" );
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data;
using System.Data.OleDb;namespace xiaoh
{
public class ExcelHelper
{
/// <summary>
/// 写入Excel文档
/// </summary>
/// <param name="xPath">文件名称</param>
/// <param name="source">需要写入的内容</param>
/// <param name="sheetIndex">sheet序列</param>
public static void writeExcel(string xPath, string[][] source, int sheetIndex)
{
Application ExcelRS = new ApplicationClass();
Workbook RSbook = ExcelRS.Workbooks.Open(xPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Worksheet RSsheet = (Worksheet)RSbook.Sheets.get_Item(sheetIndex);
for (int i = 0; i < source.Length; i++)
{
for (int j = 0; j < source[i].Length; j++)
{
RSsheet.Cells[i + 1, j + 1] = source[i][j];
}
}
RSbook.Save();
RSbook.Close();
RSsheet = null;
RSbook = null;
ExcelRS = null;
collect();
} /// <summary>
/// 读取Excel文档
/// </summary>
/// <param name="xPath">文件名称</param>
/// <param name="sheetIndex">sheet序号</param>
/// <returns>返回一个数据集</returns>
public static DataSet readExcel(string xPath, int sheetIndex)
{
Microsoft.Office.Interop.Excel.Application ExcelRS = new ApplicationClass();
Workbook RSbook = ExcelRS.Workbooks.Open(xPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Worksheet RSsheet = (Worksheet)RSbook.Sheets.get_Item(sheetIndex);
string sheetName = RSsheet.Name;
RSbook.Close();
RSsheet = null;
RSbook = null;
ExcelRS = null;
collect();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + xPath + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
try
{
conn.Open();
string strExcel = string.Format("select * from [{0}$]", sheetName);
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();
myCommand.Fill(ds);
return ds;
}
catch
{
return null;
}
finally
{
conn.Close();
}
} private static void collect()
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
我们公司用的是FarPoint第三方控件，这个比较方便。
{
string Todate =System.DateTime.Now.Year.ToString().Trim()+System.DateTime.Now.Month.ToString().Trim().PadLeft(2,'0')+System.DateTime.Now.Day.ToString().Trim().PadLeft(2,'0'); if(this.File1.Value=="")
{
this.RegisterClientScriptBlock("er01","<script language=JavaScript>alert('請選擇一個文件')</script>");
return;
}
string workno=this.Session["user_id"].ToString().Trim();
string filename =System.DateTime.Now.Year.ToString().Trim()+System.DateTime.Now.Month.ToString().Trim().PadLeft(2,'0')+System.DateTime.Now.Day.ToString().Trim().PadLeft(2,'0')+System.DateTime.Now.Hour.ToString().Trim().PadLeft(2,'0')+System.DateTime.Now.Minute.ToString().Trim().PadLeft(2,'0')+System.DateTime.Now.Second.ToString().Trim().PadLeft(2,'0')+System.DateTime.Now.Millisecond.ToString().Trim();
this.File1.PostedFile.SaveAs(@"D:\KQConfirm\data\"+workno+"_"+filename+".xls");
//--------------check data
string filestr =@"D:\KQConfirm\data\"+workno+"_"+filename+".xls";
string con = "Provider =Microsoft.Jet.OLEDB.4.0; Data Source ="+filestr+";Extended Properties = Excel 8.0";
string sql ="select * from [Sheet1$]";
int i=0,j=0; OleDbConnection olecon = new OleDbConnection(con);
OleDbCommand olecmd = new OleDbCommand(sql,olecon);
OleDbDataAdapter oleda = new OleDbDataAdapter(olecmd);
DataSet myds = new DataSet();
try
{
oleda.Fill(myds ,"jiarecordtemp");
}
catch
{ this.RegisterClientScriptBlock("er01","<script language=JavaScript>alert('文件不正確,請檢查!')</script>");
return;
}
oleda.Dispose();
olecmd.Dispose();
olecon.Dispose(); i=myds.Tables["jiarecordtemp"].DefaultView.Count;
DataTable dttemp = myds.Tables["jiarecordtemp"].Clone();
DataColumn dc = new DataColumn("報錯");
dttemp.Columns.Add(dc);
//--------------- Check wno at first
for(j=0;j<i;j++)
{
object[] to =new object[6];
to[0] = myds.Tables["jiarecordtemp"].DefaultView[j][0].ToString().Trim() ;
to[1] = myds.Tables["jiarecordtemp"].DefaultView[j][1].ToString().Trim() ;
to[2] = myds.Tables["jiarecordtemp"].DefaultView[j][2].ToString().Trim() ;
to[3] = myds.Tables["jiarecordtemp"].DefaultView[j][3].ToString().Trim() ;
to[4] = myds.Tables["jiarecordtemp"].DefaultView[j][4].ToString().Trim() ;
if(to[0].ToString()=="")
{ to[5]="這位同仁的點名時間不正確!";
dttemp.Rows.Add(to);
continue;
}
//string str=myds.Tables["jiarecordtemp"].DefaultView[j][0].ToString().Trim();
try
{
DateTime kqdate = DateTime.ParseExact(to[0].ToString(),"yyyyMMdd",null);
DateTime ndate=Convert.ToDateTime(System.DateTime.Now.Date);
System.TimeSpan ND=ndate-kqdate;
int n=ND.Days;
if (n>2)
{
// to[5]="已經超過點名維護時間!";
// dttemp.Rows.Add(to);
// continue;
}
}
catch
{
to[5]="這位同仁的點名時間不正確!";
dttemp.Rows.Add(to);
continue;}
if(myds.Tables["jiarecordtemp"].DefaultView[j]["工號"].ToString().Trim().Length!=6 && myds.Tables["jiarecordtemp"].DefaultView[j]["工號"].ToString().Trim().Length!=7)
{ to[5]="這位同仁的工號不正確!";
dttemp.Rows.Add(to);
continue;
}
if(to[3].ToString()!="N" &&to[3].ToString()!="Y")
{
to[5]="請正確維護點名狀態!";
dttemp.Rows.Add(to);
continue;
}
SqlCommand pcmd = new SqlCommand("select TYPE from dbo.TYPEDefine where type='ETYPE' and code='"+to[4]+"'");
SqlConnection con1 = new SqlConnection(ConnectionString1);
pcmd.Connection = con1 ; pcmd.CommandTimeout = 300000 ;
pcmd.Connection.Open();
SqlDataReader pdr = pcmd.ExecuteReader(CommandBehavior.SingleRow);
if (!pdr.HasRows && to[3].ToString()=="N")
{
to[5]="請按照考勤異常代碼";
dttemp.Rows.Add(to);
//dttemp.Rows.Add( myds.Tables["jiarecordtemp"].Rows[j].ItemArray );
continue;
}
pcmd.Connection.Close();
pdr.Close();
pcmd.Dispose();
string dd1 =myds.Tables["jiarecordtemp"].DefaultView[j][0].ToString().Trim();
pcmd = new SqlCommand("select top 1 * from Needpepole where workno ='"+myds.Tables["jiarecordtemp"].DefaultView[j]["工號"].ToString().Trim()+"' and dateoccur='"+dd1+"'" );
con1 = new SqlConnection(ConnectionString1);
pcmd.Connection = con1 ; pcmd.CommandTimeout = 30000 ;
pcmd.Connection.Open();
pdr = pcmd.ExecuteReader(CommandBehavior.SingleRow);
if (!pdr.HasRows)
{
to[5]="下列紀錄的工號、班別有問題或當日數據尚未產生!";
dttemp.Rows.Add(to);
continue;
}
pcmd.Connection.Close();
pdr.Close();
pcmd.Dispose(); }
if (dttemp.DefaultView.Count!=0)
{
this.DataGrid1.DataSource=dttemp;
this.DataGrid1.DataBind();
this.Label1.Text="下列紀錄的異常!";
this.DataGrid1.Visible=true;
dttemp.Rows.Clear();
return;
}
dttemp.Rows.Clear();
this.DataGrid1.DataSource=dttemp;
this.DataGrid1.DataBind();
this.DataGrid1.Visible=false;
this.Label1.Text=""; //update //string update=System.DateTime.Now.ToString("yyyy-mm-dd hh:mm:ss");
StringBuilder sb = new StringBuilder();
string sal="declare @d datetime set @d=getdate();";
sb.Append(sal);
// string workno=this.Session["user_id"].ToString().Trim();
for(j=0;j<i;j++)
{
string Dmstatus=myds.Tables["jiarecordtemp"].DefaultView[j][3].ToString().Trim();
string status;
string Dmcomment=myds.Tables["jiarecordtemp"].DefaultView[j][4].ToString().Trim();
string Dmno=myds.Tables["jiarecordtemp"].DefaultView[j]["工號"].ToString().Trim();
string dd =myds.Tables["jiarecordtemp"].DefaultView[j][0].ToString().Trim();
if (Dmstatus!="Y")
{status="N";
sal=" update Needpepole set DMstatus='"+Dmstatus+ "', status='"+status+"', Dmcomment='" +Dmcomment+ "', batchID='" +filename+"',Lasteditby='"+u_name+"',lastedittime=@d where workno='"+Dmno+"' and dateoccur='"+dd+"';" ;}
else
{
sal=" update Needpepole set DMstatus='"+Dmstatus+"', status=case when KQstatus='Y' and fabstatus='Y' then 'Y' else 'N' END, Dmcomment='" +Dmcomment+ "', batchID='" +filename+"',Lasteditby='"+u_name+"',lastedittime=@d where workno='"+Dmno+"' and dateoccur='"+dd+"' ; " ; }
sb.Append(sal);
sal="insert into CheckRecord(Workno,CheckDate,KQstatus,Comment,Lastedittime,Lasteditby,BatchID) values('"+Dmno+"','" +dd+"','"+Dmstatus+ "','" +Dmcomment+ "',@d,'"+workno+"','"+filename+"');";
sb.Append(sal);
}
SqlConnection con2 = new SqlConnection(ConnectionString1);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con2 ;
cmd.CommandTimeout=10000;
sb.Append("update Needpepole set dmcomment=codename from TYPEDefine b where dmcomment=cast(b.code as varchar(3)) and TYPE='ETYPE' and batchID='" +filename+ "';" );
cmd.CommandText =sb.ToString();
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
this.RegisterClientScriptBlock("er03","<script language=JavaScript>alert('成功匯入點名!')</script>");
}
代码如下：
ExcelUtil.csusing System;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Collections.Generic;
namespace zMoneyKeeper.business
{
class ExcelUtil
{
/// <summary>
/// 释放资源
/// </summary>
/// <param name="pObj"></param>
public void ReleaseCOM(object pObj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
}
catch
{
throw new Exception("释放资源时发生错误！");
}
finally
{
pObj = null;
}
} public ExcelUtil(string filePath)
{
this.filePath = filePath;
app = new Microsoft.Office.Interop.Excel.Application();//打开一个Excel应用
if (app == null)
{
return;
}
wbs = app.Workbooks;
wb = wbs.Add(filePath);
shs = wb.Sheets;
int sheetNumber = shs.Count;
for (int i = 1; i <= sheetNumber; i++ )
{
_Worksheet sh = (_Worksheet)shs.get_Item(i);
this.ShList.Add(sh);
}
} /// <summary>
/// 保存文件
/// </summary>
public void save()
{
if (filePath != null)
{
FileInfo old = new FileInfo(filePath);
if(old.Exists)
{
File.Delete(filePath);
} wb.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
foreach (_Worksheet inst in shList)
{
ReleaseCOM(inst);
}
ReleaseCOM(shs);
ReleaseCOM(wb);
ReleaseCOM(wbs);
app.Quit();
ReleaseCOM(app);
}
}
/// <summary>
/// 杀掉进程的方式保存excel。
/// 用来解决正常保存时出现的公式异常问题。
/// </summary>
public void saveByKillProcess()
{
if (filePath != null)
{
FileInfo old = new FileInfo(filePath);
if (old.Exists)
{
File.Delete(filePath);
}
wb.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
foreach (_Worksheet inst in shList)
{
ReleaseCOM(inst);
}
ReleaseCOM(shs);
ReleaseCOM(wb);
ReleaseCOM(wbs);
KillExcel(app);
}
} [DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
private void KillExcel(Microsoft.Office.Interop.Excel.Application excel)
{
IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口 int k = 0;
GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
p.Kill(); //关闭进程k
} private Microsoft.Office.Interop.Excel.Application app;
private Workbooks wbs;
private _Workbook wb;
private Sheets shs; private List<_Worksheet> shList = new List<_Worksheet>();
public List<_Worksheet> ShList
{
get { return shList; }
set { shList = value; }
}
private string filePath;
}
}调用示例： ExcelUtil eu = null;
private bool exportDailyDetail(string dateStr)
{
bool ret = false;
try
{
int index = 2;
foreach (InOutRecordEntity inst in recordList)
{
eu.ShList[3].get_Range("A" + index, Type.Missing).Value2 = inst.Date;
eu.ShList[3].get_Range("B" + index, Type.Missing).Value2 = inst.InOrOut;
if (inst.InOrOut.Equals("收入"))
{
eu.ShList[3].get_Range("C" + index, Type.Missing).Value2 = inst.InType.Name;
}
else
{
eu.ShList[3].get_Range("D" + index, Type.Missing).Value2 = inst.OutType.Name;
}
eu.ShList[3].get_Range("E" + index, Type.Missing).Value2 = inst.MoneyType.Name;
eu.ShList[3].get_Range("F" + index, Type.Missing).Value2 = inst.Amount;
eu.ShList[3].get_Range("G" + index, Type.Missing).Value2 = inst.Comment;
index++;
}
ret = true;
}
catch (Exception ex)
{
System.Console.WriteLine(ex.Message);
ret = false;
}
return ret;
}
public bool exportMonth(String dateStr, string exportFile)
{
try
{
File.Copy(this.moduleFile, exportFile, true);
}
catch (System.Exception e)
{
Console.WriteLine(e.Message);
}
eu = new ExcelUtil(exportFile);
InOutRecordBusiness busi = new InOutRecordBusiness();
this.recordList = busi.queryByDate(dateStr);
this.exportDailyDetail(dateStr);
this.exportSum(dateStr);
this.exportInCome();
this.exportOutCome();
eu.save();
return true;
}
这是我写的小程序里面摘出来的，你凑合着看吧。