excel.cs

用ADO.net查询数据库

129                 colIndex++;    
130                 excel.Cells[1,colIndex]=col.ColumnName;                
131             }
132 
133             foreach(DataRow row in table.Rows)
134             {
135                 rowIndex++;
136                 colIndex=0;
137                 foreach(DataColumn col in table.Columns)
138                 {
139                     colIndex++;
140                     excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
141                 }
142             }
143             excel.Visible=false;    
144             excel.Sheets[0] = "sss";
145             excel.ActiveWorkbook.SaveAs(strExcelFileName+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
146             
147             
148             //wkbNew.SaveAs strBookName
149 
150 
151             //excel.Save(strExcelFileName);
152             excel.Quit();
153             excel=null;
154             
155             GC.Collect();//垃圾回收
156         }
157         #endregion
158 
159         从XML导入到Dataset#region 从XML导入到Dataset
160 
161         /**//// <summary>
162         /// 从选择的XML文件导入
163         /// </summary>
164         /// <returns>DataSet</returns>
165         public DataSet ImportFromXML()
166         {
167             DataSet ds=new DataSet();
168             System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
169             openFileDlg.DefaultExt="xml";
170             openFileDlg.Filter= "xml文件 (*.xml)|*.xml";
171             if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
172                 try{ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}
173                 catch{}
174             return ds;
175         }
176         /**//// <summary>
177         /// 从指定的XML文件导入
178         /// </summary>
179         /// <param name="strFileName">XML文件名</param>
180         /// <returns></returns>
181         public DataSet ImportFromXML(string strFileName)
182         {
183             if (strFileName=="")
184                 return null;
185             DataSet ds=new DataSet();
186             try{ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);}
187             catch{}
188             return ds;
189         }
190         
191         #endregion
192 
193         从DataSet导出到XML#region 从DataSet导出到XML
194         /**//// <summary>
195         /// 导出指定的XML文件
196         /// </summary>
197         /// <param name="ds">要导出的DataSet</param>
198         /// <param name="strXMLFileName">要导出的XML文件名</param>
199         public void ExportToXML(DataSet ds,string strXMLFileName)
200         {
201             if (ds.Tables.Count==0 || strXMLFileName=="") return;
202             doExportXML(ds,strXMLFileName);
203         }
204         /**//// <summary>
205         /// 导出用户选择的XML文件
206         /// </summary>
207         /// <param name="ds">DataSet</param>
208         public void ExportToXML(DataSet ds)
209         {
210             System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog(); 
211             saveFileDlg.DefaultExt="xml";
212             saveFileDlg.Filter= "xml文件 (*.xml)|*.xml";
213             if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
214                 doExportXML(ds,saveFileDlg.FileName);
215         }
216 
217         /**//// <summary>
218         /// 执行导出
219         /// </summary>
220         /// <param name="ds">要导出的DataSet</param>
221         /// <param name="strExcelFileName">要导出的XML文件名</param>
222         private void doExportXML(DataSet ds,string strXMLFileName)
223         {
224             try
225             {ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}
226             catch(Exception ex)
227             {System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}    
228         }
229 
230         #endregion
231 
232     
233     
234     }
235 
可能用进程的强制删除方法:kill() 
 1 #region "强制结束Word进程(垃圾回收)" 
 2 /// <summary> 
 3 /// 强制结束Word进程(垃圾回收) 
 4 /// </summary> 
 5 public void KillWordProcess() 
 6 { 
 7 int ProceedingCount = 0; 
 8 try 
 9 { 
10 System.Diagnostics.Process [] ProceddingCon = System.Diagnostics.Process.GetProcesses(); 
11 foreach(System.Diagnostics.Process IsProcedding in ProceddingCon) 
12 { 
13 if(IsProcedding.ProcessName.ToUpper() == "WINWORD") 
14 { 
15 ProceedingCount += 1; 
16 IsProcedding.Kill(); 
17 } 
18 } 
19 } 
20 catch(System.Exception err) 
21 { 
22 MessageBox.Show(err.Message + "\r" +"(" + err.Source + ")" + "\r" + err.StackTrace); 
23 } 
24 } 
25 #endregion
	#endregion

