protected void btnImport_Click(object sender, EventArgs e)
{
try
{
file.PostedFile.SaveAs(Server.MapPath("ExcelFile/Temp.xls")); String MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("ExcelFile/Temp.xls") + "; Extended Properties=Excel 8.0;";
OleDbConnection ExcelConn = new OleDbConnection(MyConnection);
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", ExcelConn);
DataSet ds = new DataSet();
ExcelConn.Open();
adapter.Fill(ds, "exceldata"); for (int i = 0; i < ds.Tables["exceldata"].Rows.Count; i++)
{
DataRow dr = ds.Tables["exceldata"].Rows[i];
DOMS.DoseTLDDa da = new DOMS.DoseTLDDa();
da.Insert(dr[0].ToString(), Convert.ToDateTime(dr[1].ToString()), Convert.ToDateTime(dr[2].ToString()), Convert.ToDecimal(dr[3].ToString()), dr[4].ToString());
}
Response.Write("<script>alert('导入成功!');window.location.href=window.location.href;</script>");
//Toolbar.refreshGridView();
adapter.Dispose();
ExcelConn.Close();
}
catch
{
//throw (ex);
Response.Write("<script>alert('导入失败,请检查格式!')</script>");
}
}
请教各位高手帮帮忙!谢谢
{
try
{
file.PostedFile.SaveAs(Server.MapPath("ExcelFile/Temp.xls")); String MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("ExcelFile/Temp.xls") + "; Extended Properties=Excel 8.0;";
OleDbConnection ExcelConn = new OleDbConnection(MyConnection);
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", ExcelConn);
DataSet ds = new DataSet();
ExcelConn.Open();
adapter.Fill(ds, "exceldata"); for (int i = 0; i < ds.Tables["exceldata"].Rows.Count; i++)
{
DataRow dr = ds.Tables["exceldata"].Rows[i];
DOMS.DoseTLDDa da = new DOMS.DoseTLDDa();
da.Insert(dr[0].ToString(), Convert.ToDateTime(dr[1].ToString()), Convert.ToDateTime(dr[2].ToString()), Convert.ToDecimal(dr[3].ToString()), dr[4].ToString());
}
Response.Write("<script>alert('导入成功!');window.location.href=window.location.href;</script>");
//Toolbar.refreshGridView();
adapter.Dispose();
ExcelConn.Close();
}
catch
{
//throw (ex);
Response.Write("<script>alert('导入失败,请检查格式!')</script>");
}
}
请教各位高手帮帮忙!谢谢
解决方案 »
- 请教向WCF提交DataSet或DataTable,返回远程服务器返回了意外响应错误!
- VS2008 自动关闭问题
- 关于asp.net的源码安全问题
- 求助一代代码!!谢谢各位!!!!!
- 怎么在前台声明namespace
- 求一正则表达式写法
- 请问:DataGrid控件为何有时候显示不出来?
- 用.net作文件下载,中文名限制长度,如何处理,急!!!!!!!!!在线等。等思归大哥。
- js中,如何对一字串进行base64编码和解码?
- 如何把"_"编码成 "%5F",就是如何把字符串编成cookie里面允许的字符串?
- 请高手帮我看一下这段代码,谢谢
- gridview中使用checkbox 完成批量修改功能.
using(OleDbConnection OleConn = new OleDbConnection(strConn))
{
OleConn.Open();
String sql = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet ds= new DataSet();
OleDaExcel.Fill(ds);
OleConn.Close();
}
检查excel
我导入的数据是我开始导出来的Excel的数据;我再导入就报这个异常;
我用记事本打开Excel 有数据但是是HTML代码显示的不知道怎么解决!谢谢 我的代码就是那样些的!
ExcelConn.Open()位置错了 OleDbConnection ExcelConn = new OleDbConnection(MyConnection);
ExcelConn.Open();//先打开链接才能读取。
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", ExcelConn);
DataSet ds = new DataSet(); adapter.Fill(ds, "exceldata");
<meta http-equiv="Content-Type" content="text/html;charset=GB2312">
<table cellspacing="0" rules="all" border="1" style="border-collapse:collapse;vnd.ms-excel.numberformat:@">
<tr align="center">
<td>姓名</td><td>性别</td><td>身份证号码</td><td>TLD编号</td><td>TLD佩带日期起</td><td>TLD佩带日期止</td><td>Hp(10)测量值</td><td>备注</td>
</tr><tr align="center">
<td>彭素岚</td><td>女</td><td> </td><td>PCITPSL</td><td>2010-8-11 0:00:00</td><td>2010-8-20 0:00:00</td><td>0.22</td><td> </td>
</tr><tr align="center">
<td>徐莎</td><td>女</td><td>3242342</td><td>PCITXSH</td><td>2010-9-22 0:00:00</td><td>2010-9-22 0:00:00</td><td>222</td><td> </td>
</tr><tr align="center">
<td>liyang</td><td>男</td><td>123456789112345678</td><td>pciliy</td><td>2010-10-14 0:00:00</td><td>2010-10-14 0:00:00</td><td>0.0020</td><td> </td>
</tr>
</table>就是导入不进去
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Excel;
using System.IO;
/// <summary>
/// Summary description for excel2
/// </summary>
public class excel2
{
public excel2()
{
//
// TODO: Add constructor logic here
//
}
public static void biaozhunexcel2(DataSet ds, report[] report)
{ System.Data.DataTable dt = ds.Tables[0]; string FileName = HttpContext.Current.Server.MapPath("./") + @"download\dao2.xls";
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
Excel.Application xlApp = null;
xlApp = new Excel.Application();
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Range range; //写入字段
for (int i = 0; i < report.Length; i++)
{
worksheet.Cells[1, i + 1] = report[i].chinese;
range = (Excel.Range)worksheet.Cells[1, i + 1];
} int sobad = 0;
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < report.Length; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
}
rowRead++;
sobad = r;
}
workbook.Saved = true;
workbook.SaveCopyAs(FileName);
} /// <summary>
/// 下载文件
/// </summary>
/// <param name="filename">文件物理地址</param>
// protected void DownloadFile(string filename)
// {// string saveFileName = "test.xls";
// int intStart = filename.LastIndexOf("\")+1;
// saveFileName = filename.Substring(intStart,filename.Length-intStart);// System.IO.FileInfo fi=new System.IO.FileInfo(filename);
// string fileextname=fi.Extension;
// string DEFAULT_CONTENT_TYPE = "application/unknown";
// RegistryKey regkey,fileextkey;
// string filecontenttype;
// try
// {
// regkey=Registry.ClassesRoot;
// fileextkey=regkey.OpenSubKey(fileextname);
// filecontenttype=fileextkey.GetValue("Content Type",DEFAULT_CONTENT_TYPE).ToString();
// }
// catch
// {
// filecontenttype=DEFAULT_CONTENT_TYPE;
// }
// Response.Clear();
// Response.Charset = "utf-8";
// Response.Buffer= true;
// this.EnableViewState = false;
//Response.ContentEncoding = System.Text.Encoding.UTF8;// Response.AppendHeader("Content-Disposition","attachment;filename=" + saveFileName);
//Response.ContentType=filecontenttype;// Response.WriteFile(filename);
// Response.Flush();
// Response.Close();// Response.End();
// }
public static bool ResponseFile(HttpRequest _Request,HttpResponse _Response,string _fileName,string _fullPath, long _speed)
{
try
{
FileStream myFile = new FileStream(_fullPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
BinaryReader br = new BinaryReader(myFile);
try
{
_Response.AddHeader("Accept-Ranges", "bytes");
_Response.Buffer = false;
long fileLength = myFile.Length;
long startBytes = 0;
double pack = 10240; //10K bytes
//int sleep = 200; //每秒5次 即5*10K bytes每秒
int sleep = (int)Math.Floor(1000 * pack / _speed) + 1;
if (_Request.Headers["Range"] != null)
{
_Response.StatusCode = 206;
string[] range = _Request.Headers["Range"].Split(new char[] {'=', '-'});
startBytes = Convert.ToInt64(range[1]);
}
_Response.AddHeader("Content-Length", (fileLength - startBytes).ToString());
if (startBytes != 0)
{
//Response.AddHeader("Content-Range", string.Format(" bytes {0}-{1}/{2}", startBytes, fileLength-1, fileLength));
}
_Response.AddHeader("Connection", "Keep-Alive");
_Response.ContentType = "application/octet-stream";
_Response.AddHeader("Content-Disposition","attachment;filename=" + HttpUtility.UrlEncode(_fileName,System.Text.Encoding.UTF8) );
br.BaseStream.Seek(startBytes, SeekOrigin.Begin);
int maxCount = (int) Math.Floor((fileLength - startBytes) / pack) + 1; for (int i = 0; i < maxCount; i++)
{
if (_Response.IsClientConnected)
{
_Response.BinaryWrite(br.ReadBytes(int.Parse(pack.ToString())));
System.Threading.Thread.Sleep(sleep);
}
else
{
i=maxCount;
}
}
}
catch
{
return false;
}
finally
{
br.Close(); myFile.Close();
}
}
catch
{
return false;
}
return true;
}
}2.调用工具类导出excel
//这个report 其实 就只是 有 两个 成员变量的 结构,随便写的
report[] a = new report[2] { new report("XX", "编码"),
new report("material_name", "名称")
}; excel2.biaozhunexcel2(dao1.returndataset(sql), a);
Page.Response.Clear();
string excelFileName = this.Page.Title + ".xls"; bool success = excel2.ResponseFile(Page.Request, Page.Response, excelFileName, Server.MapPath("./") + @"download\dao2.xls", 1024000);
if (!success)
Response.Write("下载文件出错!");
Page.Response.End();
注意数据库连接字符串的写法。
{
string strConn = @"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;DBQ=" + HttpContext.Current.Server.MapPath("./") + @"download\" + strFileName;
OdbcDataAdapter ExcelDA = new OdbcDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataSet ExcelDs = new DataSet();
try
{
ExcelDA.Fill(ExcelDs, "ExcelInfo");
}
catch (Exception err)
{
System.Console.WriteLine(err.ToString());
System.Web.HttpContext.Current.Response.Write(err.ToString()); }
return ExcelDs;
}