|
最近一段时间，由于工作需要，需要做数据库与Excel 的导入导出，由于以前没接触过，有点难煞我也。
迎难直上，查证N多资料，终于做出最稳定，最方便的解决方案，封装为类，方便自己以后使用查看，附代码。
个人认为比较适合基础较差的开发同志，涉及的知识点很少，属于一看就懂的那种，你懂的。
废话少说，看代码：
excel导入到sql server 数据库：
View Code
1 using System;
2 using System.Data;
3 using System.Data.SqlClient;
4 using TongLu.DatasInfo.DataLayer;
5 using Sql = TongLu.PMethod.Sql;
6 using SqlProc = TongLu.SqlProc;
7 using System.Data.OleDb;
8 using System.Collections;
9
10 namespace TongLu.DatasInfo.DataLayer.DBHelper
11 {
12 public class ExcelOperation
13 {
14 /// <summary>
15 /// 加载Excel
16 /// </summary>
17 /// <param name="filePath">本地完整路径</param>
18 /// <param name="tableName">Excel中表名(Sheet1)</param>
19 /// <returns>返回DataSet</returns>
20 public static DataSet LoadDataFromExcel(string filePath, string tableName)
21 {
22 try
23 {
24 if (!tableName.EndsWith("$"))
25 {
26 tableName = tableName+"$";
27 }
28
29 string strConn = string.Empty;
30 if (filePath.Substring(filePath.LastIndexOf('.')).Equals(".xls"))
31 {
32 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
33 }
34 else
35 {
36 strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
37 }
38 OleDbConnection OleConn = new OleDbConnection(strConn);
39 OleConn.Open();
40 string sql = "SELECT * FROM [" + tableName + "]";//[Sheet1$]";//可更改Sheet名称，比如sheet2，等等
41
42 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
43 DataSet ds = new DataSet();
44 OleDaExcel.Fill(ds, tableName);
45 OleConn.Close();
46 return ds;
47 }
48 catch
49 {
50 return null;
51 }
52 }
53 /// <summary>
54 /// 服务器安全设置阻止此操作OpenDataSource
55 /// </summary>
56 /// <param name="filePath"></param>
57 /// <param name="tableName"></param>
58 /// <returns></returns>
59 public static int InsertDataListIntoSql(string filePath, string tableName)
60 {
61 string connstr = "SELECT * into Chain_cashphone_card FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="
62 +"\""+filePath+"\";User ID=administrator;Password=;Extended properties=Excel 8.0')..."+tableName;
63 SqlCommand cmd = new SqlCommand(connstr);
64 int res = 0;
65 res= (int)(new Sql.SqlConn().sqlExecObj(cmd));
66 return res;
67
68 }
69
70
71 /// <summary>
72 /// 数据集操作
73 /// </summary>
74 /// <param name="ds"></param>
75 public static string DataSetOperator(DataSet ds)
76 {
77 SqlConnection conn = new SqlConnection(new Sql.SqlConn().getStringConnention());
78 conn.Open();
79 string Mess = string.Empty;
80 SqlTransaction str = conn.BeginTransaction();//利用事务处理 防止中断
81 int k = 0;
82 int Datacount = 0;
83 for (int n = 0; n < ds.Tables.Count; n++)
84 {
85 if (ds.Tables[n].Rows.Count < 1)
86 {
87 Mess = "没有数据！";
88 Datacount++;
89 }
90 if (ds.Tables.Count == Datacount)
91 {
92 return Mess;
93 }
94
95 }
96 try
97 {
98 for (int j = 0; j < ds.Tables.Count; j++)
99 {
100 for (int i = 0; i < ds.Tables[j].Rows.Count; i++)
101 {
102 string sqlStr = "insert into chain_CashPhone_Card(CardNO,CardPwd,ExpireTime,ServiceName,CardName,PurPrice,Price,Cost,Block,IsUsed,AddTime,IsValid,ProductCode,Area)values";
103 sqlStr += "('" + ds.Tables[j].Rows[i][0].ToString() + "','";
104 sqlStr += ds.Tables[j].Rows[i][1].ToString() + "','";
105 sqlStr += ds.Tables[j].Rows[i][2].ToString() + "','";
106 sqlStr += ds.Tables[j].Rows[i][3].ToString() + "','";
107 sqlStr += ds.Tables[j].Rows[i][4].ToString() + "',";
108 sqlStr += ds.Tables[j].Rows[i][5].ToString() + ",";
109 sqlStr += ds.Tables[j].Rows[i][6].ToString() + ",";
110 sqlStr += ds.Tables[j].Rows[i][7].ToString() + ",'',";
111 sqlStr += ds.Tables[j].Rows[i][8].ToString() + ",getDate(),";
112 sqlStr += ds.Tables[j].Rows[i][9].ToString() + ",'','";
113 sqlStr += ds.Tables[j].Rows[i][10].ToString() + "')";
114 SqlCommand cmd = new SqlCommand(sqlStr, conn, str);
115 cmd.Transaction = str;
116 k += cmd.ExecuteNonQuery();
117 }
118 }
119 str.Commit();
120 }
121 catch (Exception ex)
122 {
123 Mess="发生异常,数据已回滚/n信息/n" + ex.Message;
124 str.Rollback();
125 return Mess;
126 }
127 finally
128 {
129 Mess="上传成功" + k + "条";
130 }
131 return Mess;
132 }
133
134
135
136 /// <summary>
137 /// 将ds中表插入数据库表中
138 /// </summary>
139 /// <param name="ds">DataSet</param>
140 /// <param name="tableName">数据库中表名</param>
141 /// <returns>返回状态</returns>
142 public static string ExcelToSql(DataSet ds, string tableName)
143 {
144 try
145 {
146 if (ds != null && ds.Tables.Count>0)
147 {
148 string conn = TongLu.DatasInfo.SqlHelper.ConnectionStringLocalTransaction;
149 SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.UseInternalTransaction);
150 sqlbulkcopy.DestinationTableName = tableName;//数据库中的表名
151 for (int i = 0; i < ds.Tables.Count; i++)
152 {
153 sqlbulkcopy.WriteToServer(ds.Tables[i]);
154 }
155 }
156 else
157 {
158 return "数据加载失败,文件路径错误！";
159 }
160 }
161 catch(Exception e)
162 {
163 return e.Message;//"导入异常，请确认表数据是否规范或有重复添加！";
164 }
165 return "导入成功！";
166 }
167
168 public static ArrayList GetExcelSheetNameList(string filePath)
169 {
170 string strConn = string.Empty;
171 if (filePath.Substring(filePath.LastIndexOf('.')).Equals(".xls"))
172 {
173 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
174 }
175 else
176 {
177 strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
178 }
179 OleDbConnection Conn = new OleDbConnection(strConn);
180 Conn.Open();
181
182 //在传递限制数组的值时，对于不包含值的数组元素使用 Visual C# .NET 的 null 关键字。例如，如果要检索表的架构，使用 OleDbSchemaGuid.Tables。
183 //DataTable 中返回的每一列是限制列（TABLE_CATALOG、TABLE_SCHEMA、TABLE_NAME、TABLE_TYPE），后面是 TABLE_GUID、DESCRIPTION、TABLE_PROPID、DATE_CREATED 和 DATE_MODIFIED 的其他架构列。
184 //object[] 数组中的[3]="TABLE" 对应 TABLE_TYPE,TABLE_NAME对应 [2]
185 DataTable dtSheetNameList = Conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null,null,null,"TABLE"});
186 Conn.Close();
187
188 ArrayList ai = new ArrayList();
189 foreach (DataRow dr in dtSheetNameList.Rows)
190 {
191 ai.Add(dr[2].ToString());//或 ai.Add(dr.ItemArray[2].ToString());
192 }
193 return ai;
194 }
195
196 }
197 }
相信以上大家都可以看得懂，此类方法可兼容office07下（包涵07）版本，个别知识点，大家可以自己去查证，在下就不多说了。导入数据的时候，个别数据需要处理的，比如excel空行数据要剔除，excel自动把比较长的数据科学计数法转换，
这里就需要在导入的时候稍作处理了，方法应该很多，每人都有不同理解，就不献丑了，鄙人的比较粗糙，欢迎有简便处理的可以贴出来讨论讨论，本人的代码就不贴了，个人认为不太科学，不误导大家了。。
看下面，导出数据库查询数据到excel
这个地方要稍微注意的就是中文乱码问题，主要是编码方式，大家要谨慎对待，个人测试发现utf-7目前较好，大家可以测试测试，当然网上也有一些其他的解决方法，大家可以自己看看，有更好的方式了，欢迎大家贴出来，分享下，知识无国界嘛，还有就是输出方式了。
代码：
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using TongLu.PMethod.Sql;
using System.IO;
/// <summary>
/// Summary description for DataListToExcel
/// </summary>
public class DataListToExcel
{
public DataListToExcel()
{
//
// TODO: Add constructor logic here
//
}
/// <summary>
/// 传入由制表符‘\t’构建好的字符流对象
/// </summary>
/// <param name="sw">字符流</param>
/// <param name="fileName">文件名</param>
public static void DataSaveToExcel(StringWriter sw, string fileName)
{
string headerEncodeStr = "GB2312";
string contentEndoeStr = "GB2312";
System.Web.HttpContext currContent = System.Web.HttpContext.Current;
currContent.Response.HeaderEncoding = System.Text.Encoding.GetEncoding(headerEncodeStr);
currContent.Response.AddHeader("Content-Disposition", "inline;attachment;filename=" + fileName + ".xls");
currContent.Response.ContentEncoding = System.Text.Encoding.GetEncoding(contentEndoeStr);
currContent.Response.Charset = contentEndoeStr;
currContent.Response.ContentType = "application/ms-excel";
currContent.Response.Write(sw);
currContent.Response.End();
}
/// <summary>
/// 传入由制表符‘\t’构建好的字符流对象
/// </summary>
/// <param name="sw">字符流</param>
/// <param name="fileName">文件名</param>
/// <param name="swHeaderEncoding">当前标头输出流的编码格式，默认为GB2312</param>
/// <param name="swContentEncoding">输出流的http字符集编码，默认为GB2312</param>
public static void DataSaveToExcel(StringWriter sw,string fileName,string swHeaderEncoding,string swContentEncoding)
{
string headerEncodeStr = "GB2312";
string contentEndoeStr = "GB2312";
if (!string.IsNullOrEmpty(swHeaderEncoding))
{
headerEncodeStr = swHeaderEncoding;
}
if (!string.IsNullOrEmpty(swContentEncoding))
{
contentEndoeStr = swContentEncoding;
}
System.Web.HttpContext currContent = System.Web.HttpContext.Current;
currContent.Response.HeaderEncoding = System.Text.Encoding.GetEncoding(headerEncodeStr);
currContent.Response.AddHeader("Content-Disposition","inline;attachment;filename="+fileName+".xls");
currContent.Response.ContentEncoding = System.Text.Encoding.GetEncoding(contentEndoeStr);
currContent.Response.Charset = contentEndoeStr;
currContent.Response.ContentType = "ap |
客服电话
APP下载
官方微信
请发表评论