c# excel表格转Json

来源：互联网发布：.net程序员自我评价编辑：程序博客网时间：2024/05/06 11:21

excel表格数据转Json数据

我用的是 OleDbConnection Newtonsoft.Json

废话不多说，开撸！

public class ExcelToJson : IHttpHandler {

public void ProcessRequest (HttpContext context) {
string rootPath = "e:\\epfile\\";
//接收文件
HttpFileCollection files = context.Request.Files;
HttpPostedFile file = files[0];

if (file.ContentLength > 1024 * 1024 * 10)
{

//异域访问的声明

context.Response.Headers.Add("Access-Control-Allow-Origin", "*");
context.Response.ContentType = "text/plain";
context.Response.Write(BaseResponse("1", "文件大小不能超过10M"));
return;
}

if(!file.FileName.ToLower().EndsWith("xls"))
{
context.Response.Headers.Add("Access-Control-Allow-Origin", "*");
context.Response.ContentType = "text/plain";
context.Response.Write(BaseResponse("1", "必须是xls文件"));
return;
}

//保存文件
string saveFileName = rootPath + Guid.NewGuid() + ".xls";
file.SaveAs(saveFileName);

//解析文件
List<Hashtable> list = new List<Hashtable>();
ReadExcel(saveFileName, list);

//删除文件
File.Delete(saveFileName);

string responseString = MakeResponseString(list);

//响应客户端
context.Response.Headers.Add("Access-Control-Allow-Origin", "*");
context.Response.ContentType = "text/plain";
context.Response.Write(responseString);
}

public bool IsReusable {
get {
return false;
}
}

private string BaseResponse(string result,string error)
{
QWResponse response = new QWResponse();

response.Result = result;
response.Error = error;

return response.ToJson();

}

private string MakeResponseString(List<Hashtable> lineList)
{
QWResponse response = new QWResponse();
List<Hashtable> list = new List<Hashtable>();

foreach (Object obj in lineList)
{
list.Add((Hashtable)obj);
}

response.MsgBody = JArray.FromObject(list).ToString();

return response.ToJson();
}

private void ReadExcel(string fileName, List<Hashtable> lineList)
{
string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"", fileName);
OleDbConnection conn = null;
try
{
conn = new OleDbConnection(connectionString);
conn.Open();

DataTable tb = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow row in tb.Rows)//每个sheet
{
string tableName = row["TABLE_NAME"].ToString();
if (string.IsNullOrWhiteSpace(tableName))
{
continue;
}

OleDbCommand command = new OleDbCommand();
command.Connection = conn;
command.CommandText = string.Format("select * from [{0}]", tableName);

OleDbDataReader odr = command.ExecuteReader();

while (odr.Read())
{
try
{
Hashtable pms = new Hashtable();
for (int i = 0; i < odr.FieldCount; i++)
{
pms[odr.GetName(i)] = odr[i].ToString();
}
lineList.Add(pms);
}
catch (Exception ex)
{
File.WriteAllText("e:\\debug.txt", ex.Message);
}
}
}
}
catch (Exception ex)
{
File.WriteAllText("e:\\debug.txt", ex.Message);
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}

因为我们项目有点特殊，前端和后台是完全分开的，所以，前端妹子在做好测试之后是在本地访问服务器上的url，这样就造成了异域访问的一个烦恼。不得已，在每次返回相应之前都加上了一段代码 context.Response.Headers.Add("Access-Control-Allow-Origin", "*"); ps:具体原因并不知情，如果有哪位大大看到了这里，还望不吝赐教，在下不胜感激。

代码写得有点渣，只做一个笔记，并没有炫耀之意。只是以防以后再碰到同样的问题。

0 0