#region 代码4
protected void Button1_Click(object sender, EventArgs e) 
{ 
try 
{ 
string fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + "." + MsgBox.getFileLastName(File1.Value); 
File1.PostedFile.SaveAs(System.Web.HttpContext.Current.Server.MapPath("~/Excel/" + fileName)); 

string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Server.MapPath("~/Excel/" + fileName) + ";Extended Properties=Excel 8.0"; 

OleDbConnection thisconnection = new OleDbConnection(conn); 
thisconnection.Open(); 
string Sql = "select DISTINCT(厂牌) from [Sheet1$]"; 
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection); 
DataSet ds = new DataSet(); 
mycommand.Fill(ds); 
for (int i = 0; i < ds.Tables[0].Rows.Count; i++) 
{ 
DataTable dt2 = ConString.QuerySql("select * from protype where name='" + ds.Tables[0].Rows[i]["厂牌"].ToString().Replace("'", "") + "' and languageid='" + Session["language"] + "'").Tables[0]; 
if (dt2.Rows.Count < 1) 
{ 
ConString.ExecuteSql("insert protype (name,languageid)values('" + ds.Tables[0].Rows[i]["厂牌"].ToString().Replace("'", "") + "','" + Session["language"] + "')"); 
} 
} 
try 
{ 
DataTable dt = ConString.QuerySql("select * from protype").Tables[0]; 
for (int i = 0; i < dt.Rows.Count; i++) 
{ 
string name = dt.Rows[i]["name"].ToString(); 
string id = dt.Rows[i]["id"].ToString(); 

string Sql2 = "select * from [Sheet1$] where 厂牌='" + name + "'"; 
OleDbDataAdapter mycommand2 = new OleDbDataAdapter(Sql2, thisconnection); 
DataSet ds2 = new DataSet(); 
mycommand2.Fill(ds2); 
for (int ii = 0; ii < ds2.Tables[0].Rows.Count; ii++) 
{ 
DataTable dt3 = ConString.QuerySql("select * from product where name='" + ds2.Tables[0].Rows[ii]["型号"].ToString() + "'and languageid='" + Session["language"] + "'").Tables[0]; 
if (dt3.Rows.Count < 1) 
{ 
//ConString.ExecuteSql("insert product (type_id,name,num,pice,file1,languageid)values('" + id + "','" + ds2.Tables[0].Rows[ii]["型号"].ToString().Replace("'", "") + "','" + ds2.Tables[0].Rows[ii]["数量"].ToString().Replace("'", "") + "','" + ds2.Tables[0].Rows[ii]["价格"].ToString().Replace("'", "") + "','" + ds2.Tables[0].Rows[ii]["设备描述"].ToString().Replace("'", "") + "','" + Session["language"] + "')"); 
ConString.ExecuteSql("insert product (type_id,name,num,pice,file1,languageid,shows,top_num,fact,img)values('" + id + "','" + ds2.Tables[0].Rows[ii]["型号"].ToString().Replace("'", "") + "','" + ds2.Tables[0].Rows[ii]["数量"].ToString().Replace("'", "") + "','" + ds2.Tables[0].Rows[ii]["价格"].ToString().Replace("'", "") + "','" + ds2.Tables[0].Rows[ii]["设备描述"].ToString().Replace("'", "") + "','" + Session["language"] + "','" + ds2.Tables[0].Rows[ii]["热门"].ToString().Replace("'", "") + "','" + ds2.Tables[0].Rows[ii]["排序"].ToString().Replace("'", "") + "','" + ds2.Tables[0].Rows[ii]["其他厂牌"].ToString().Replace("'", "") + "','" + ds2.Tables[0].Rows[ii]["图片"].ToString().Replace("'", "") + "')"); 
} 
} 
} 
} 
catch(Exception ex) 
{ 
Response.Write(ex.Message); 
Response.End(); 
} 
thisconnection.Close(); 
File.Delete(Server.MapPath("~/Excel/" + fileName)); 
MsgBox.alert("导入产品成功~！"); 
} 
catch(Exception ex) 
{ 
MsgBox.alert(ex.Message); 
} 

} 


	#endregion
#region 代码5
 /**//// <summary>
        /// 将DataSet里所有数据导入Excel
        /// 需要添加COM: Microsoft Excel Object Library
        /// using Excel;
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="ds">需要转存的DataSet</param>
        public static void ExportToExcel(string filePath, DataSet ds)
        {
            object oMissing = System.Reflection.Missing.Value;
            Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
            try
            {
                // 打开Excel文件。以下为Office 2000.
                Excel.Workbook xlWorkbook =
                    xlApp.Workbooks.Open(filePath, oMissing, oMissing,
                                          oMissing, oMissing, oMissing,
                                          oMissing, oMissing, oMissing,
                                          oMissing, oMissing, oMissing,
                                          oMissing);
 
                Excel.Worksheet xlWorksheet;
                // 循环所有DataTable
                for( int i=0; i<ds.Tables.Count; i++ )

{
                    // 添加入一个新的Sheet页
                    xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing);
 
                    // 以TableName作为新加的Sheet页名
                    xlWorksheet.Name = ds.Tables[i].TableName;
 
                    // 取出这个DataTable中的所有值,暂存于stringBuffer中
                    System.Text.StringBuilder stringBuffer = new System.Text.StringBuilder();
 
                    for( int j=0; j<ds.Tables[i].Rows.Count; j++ )
                    {
                        for( int k=0; k<ds.Tables[i].Columns.Count; k++ )
                        {
                                stringBuffer.Append((string)ds.Tables[i].Rows[j][k]);
                            if( k < ds.Tables[i].Columns.Count - 1 )
                                stringBuffer.Append(" ");
                        }
                        stringBuffer.Append(" ");
                    }
                    // 利用系统剪切板
                    System.Windows.Forms.Clipboard.SetDataObject("");
 
System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
 
                    // 选中这个sheet页中的第一个单元格（下标从1开始）
                    ((Excel.Range)xlWorksheet.Cells[1,1]).Select();
 
                    // 将剪切板里的内容粘贴在Sheet中
                    xlWorksheet.Paste(oMissing,oMissing);
 
                    // 清空系统剪切板
                    System.Windows.Forms.Clipboard.SetDataObject("");
                }
 
                // 保存并关闭这个工作簿
                xlWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
                xlWorkbook = null;
            }
            catch(Exception ex)           
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                // 释放COM对象
                xlApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                GC.Collect();
            }
        } 
	#endregion