| « | August 2025 | » | | 日 | 一 | 二 | 三 | 四 | 五 | 六 | | | | | | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | | | | | | | |
|
公告 |
| 暂无公告... |
| Blog信息 |
|
blog名称:
日志总数:18
评论数量:10
留言数量:1
访问次数:137525
建立时间:2005年11月22日 |
| |
|
 快速导入EXCEL
软件技术
flymcx 发表于 2006/9/5 10:28:03 |
| using System;using System.Data;using System.Windows.Forms;using System.Diagnostics;using Excel;using System.Reflection;
namespace module81 { /// <summary> /// DataToExcel 的摘要说明。 /// </summary> public class FastDataToExcel { public FastDataToExcel() { // // TODO: 在此处添加构造函数逻辑 // }
public void OrinctDataToExcel(System.Data.DataTable datatable,ProgressBar pBar) { Excel.Application xlApp; try { pBar.Value = 0; pBar.Maximum = datatable.Rows.Count+50; //标题 object[] sheetName = {"图书信息"}; object[] colName = {"图书编号","图书名称","图书作者","ISBN","图书价格", "图书类别","出版社名","出版时间","页数","入库时间","图书总数","馆内剩余","存放位置","借书期限","备注"}; //获取保存路径 string saveFileName=""; bool fileSaved=false; SaveFileDialog saveDialog=new SaveFileDialog(); saveDialog.DefaultExt ="xls"; saveDialog.Filter="Excel文件|*.xls"; saveDialog.FileName ="图书信息"; saveDialog.ShowDialog(); saveFileName=saveDialog.FileName; if(saveFileName.IndexOf(":")<0) return; //被点了取消 //初始化excel对象 xlApp=new Excel.Application(); if(xlApp==null) { MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel"); return; } xlApp.Visible = false; Excel.Workbooks wbs = xlApp.Workbooks; Excel.Workbook wb = wbs.Add(Missing.Value); Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1]; pBar.Value += 10; //设置样式 ws.get_Range(ws.Cells[1,1],ws.Cells[1,datatable.Columns.Count-2]).MergeCells=true; ws.get_Range(ws.Cells[1,1],ws.Cells[1,1]).Font.Bold=true; ws.get_Range(ws.Cells[1,1],ws.Cells[1,1]).Font.Size=16; ws.get_Range(ws.Cells[2,1],ws.Cells[2,colName.Length]).Font.Bold=true; ws.get_Range(ws.Cells[2,1],ws.Cells[datatable.Rows.Count+3,datatable.Columns.Count-2]).Font.Size=9; ws.get_Range(ws.Cells[1,1],ws.Cells[datatable.Rows.Count+3,datatable.Columns.Count-2]).Borders.LineStyle=1; ws.get_Range("E1",ws.Cells[datatable.Rows.Count+3,5]).NumberFormatLocal = "￥#,##0.00;￥-#,##0.00"; ws.get_Range("N1",ws.Cells[datatable.Rows.Count+3,datatable.Columns.Count-2]).NumberFormatLocal = "0天"; pBar.Value += 10; //标题 Excel.Range r = ws.get_Range("A1","A1"); r.Value2 = sheetName; //列标题 r = ws.get_Range("A2",Missing.Value); r = r.get_Resize(1,datatable.Columns.Count-2); r.Value2 = colName; pBar.Value += 10; //填充数据 r = ws.get_Range("A3",Missing.Value); object [,] objData = new Object[datatable.Rows.Count+3,datatable.Columns.Count-2]; for(int i=0;i<=datatable.Rows.Count-1;i++) { pBar.Value += 1; for(int k=0;k<datatable.Columns.Count-2;k++) { objData[i,k]=datatable.Rows[i][k+1].ToString(); } } //设置数据 r = r.get_Resize(datatable.Rows.Count+3,datatable.Columns.Count-2); r.Value2 = objData; r.EntireColumn.AutoFit(); pBar.Value += 10; //统计总量 ws.Cells[datatable.Rows.Count+3,1]="总计："; ws.Cells[datatable.Rows.Count+3,2]=datatable.Rows.Count.ToString()+" 册"; pBar.Value += 10; //保存excel if(saveFileName!="") { try { xlApp.Visible=true; wb.Saved=true; fileSaved=true; wb.SaveCopyAs(@saveFileName); } catch(System.Exception ex) { fileSaved=false; MessageBox.Show("导出文件时出错,文件可能正被打开！\n"+ex.Message); } } else { fileSaved=false; } wb.Saved=true; xlApp.Quit(); MessageBox.Show(pBar.FindForm(),"EXCEL文档导出成功！","导出成功",MessageBoxButtons.OK,MessageBoxIcon.Information); } catch(System.Exception error) { MessageBox.Show("Excel导出错误！"+error.Message); } finally { //释放资源 pBar.Value=0; pBar.FindForm().Enabled = true; xlApp=null; KillProcess("EXCEL"); GC.Collect(); } }
public void KillProcess(string processName) { System.Diagnostics.Process myproc= new System.Diagnostics.Process(); //得到所有打开的进程 try { foreach (Process thisproc in Process.GetProcessesByName(processName)) { if(!thisproc.CloseMainWindow()) { thisproc.Kill(); } } } catch(Exception Exc) { } } }} |
|
|
回复:快速导入EXCEL
软件技术
草莓雪糕(游客)发表评论于2008/3/18 22:55:32 |
|
虽然现在知道你不搞软件了，但还是告诉你一个更好的实现方法，以作老朋友的问候！
using System;using System.Data.SqlClient;using System.Data;using System.IO;using System.Drawing.Printing;using System.Windows.Forms; public class ClassOften {
public static string GetExcelTypeName(string TypeName) { switch (TypeName) { case "System.Double": return " double"; // break; case "System.Int32": return " int"; // break; default: return " char(200)"; // break; } }
public static string GetFileName(string FileName) { System.Windows.Forms.SaveFileDialog dlgSave = new System.Windows.Forms.SaveFileDialog(); dlgSave.Filter = "数据库文件(*.xls)|*.xls"; dlgSave.FileName = FileName;
if (dlgSave.ShowDialog() == System.Windows.Forms.DialogResult.OK) { return dlgSave.FileName; } else { return null; } }
public static string GetFileName() { return GetFileName("Excel文档" + System.DateTime.Now.ToString("yyyy-MM-dd")); }
/// <summary> /// 导出Excel /// </summary> public static void EduceExcel(System.Data.DataTable dt) { EduceExcel(dt, GetFileName(), "table", true, true); }
public static void EduceExcel(System.Data.DataTable dt,string FileName,string TableName,bool IsDeleteFile,bool IsTips) { if (FileName == null||FileName == "") { return; }
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileName + ";" + "Extended Properties=Excel 8.0;"; string strExcel = ""; //System.Windows.Forms.MessageBox.Show(Path.GetFileName(dlgSave.FileName));
if (IsDeleteFile == true) { System.IO.File.Delete(FileName); }
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); conn.Open();
try {
strExcel = "create table ["+TableName+"](";
for (int i = 0; i < dt.Columns.Count; i++) { if (i != dt.Columns.Count - 1) { strExcel += dt.Columns[i].ColumnName + GetExcelTypeName(dt.Columns[i].DataType.ToString()) + ","; } else { strExcel += dt.Columns[i].ColumnName + GetExcelTypeName(dt.Columns[i].DataType.ToString()) + ")"; } } System.Data.OleDb.OleDbCommand comd = new System.Data.OleDb.OleDbCommand(strExcel, conn); comd.ExecuteNonQuery(); for (int k = 0; k < dt.Rows.Count; k++) {
strExcel = "insert into ["+TableName+"$]("; for (int i = 0; i < dt.Columns.Count; i++) { if (i != dt.Columns.Count - 1) { strExcel += dt.Columns[i].ColumnName + ","; } else { strExcel += dt.Columns[i].ColumnName + ")"; } } strExcel += " values("; for (int i = 0; i < dt.Columns.Count; i++) { if(dt.Columns[i].DataType.ToString()=="System.Double" ||dt.Columns[i].DataType.ToString()=="System.Int32") { if(dt.Rows[k][i]==DBNull.Value) { strExcel += ClassOften.AddSign("0"); } else { strExcel += ClassOften.AddSign(dt.Rows[k][i].ToString()); } } else { strExcel += ClassOften.AddSign(dt.Rows[k][i].ToString()); }
if (i != dt.Columns.Count - 1) { strExcel +=","; } else { strExcel += ")"; } } comd = new System.Data.OleDb.OleDbCommand(strExcel, conn); comd.ExecuteNonQuery(); } conn.Close(); if (IsTips == true) { System.Windows.Forms.MessageBox.Show("导出成功!"); } } catch (Exception er) { if (IsTips == true) { System.Windows.Forms.MessageBox.Show(er.Message); } conn.Close(); } }
}
还记得ClassOften这个类吗？我现在还在用啊。
现在还会经常想起她，想疯了就百度她的名字，没什么东东，又百度你的，发现了的blog,世界真奇妙！
以下为blog主人的回复：
我不搞软件已经很久！ |
|
» 1 »
|