一、自数据表导出excel
/// <summary>
/// 导出excel
/// </summary>
/// <param name="sortgid"></param>
/// <returns></returns>
public FileResult WithQuestionToExcel(string sortgid)
{
QuestionsLibraryAndSortService service = new QuestionsLibraryAndSortService();
List<OrderBy> orderbys = new List<OrderBy>
{
new OrderBy{Field="Sorting",IsASC=true},
new OrderBy{Field="CreateTime",IsASC=true}
};
var info = service.Get(m => m.SortGID == sortgid).OrderBy(orderbys);
string fileName = "sort" + DateTime.Now.ToString("yyyy-MM-dd");
StringBuilder strRows = new StringBuilder();
strRows.Append("<table><tr>");
strRows.Append("<td style=\"mso-number-format:\\@;\">Id</td>");
strRows.Append("<td style=\"mso-number-format:\\@;\">title</td>");
strRows.Append("</tr>");
foreach (var row in info)
{
QuestionsLibraryService qService = new QuestionsLibraryService();
QuestionsLibrary model = qService.Get(row.QuestionsLibraryGID);
strRows.Append("<tr>");
strRows.Append("<td style=\"mso-number-format:\\@;\">" + model.IID + "</td>");
strRows.Append("<td style=\"mso-number-format:\\@;\">" + (model.Title ?? "") + "</td>");
strRows.Append("</tr>");
}
strRows.Append("</table>");
string result = strRows.ToString();
//第一种:使用FileContentResult
byte[] fileContents = Encoding.Default.GetBytes(result);
//return File(fileContents, "application/ms-excel", fileName + ".xls");
return File(fileContents, "application/vnd.ms-excel", fileName + ".xls");
}
其一、导出excel使用的是网页的table
其二、为了防止数字过长导致的科学计数法现象,使用了tyle=\"mso-number-format:\\@;\"
其三、此导出的excel并不是标准的excel格式。当然,在excel中查看正常,但是如果使用导出的excel表当做导入的excel,不可以直接使用,需要另存一下,改为标准的excel格式。如果想知道什么是标准的excel格式,可以这样。把excel文件在记事本中打开,使用此方法导出的excel文件,不是乱码,而是html的table表单形式。而你另存后的excel则在记事本中是乱码。没错,标准的excel格式在记事本中就是乱码
二、自excel文件导入数据到数据库表
/// <summary>
/// 导入excel
/// </summary>
/// <param name="sortgid"></param>
/// <param name="file"></param>
/// <returns></returns>
[HttpPost]
public JsonResult WithQuestionToImportExcel(string sortgid,string file)
{
try
{
//EventLog.WriteLog(sortgid);
string url = Server.MapPath("/gallery/" + file);
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + url + "; " + "Extended Properties=Excel 8.0;"; //此连接只能操作Excel2007之前(.xls)文件
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + url + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
//返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
using (OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [" + strTableNames[0] + "]", strConn))
{
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "ExcelInfo");
DataTable dt = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
QuestionsLibraryService qservice = new QuestionsLibraryService();
QuestionsLibraryAndSortService service = new QuestionsLibraryAndSortService();
//EventLog.WriteLog("" + dt.Rows.Count);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
int iid = dr[0].ToInteger();
//EventLog.WriteLog(""+ iid);
var model = new QuestionsLibraryAndSort();
model.GID = Guid.NewGuid().ToString();
model.SortGID = sortgid;
var qModel = qservice.Get(m => m.IID == iid).FirstOrDefault();
if (qModel == null)
{
//EventLog.WriteLog("aa");
continue;
}
//EventLog.WriteLog("continue1");
if (service.Exist(m => m.SortGID == sortgid && m.QuestionsLibraryGID == qModel.GID))
{
continue;
}
//EventLog.WriteLog(qModel.GID);
model.QuestionsLibraryGID = qModel.GID;
model.Sorting= i;
service.Add(model);
}
}
}
return Json("导入成功");
}
catch(Exception ex)
{
return Json(ExceptionHelper.GetErrorMessageByJavascript(ex));
}
}
这里对方法的两个参数稍加解释,第一个sortgid是导入数据所属的gid。第二个是已经上传的excel文件的相对路径。没错,这里excel表需要上传后才能使用,至于如果上传,这里不加解释,无论是自写,还是用插件,可自行解决。
这里有几个坑需要注意。
错误信息:未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序
这个表示在服务器上未安装相应的支持excel处理的数据处理引擎。处理办法:去 http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe 下载。然后安装
安装后可能仍然有问题,这个时候要对网站使用的程序池开通32位支持
该应用程序的 应用程序池 ------>选择高级设置 --------->启用32位应用程序 ------->true
弄好后如果读取excel数据语句这样写:
select * from [sheet1$],有可能会提示对应表名不正确。这是因为你的excel文件的sheet名字已经修改造成。这里我已经做了处理,使用
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
循环读取,然后取第一个。
还有一个坑就是有可能会提示excel表数据不是标准形式,这里的处理方法我已经在导出excel做了说明,这里不再赘述