C# Excel 相关导入导出操作
最近一段时间，由于工作需要，需要做数据库与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 = "application/ms-excel";
currContent.Response.Write(sw);
currContent.Response.End();
}
/// <summary>
/// 把字符串格式的数据写入excel
/// </summary>
/// <param name="sw">字符串</param>
/// <param name="fileName">文件名</param>
public static void DataSaveToExcel(string 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>
/// 把字符串格式的数据写入excel
/// </summary>
/// <param name="sw">字符串</param>
/// <param name="fileName">文件名</param>
/// <param name="swHeaderEncoding">当前标头输出流的编码格式，默认为GB2312</param>
/// <param name="swContentEncoding">输出流的http字符集编码，默认为GB2312</param>
public static void DataSaveToExcel(string 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 = "application/ms-excel";
currContent.Response.Write(sw);
currContent.Response.End();
}
/// <summary>
/// 获取excel行标
/// </summary>
/// <param name="j"></param>
/// <returns></returns>
private static string GetColumnID(int j)
{
string columnNum = string.Empty;
switch (j)
{
case 1: columnNum = "A"; break;
case 2: columnNum = "B"; break;
case 3: columnNum = "C"; break;
case 4: columnNum = "D"; break;
case 5: columnNum = "E"; break;
case 6: columnNum = "F"; break;
case 7: columnNum = "G"; break;
case 8: columnNum = "H"; break;
case 9: columnNum = "I"; break;
case 10: columnNum = "J"; break;
case 11: columnNum = "K"; break;
case 12: columnNum = "L"; break;
case 13: columnNum = "M"; break;
case 14: columnNum = "N"; break;
case 15: columnNum = "O"; break;
default:
columnNum = "P"; break;
}
return columnNum;
}
/// <summary>
/// 传入DataTable集，并且可以控制是否显示数据统计列
/// </summary>
/// <param name="dt">DataTable数据集</param>
/// <param name="fileName">文件名</param>
/// <param name="isSum">是否显示统计，true为显示，false为不显示</param>
/// <param name="columnNameArr">需要统计的列的集合,不显示时填空数组</param>
public static void DataSaveToExcel(DataTable dt,string fileName,bool isSum,string[] columnNameArr)
{
if (dt != null && dt.Rows.Count != 0)
{
StringWriter sw = new StringWriter();
//列名
foreach (DataColumn dc in dt.Columns)
{
sw.Write(dc.ColumnName + "\t");// 若用 “,”分割，则为.csv文件
}
sw.WriteLine();
foreach (DataRow dr in dt.Rows)
{
for (int j = 0; j < dt.Columns.Count;j++ )
{
sw.Write(dr[j].ToString().Replace("\r","").Replace("\n","").Replace("<br>","/"));
sw.Write("\t");
}
sw.WriteLine();
}
if (isSum&&columnNameArr.Length!=0)
{
string val_last = string.Empty;
for (int i = 0; i < dt.Rows.Count + 1; i++)//第0行表示datatable的标题栏
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (i == dt.Rows.Count)
{
if (j==0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dt.Columns[j].ColumnName == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (i + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write('\t');
val_last = string.Empty;
}
}
}
}
sw.Flush();
sw.Close();
//生成excel
DataSaveToExcel(sw, fileName);
}
}
/// <summary>
/// 传入DataTable集，并且可以控制是否显示数据统计列
/// </summary>
/// <param name="dt">DataTable数据集</param>
/// <param name="fileName">文件名</param>
/// <param name="isSum">是否显示统计，true为显示，false为不显示</param>
/// <param name="columnNameArr">需要统计的列的集合,不显示时填空数组</param>
/// <param name="swHeaderEncoding">当前标头输出流的编码格式，默认为GB2312</param>
/// <param name="swContentEncoding">输出流的http字符集编码，默认为GB2312</param>
public static void DataSaveToExcel(DataTable dt, string fileName, bool isSum, string[] columnNameArr, string swHeaderEncoding, string swContentEncoding)
{
if (dt != null && dt.Rows.Count != 0)
{
StringWriter sw = new StringWriter();
//列名
foreach (DataColumn dc in dt.Columns)
{
sw.Write(dc.ColumnName + "\t");// 若用 “,”分割，则为.csv文件
}
sw.WriteLine();
foreach (DataRow dr in dt.Rows)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
sw.Write(dr[j].ToString().Replace("\r", "").Replace("\n", "").Replace("<br>", "/"));
sw.Write("\t");
}
sw.WriteLine();
}
if (isSum && columnNameArr.Length != 0)
{
string val_last = string.Empty;
for (int i = 0; i < dt.Rows.Count + 1; i++)//第0行表示datatable的标题栏
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (i == dt.Rows.Count)
{
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dt.Columns[j].ColumnName == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (i + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write('\t');
val_last = string.Empty;
}
}
}
}
sw.Flush();
sw.Close();
//生成excel
DataSaveToExcel(sw, fileName, swHeaderEncoding, swContentEncoding);
}
}
/// <summary>
/// 传入DataTable集，并且可以控制是否显示数据统计列
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="excelColumnName">excel要显示的列名</param>
/// <param name="dtColumnName">与excel列名对应匹配的真实列名</param>
/// <param name="fileName">文件名</param>
/// <param name="isSum">是否统计</param>
/// <param name="columnNameArr">统计真实列名</param>
public static void DataSaveToExcel(DataTable dt, string[] excelColumnName, string[] dtColumnName, string fileName, bool isSum, string[] columnNameArr)
{
if (dt != null && dt.Rows.Count != 0)
{
StringWriter sw = new StringWriter();
//遍历列名
if (excelColumnName.Length != 0 && dtColumnName.Length != 0&&excelColumnName.Length==dtColumnName.Length)
{
for (int i = 0; i < excelColumnName.Length; i++)
{
sw.Write(excelColumnName[i] + "\t");
}
sw.WriteLine();
//遍历数据
foreach (DataRow dr in dt.Rows)
{
for (int j = 0; j < dtColumnName.Length; j++)
{
sw.Write(dr[dtColumnName[j]].ToString() + "\t");
}
sw.WriteLine();
}
//是否统计数据
if (isSum && columnNameArr.Length != 0)
{
string val_last = string.Empty;
for (int j = 0; j < excelColumnName.Length; j++)
{
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dtColumnName[j] == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (dt.Rows.Count + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write('\t');
val_last = string.Empty;
}
}
}
else
{
//列名
foreach (DataColumn dc in dt.Columns)
{
sw.Write(dc.ColumnName + "\t");
}
sw.WriteLine();
foreach (DataRow dr in dt.Rows)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
sw.Write(dr[j].ToString()+ "\t");
}
}
sw.WriteLine();
//是否统计数据
if (isSum && columnNameArr.Length != 0)
{
string val_last = string.Empty;
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dt.Columns[j].ColumnName == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (dt.Rows.Count + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write('\t');
val_last = string.Empty;
}
}
}
sw.Flush();
sw.Close();
//生成excel
DataSaveToExcel(sw, fileName);
}
}
/// <summary>
/// 传入DataTable集，并且可以控制是否显示数据统计列
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="excelColumnName">excel要显示的列名</param>
/// <param name="dtColumnName">与excel列名对应匹配的真实列名</param>
/// <param name="fileName">文件名</param>
/// <param name="isSum">是否统计</param>
/// <param name="columnNameArr">统计真实列名</param>
/// <param name="swHeaderEncoding">当前标头输出流的编码格式，默认为GB2312</param>
/// <param name="swContentEncoding">输出流的http字符集编码，默认为GB2312</param>
public static void DataSaveToExcel(DataTable dt, string[] excelColumnName, string[] dtColumnName, string fileName, bool isSum, string[] columnNameArr, string swHeaderEncoding, string swContentEncoding)
{
if (dt != null && dt.Rows.Count != 0)
{
StringWriter sw = new StringWriter();
//遍历列名
if (excelColumnName.Length != 0 && dtColumnName.Length != 0 && excelColumnName.Length == dtColumnName.Length)
{
for (int i = 0; i < excelColumnName.Length; i++)
{
sw.Write(excelColumnName[i] + "\t");
}
sw.WriteLine();
//遍历数据
foreach (DataRow dr in dt.Rows)
{
for (int j = 0; j < dtColumnName.Length; j++)
{
sw.Write(dr[dtColumnName[j]].ToString() + "\t");
}
sw.WriteLine();
}
//是否统计数据
if (isSum && columnNameArr.Length != 0)
{
string val_last = string.Empty;
for (int j = 0; j < excelColumnName.Length; j++)
{
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dtColumnName[j] == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (dt.Rows.Count + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write('\t');
val_last = string.Empty;
}
}
}
else
{
//列名
foreach (DataColumn dc in dt.Columns)
{
sw.Write(dc.ColumnName + "\t");
}
sw.WriteLine();
foreach (DataRow dr in dt.Rows)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
sw.Write(dr[j].ToString() + "\t");
}
}
sw.WriteLine();
//是否统计数据
if (isSum && columnNameArr.Length != 0)
{
string val_last = string.Empty;
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dt.Columns[j].ColumnName == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (dt.Rows.Count + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write('\t');
val_last = string.Empty;
}
}
}
sw.Flush();
sw.Close();
//生成excel
DataSaveToExcel(sw, fileName, swHeaderEncoding, swContentEncoding);
}
}
/// <summary>
/// 传入DataRow[]，不能统计数据
/// </summary>
/// <param name="drs">DataRow[]</param>
/// <param name="excelColumnName">excel要显示的列名</param>
/// <param name="dtColumnName">与excel列名对应匹配的真实列名</param>
/// <param name="fileName">文件名</param>
public static void DataSaveToExcel(DataRow[] drs, string[] excelColumnName, string[] dtColumnName, string fileName, bool isSum, string[] columnNameArr)
{
if (drs.Length != 0)
{
StringWriter sw = new StringWriter();
//遍历列名
if (excelColumnName.Length != 0 && dtColumnName.Length != 0 && excelColumnName.Length == dtColumnName.Length)
{
for (int i = 0; i < excelColumnName.Length; i++)
{
sw.Write(excelColumnName[i] + "\t");
}
sw.WriteLine();
//遍历数据
foreach (DataRow dr in drs)
{
for (int j = 0; j < dtColumnName.Length; j++)
{
sw.Write(dr[dtColumnName[j]].ToString() + "\t");
}
sw.WriteLine();
}
//是否统计数据
if (isSum && columnNameArr.Length != 0)
{
string val_last = string.Empty;
for (int j = 0; j < excelColumnName.Length; j++)
{
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dtColumnName[j] == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (drs.Length + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write('\t');
val_last = string.Empty;
}
}
}
else
{
foreach (DataColumn dc in drs[0].Table.Columns)
{
sw.Write(dc.ColumnName+"\t");
}
sw.WriteLine();
foreach (DataRow dr in drs)
{
foreach (DataColumn dc in dr.Table.Columns)
{
sw.Write(dr[dc.ColumnName].ToString() + "\t");
}
sw.WriteLine();
}
//是否有统计列
if (isSum && columnNameArr.Length != 0)
{
string var_list = string.Empty;
for (int i = 0; i < drs[0].Table.Columns.Count; i++)
{
if (i == 0)
{
sw.Write("总计");
}
for (int j = 0; j < columnNameArr.Length; j++)
{
if (drs[0].Table.Columns[i].ColumnName == columnNameArr[j])
{
var_list = "=sum(" + GetColumnID(i + 1) + "2:" + GetColumnID(i + 1) + (drs[0].Table.Rows.Count+1) + ")";
}
}
sw.Write(var_list+"\t");
var_list = string.Empty;
}
sw.WriteLine();
}
}
sw.Flush();
sw.Close();
//生成excel
DataSaveToExcel(sw, fileName);
}
}
/// <summary>
/// 传入DataRow[]，不能统计数据
/// </summary>
/// <param name="drs">DataRow[]</param>
/// <param name="excelColumnName">excel要显示的列名</param>
/// <param name="dtColumnName">与excel列名对应匹配的真实列名</param>
/// <param name="fileName">文件名</param>
/// <param name="swHeaderEncoding">当前标头输出流的编码格式，默认为GB2312</param>
/// <param name="swContentEncoding">输出流的http字符集编码，默认为GB2312</param>
public static void DataSaveToExcel(DataRow[] drs, string[] excelColumnName, string[] dtColumnName, string fileName, bool isSum, string[] columnNameArr, string swHeaderEncoding, string swContentEncoding)
{
if (drs.Length != 0)
{
StringWriter sw = new StringWriter();
//遍历列名
if (excelColumnName.Length != 0 && dtColumnName.Length != 0 && excelColumnName.Length == dtColumnName.Length)
{
for (int i = 0; i < excelColumnName.Length; i++)
{
sw.Write(excelColumnName[i] + "\t");
}
sw.WriteLine();
//遍历数据
foreach (DataRow dr in drs)
{
for (int j = 0; j < dtColumnName.Length; j++)
{
sw.Write(dr[dtColumnName[j]].ToString() + "\t");
}
sw.WriteLine();
}
//是否统计数据
if (isSum && columnNameArr.Length != 0)
{
string val_last = string.Empty;
for (int j = 0; j < excelColumnName.Length; j++)
{
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dtColumnName[j] == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (drs.Length + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write('\t');
val_last = string.Empty;
}
}
}
else
{
foreach (DataColumn dc in drs[0].Table.Columns)
{
sw.Write(dc.ColumnName + "\t");
}
sw.WriteLine();
foreach (DataRow dr in drs)
{
foreach (DataColumn dc in dr.Table.Columns)
{
sw.Write(dr[dc.ColumnName].ToString() + "\t");
}
sw.WriteLine();
}
//是否有统计列
if (isSum && columnNameArr.Length != 0)
{
string var_list = string.Empty;
for (int i = 0; i < drs[0].Table.Columns.Count; i++)
{
if (i == 0)
{
sw.Write("总计");
}
for (int j = 0; j < columnNameArr.Length; j++)
{
if (drs[0].Table.Columns[i].ColumnName == columnNameArr[j])
{
var_list = "=sum(" + GetColumnID(i + 1) + "2:" + GetColumnID(i + 1) + (drs[0].Table.Rows.Count + 1) + ")";
}
}
sw.Write(var_list + "\t");
var_list = string.Empty;
}
sw.WriteLine();
}
}
sw.Flush();
sw.Close();
//生成excel
DataSaveToExcel(sw, fileName,swHeaderEncoding,swContentEncoding);
}
}
/// <summary>
/// 传入DataTable集,写table的形式
/// </summary>
/// <param name="dt">DataTable数据集</param>
/// <param name="fileName">文件名</param>
/// <param name="isSum">是否统计</param>
/// <param name="columnNameArr">统计真实列名</param>
public static void DataSaveToExcel_CreateTable(DataTable dt, string fileName, bool isSum, string[] columnNameArr)
{
if (dt != null && dt.Rows.Count != 0)
{
StringWriter sw = new StringWriter();
sw.Write("<table border='1' cellpadding='0' cellspacing='0'><tr>");
//列名
foreach (DataColumn dc in dt.Columns)
{
sw.Write("<td>"+dc.ColumnName + "</td>");// 若用 “,”分割，则为.csv文件
}
sw.Write("</tr>");
foreach (DataRow dr in dt.Rows)
{
sw.Write("<tr>");
for (int j = 0; j < dt.Columns.Count; j++)
{
sw.Write("<td>"+dr[j].ToString().Replace("\r", "").Replace("\n", "").Replace("<br>", "/")+"</td>");
}
sw.Write("</tr>");
}
if (isSum&&columnNameArr.Length!=0)
{
sw.Write("<tr>");
string val_last = string.Empty;
for (int j = 0; j < dt.Columns.Count; j++)
{
sw.Write("<td>");
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dt.Columns[j].ColumnName == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (dt.Rows.Count + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write("</td>");
val_last = string.Empty;
}
sw.Write("</tr>");
}
sw.Write("</table>");
sw.Flush();
sw.Close();
//生成excel
DataSaveToExcel(sw, fileName);
}
}
/// <summary>
/// 传入DataTable集,写table的形式
/// </summary>
/// <param name="dt">DataTable数据集</param>
/// <param name="fileName">文件名</param>
/// <param name="isSum">是否统计</param>
/// <param name="columnNameArr">统计真实列名</param>
/// <param name="swHeaderEncoding">当前标头输出流的编码格式，默认为GB2312</param>
/// <param name="swContentEncoding">输出流的http字符集编码，默认为GB2312</param>
public static void DataSaveToExcel_CreateTable(DataTable dt, string fileName, bool isSum, string[] columnNameArr, string swHeaderEncoding, string swContentEncoding)
{
if (dt != null && dt.Rows.Count != 0)
{
StringWriter sw = new StringWriter();
sw.Write("<table border='1' cellpadding='0' cellspacing='0'><tr>");
//列名
foreach (DataColumn dc in dt.Columns)
{
sw.Write("<td>" + dc.ColumnName + "</td>");// 若用 “,”分割，则为.csv文件
}
sw.Write("</tr>");
foreach (DataRow dr in dt.Rows)
{
sw.Write("<tr>");
for (int j = 0; j < dt.Columns.Count; j++)
{
sw.Write("<td>" + dr[j].ToString().Replace("\r", "").Replace("\n", "").Replace("<br>", "/") + "</td>");
}
sw.Write("</tr>");
}
if (isSum && columnNameArr.Length != 0)
{
sw.Write("<tr>");
string val_last = string.Empty;
for (int j = 0; j < dt.Columns.Count; j++)
{
sw.Write("<td>");
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dt.Columns[j].ColumnName == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (dt.Rows.Count + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write("</td>");
val_last = string.Empty;
}
sw.Write("</tr>");
}
sw.Write("</table>");
sw.Flush();
sw.Close();
//生成excel
DataSaveToExcel(sw, fileName,swHeaderEncoding,swContentEncoding);
}
}
/// <summary>
/// 传入DataTable集
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="fileName">文件名</param>
/// <param name="excelColumnName">excel要显示的列名</param>
/// <param name="dtColumnName">与excel列名对应匹配的真实列名</param>
/// <param name="isSum">是否统计</param>
/// <param name="columnNameArr">统计真实列名，必须存在于要显示的列列表中</param>
public static void DataSaveToExcel_CreateTable(DataTable dt, string fileName, string[] excelColumnName, string[] dtColumnName, bool isSum, string[] columnNameArr)
{
if (dt != null && dt.Rows.Count != 0)
{
StringWriter sw = new StringWriter();
sw.Write("<table border='1' cellpadding='0' cellspacing='0'><tr>");
if (excelColumnName.Length != 0 && dtColumnName.Length != 0 && excelColumnName.Length == dtColumnName.Length)
{
for (int i = 0; i < excelColumnName.Length; i++)
{
sw.Write("<td>" + excelColumnName[i] + "</td>");
}
sw.Write("</tr>");
//遍历数据
foreach (DataRow dr in dt.Rows)
{
sw.Write("<tr>");
for (int j = 0; j < dtColumnName.Length; j++)
{
sw.Write("<td>" + dr[dtColumnName[j]].ToString() + "</td>");
}
sw.Write("</tr>");
}
if (isSum&&columnNameArr.Length!=0)
{
sw.Write("<tr>");
string val_last = string.Empty;
for (int j = 0; j < excelColumnName.Length; j++)
{
sw.Write("<td>");
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dtColumnName[j] == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (dt.Rows.Count + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write("</td>");
val_last = string.Empty;
}
sw.Write("</tr>");
}
}
else
{
//列名
foreach (DataColumn dc in dt.Columns)
{
sw.Write("<td>" + dc.ColumnName + "</td>");// 若用 “,”分割，则为.csv文件
}
sw.Write("</tr>");
foreach (DataRow dr in dt.Rows)
{
sw.Write("<tr>");
for (int j = 0; j < dt.Columns.Count; j++)
{
sw.Write("<td>" + dr[j].ToString().Replace("\r", "").Replace("\n", "").Replace("<br>", "/") + "</td>");
}
sw.Write("</tr>");
}
if (isSum&&columnNameArr.Length!=0)
{
sw.Write("<tr>");
string val_last = string.Empty;
for (int j = 0; j < dt.Columns.Count; j++)
{
sw.Write("<td>");
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dt.Columns[j].ColumnName == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (dt.Rows.Count + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write("</td>");
val_last = string.Empty;
}
sw.Write("</tr>");
}
}
sw.Write("</table>");
sw.Flush();
sw.Close();
//生成excel
DataSaveToExcel(sw, fileName);
}
}
/// <summary>
/// 传入DataTable集
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="fileName">文件名</param>
/// <param name="excelColumnName">excel要显示的列名</param>
/// <param name="dtColumnName">与excel列名对应匹配的真实列名</param>
/// <param name="isSum">是否统计</param>
/// <param name="columnNameArr">统计真实列名，必须存在于要显示的列列表中</param>
/// <param name="swHeaderEncoding">当前标头输出流的编码格式，默认为GB2312</param>
/// <param name="swContentEncoding">输出流的http字符集编码，默认为GB2312</param>
public static void DataSaveToExcel_CreateTable(DataTable dt, string fileName, string[] excelColumnName, string[] dtColumnName, bool isSum, string[] columnNameArr, string swHeaderEncoding, string swContentEncoding)
{
if (dt != null && dt.Rows.Count != 0)
{
StringWriter sw = new StringWriter();
sw.Write("<table border='1' cellpadding='0' cellspacing='0'><tr>");
if (excelColumnName.Length != 0 && dtColumnName.Length != 0 && excelColumnName.Length == dtColumnName.Length)
{
for (int i = 0; i < excelColumnName.Length; i++)
{
sw.Write("<td>" + excelColumnName[i] + "</td>");
}
sw.Write("</tr>");
//遍历数据
foreach (DataRow dr in dt.Rows)
{
sw.Write("<tr>");
for (int j = 0; j < dtColumnName.Length; j++)
{
sw.Write("<td>" + dr[dtColumnName[j]].ToString() + "</td>");
}
sw.Write("</tr>");
}
if (isSum && columnNameArr.Length != 0)
{
sw.Write("<tr>");
string val_last = string.Empty;
for (int j = 0; j < excelColumnName.Length; j++)
{
sw.Write("<td>");
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dtColumnName[j] == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (dt.Rows.Count + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write("</td>");
val_last = string.Empty;
}
sw.Write("</tr>");
}
}
else
{
//列名
foreach (DataColumn dc in dt.Columns)
{
sw.Write("<td>" + dc.ColumnName + "</td>");// 若用 “,”分割，则为.csv文件
}
sw.Write("</tr>");
foreach (DataRow dr in dt.Rows)
{
sw.Write("<tr>");
for (int j = 0; j < dt.Columns.Count; j++)
{
sw.Write("<td>" + dr[j].ToString().Replace("\r", "").Replace("\n", "").Replace("<br>", "/") + "</td>");
}
sw.Write("</tr>");
}
if (isSum && columnNameArr.Length != 0)
{
sw.Write("<tr>");
string val_last = string.Empty;
for (int j = 0; j < dt.Columns.Count; j++)
{
sw.Write("<td>");
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dt.Columns[j].ColumnName == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (dt.Rows.Count + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write("</td>");
val_last = string.Empty;
}
sw.Write("</tr>");
}
}
sw.Write("</table>");
sw.Flush();
sw.Close();
//生成excel
DataSaveToExcel(sw, fileName,swHeaderEncoding,swContentEncoding);
}
}
/// <summary>
/// 传入DataRow[]，不能统计数据
/// </summary>
/// <param name="drs">DataRow[]</param>
/// <param name="excelColumnName">excel要显示的列名</param>
/// <param name="dtColumnName">与excel列名对应匹配的真实列名</param>
/// <param name="fileName">文件名</param>
public static void DataSaveToExcel_CreateTable(DataRow[] drs, string[] excelColumnName, string[] dtColumnName, string fileName, bool isSum, string[] columnNameArr)
{
if (drs.Length != 0)
{
StringWriter sw = new StringWriter();
sw.Write("<table cellpadding='0' cellspacing='0' border='1'><tr>");
//遍历列名
if (excelColumnName.Length != 0 && dtColumnName.Length != 0 && excelColumnName.Length == dtColumnName.Length)
{
for (int i = 0; i < excelColumnName.Length; i++)
{
sw.Write("<td>"+excelColumnName[i] + "</td>");
}
sw.Write("</tr>");
//遍历数据
foreach (DataRow dr in drs)
{
sw.Write("<tr>");
for (int j = 0; j < dtColumnName.Length; j++)
{
sw.Write("<td>"+dr[dtColumnName[j]].ToString() + "</td>");
}
sw.Write("</tr>");
}
//是否统计数据
if (isSum && columnNameArr.Length != 0)
{
string val_last = string.Empty;
sw.Write("<tr>");
for (int j = 0; j < excelColumnName.Length; j++)
{
sw.Write("<td>");
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dtColumnName[j] == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (drs.Length + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write("</td>");
val_last = string.Empty;
}
sw.Write("</tr>");
}
}
else
{
foreach (DataColumn dc in drs[0].Table.Columns)
{
sw.Write("<td>"+dc.ColumnName + "</td>");
}
sw.Write("</tr>");
foreach (DataRow dr in drs)
{
sw.Write("<tr>");
foreach (DataColumn dc in dr.Table.Columns)
{
sw.Write("<td>"+dr[dc.ColumnName].ToString() + "</td>");
}
sw.Write("</tr>");
}
//是否有统计列
if (isSum && columnNameArr.Length != 0)
{
string var_list = string.Empty;
sw.Write("<tr>");
for (int i = 0; i < drs[0].Table.Columns.Count; i++)
{
sw.Write("<td>");
if (i == 0)
{
sw.Write("总计");
}
for (int j = 0; j < columnNameArr.Length; j++)
{
if (drs[0].Table.Columns[i].ColumnName == columnNameArr[j])
{
var_list = "=sum(" + GetColumnID(i + 1) + "2:" + GetColumnID(i + 1) + (drs[0].Table.Rows.Count + 1) + ")";
}
}
sw.Write(var_list + "</td>");
var_list = string.Empty;
}
sw.Write("</tr>");
}
}
sw.Write("</table>");
sw.Flush();
sw.Close();
//生成excel
DataSaveToExcel(sw, fileName);
}
}
/// <summary>
/// 传入DataRow[]，不能统计数据
/// </summary>
/// <param name="drs">DataRow[]</param>
/// <param name="excelColumnName">excel要显示的列名</param>
/// <param name="dtColumnName">与excel列名对应匹配的真实列名</param>
/// <param name="fileName">文件名</param>
/// <param name="swHeaderEncoding">当前标头输出流的编码格式，默认为GB2312</param>
/// <param name="swContentEncoding">输出流的http字符集编码，默认为GB2312</param>
public static void DataSaveToExcel_CreateTable(DataRow[] drs, string[] excelColumnName, string[] dtColumnName, string fileName, bool isSum, string[] columnNameArr, string swHeaderEncoding, string swContentEncoding)
{
if (drs.Length != 0)
{
StringWriter sw = new StringWriter();
sw.Write("<table cellpadding='0' cellspacing='0' border='1'><tr>");
//遍历列名
if (excelColumnName.Length != 0 && dtColumnName.Length != 0 && excelColumnName.Length == dtColumnName.Length)
{
for (int i = 0; i < excelColumnName.Length; i++)
{
sw.Write("<td>" + excelColumnName[i] + "</td>");
}
sw.Write("</tr>");
//遍历数据
foreach (DataRow dr in drs)
{
sw.Write("<tr>");
for (int j = 0; j < dtColumnName.Length; j++)
{
sw.Write("<td>" + dr[dtColumnName[j]].ToString() + "</td>");
}
sw.Write("</tr>");
}
//是否统计数据
if (isSum && columnNameArr.Length != 0)
{
string val_last = string.Empty;
sw.Write("<tr>");
for (int j = 0; j < excelColumnName.Length; j++)
{
sw.Write("<td>");
if (j == 0)
{
val_last = "合计";
}
for (int k = 0; k < columnNameArr.Length; k++)
{
if (dtColumnName[j] == columnNameArr[k])
{
val_last = "=sum(" + GetColumnID(j + 1) + "2:" + GetColumnID(j + 1) + (drs.Length + 1) + ")";
}
}
sw.Write(val_last.Replace("\n", "").Replace("\r", "").Replace("<br>", "/"));
sw.Write("</td>");
val_last = string.Empty;
}
sw.Write("</tr>");
}
}
else
{
foreach (DataColumn dc in drs[0].Table.Columns)
{
sw.Write("<td>" + dc.ColumnName + "</td>");
}
sw.Write("</tr>");
foreach (DataRow dr in drs)
{
sw.Write("<tr>");
foreach (DataColumn dc in dr.Table.Columns)
{
sw.Write("<td>" + dr[dc.ColumnName].ToString() + "</td>");
}
sw.Write("</tr>");
}
//是否有统计列
if (isSum && columnNameArr.Length != 0)
{
string var_list = string.Empty;
sw.Write("<tr>");
for (int i = 0; i < drs[0].Table.Columns.Count; i++)
{
sw.Write("<td>");
if (i == 0)
{
sw.Write("总计");
}
for (int j = 0; j < columnNameArr.Length; j++)
{
if (drs[0].Table.Columns[i].ColumnName == columnNameArr[j])
{
var_list = "=sum(" + GetColumnID(i + 1) + "2:" + GetColumnID(i + 1) + (drs[0].Table.Rows.Count + 1) + ")";
}
}
sw.Write(var_list + "</td>");
var_list = string.Empty;
}
sw.Write("</tr>");
}
}
sw.Write("</table>");
sw.Flush();
sw.Close();
//生成excel
DataSaveToExcel(sw, fileName,swHeaderEncoding,swContentEncoding);
}
}
}
根据本人测试，该导出封装类，操作比较稳定，基本不会出现导出表出现不能查看的问题，方法考虑了大部分情况，基础是一样的，使用需要各位去筛选。就不赘述了。强调一点，该保存的excel最好使用office的打开，向wps之类的，统计函数是office标准，可能不太好使。
转载请注明，谢谢。
