using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using org.in2bits.MyXls;
using System.Data.OleDb;public partial class 处理Excel_OperExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{ } /// <summary>
/// 根据Excel得到dt
/// </summary>
/// <returns></returns>
public DataTable GetdtFromExcel()
{
//得到完整路径
String filePath = this.upfile.PostedFile.FileName; string strConnForExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
DataSet ds = new DataSet();
DataTable dt = new DataTable();
OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConnForExcel); myCommand.Fill(ds);
dt = ds.Tables[0];
String _tr = dt.Rows.Count.ToString();
String _td = dt.Columns.Count.ToString();
String msg = "";
//for (int i = 0; i < Convert.ToInt32(_tr); i++)
//{
// for (int j = 0; j < Convert.ToInt32(_td); j++)
// {
// msg += dt.Rows[i][j].ToString();
// }
//}
//Response.Write("<script>alert('" + msg + "');</script>");
return dt;
} /// <summary>
/// 根据dt生成新的Excel判断dt的列数
/// </summary>
/// <returns></returns>
public bool Upfile()
{
DataTable dt = GetdtFromExcel();
bool bl = false;
////得到完整路径
//String filePath = this.upfile.PostedFile.FileName;
////得到上传的文件名
//int Index1 = filePath.LastIndexOf(".");
//int Index2 = filePath.LastIndexOf("\\");
//int ResultIndex = Index1 - Index2 - 1;
//String ResultName = filePath.Substring(Index2 + 1, ResultIndex);
////得到后缀名
//String fileExtName = filePath.Substring(filePath.LastIndexOf(".") + 1); //XlsDocument xls = new XlsDocument();
//Worksheet sheet = xls.Workbook.Worksheets.AddNamed("sheet1"); //xls.Workbook.Worksheets.AddNamed("Sheet1"); //Cells cells = sheet.Cells; if(dt.Columns.Count>4)
{
bl = false;
}
if (dt.Columns.Count <= 4)
{
//如果要分割多余的列,进行下列操作--产生新的Excel,名字和原来一样
//Cell cell = cells.AddValueCell(1, 1, "列1");
//cell.Font.ColorIndex = 2;
//cell = cells.AddValueCell(1, 2, "列2");
//cell.Font.ColorIndex = 2;
//cell = cells.AddValueCell(1, 3, "列3");
//cell.Font.ColorIndex = 2;
//cell = cells.AddValueCell(1, 4, "列4");
//cell.Font.ColorIndex = 2; //for (int i = 0; i < dt.Rows.Count; i++)
//{
// cells.AddValueCell(i + 2, 1, dt.Rows[i][0].ToString());
// cells.AddValueCell(i + 2, 2, dt.Rows[i][1].ToString());
// cells.AddValueCell(i + 2, 3, dt.Rows[i][2].ToString());
// cells.AddValueCell(i + 2, 4, dt.Rows[i][3].ToString());
//}
//xls.FileName = ResultName;
bl = true;
}
return bl;
} /// <summary>
/// 上传文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btn_upfile_Click(object sender, EventArgs e)
{
bool bl = Upfile(); //得到完整路径
String filePath = this.upfile.PostedFile.FileName;
//得到上传的文件名
int Index1 = filePath.LastIndexOf(".");
int Index2 = filePath.LastIndexOf("\\");
int ResultIndex = Index1 - Index2 - 1;
String ResultName = filePath.Substring(Index2 + 1, ResultIndex);
//得到后缀名
String fileExtName = filePath.Substring(filePath.LastIndexOf(".") + 1); //设置上传路径
String mPath = AppDomain.CurrentDomain.BaseDirectory.ToString() + "上传文件目录"; if (bl)
{
this.upfile.PostedFile.SaveAs(mPath + "\\" + ResultName + "." + fileExtName);
Response.Write("<script>alert('上传成功');</script>");
}
else
{
Response.Write("<script>alert('超过4列,上传失败');</script>");
}
}
}小弟遇到的问题就是 上传Excel需要控制4列,我例外写了个方法,导出datalist成Excel,只有4列(没有打开过),
结果String _td = dt.Columns.Count.ToString() == 1;当我在上传前打开那个文件并且保存下,String _td = dt.Columns.Count.ToString() == 4了 很奇怪 希望高手给答案 (难道上传Excel之前要格式化下???)
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using org.in2bits.MyXls;
using System.Data.OleDb;public partial class 处理Excel_OperExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{ } /// <summary>
/// 根据Excel得到dt
/// </summary>
/// <returns></returns>
public DataTable GetdtFromExcel()
{
//得到完整路径
String filePath = this.upfile.PostedFile.FileName; string strConnForExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
DataSet ds = new DataSet();
DataTable dt = new DataTable();
OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConnForExcel); myCommand.Fill(ds);
dt = ds.Tables[0];
String _tr = dt.Rows.Count.ToString();
String _td = dt.Columns.Count.ToString();
String msg = "";
//for (int i = 0; i < Convert.ToInt32(_tr); i++)
//{
// for (int j = 0; j < Convert.ToInt32(_td); j++)
// {
// msg += dt.Rows[i][j].ToString();
// }
//}
//Response.Write("<script>alert('" + msg + "');</script>");
return dt;
} /// <summary>
/// 根据dt生成新的Excel判断dt的列数
/// </summary>
/// <returns></returns>
public bool Upfile()
{
DataTable dt = GetdtFromExcel();
bool bl = false;
////得到完整路径
//String filePath = this.upfile.PostedFile.FileName;
////得到上传的文件名
//int Index1 = filePath.LastIndexOf(".");
//int Index2 = filePath.LastIndexOf("\\");
//int ResultIndex = Index1 - Index2 - 1;
//String ResultName = filePath.Substring(Index2 + 1, ResultIndex);
////得到后缀名
//String fileExtName = filePath.Substring(filePath.LastIndexOf(".") + 1); //XlsDocument xls = new XlsDocument();
//Worksheet sheet = xls.Workbook.Worksheets.AddNamed("sheet1"); //xls.Workbook.Worksheets.AddNamed("Sheet1"); //Cells cells = sheet.Cells; if(dt.Columns.Count>4)
{
bl = false;
}
if (dt.Columns.Count <= 4)
{
//如果要分割多余的列,进行下列操作--产生新的Excel,名字和原来一样
//Cell cell = cells.AddValueCell(1, 1, "列1");
//cell.Font.ColorIndex = 2;
//cell = cells.AddValueCell(1, 2, "列2");
//cell.Font.ColorIndex = 2;
//cell = cells.AddValueCell(1, 3, "列3");
//cell.Font.ColorIndex = 2;
//cell = cells.AddValueCell(1, 4, "列4");
//cell.Font.ColorIndex = 2; //for (int i = 0; i < dt.Rows.Count; i++)
//{
// cells.AddValueCell(i + 2, 1, dt.Rows[i][0].ToString());
// cells.AddValueCell(i + 2, 2, dt.Rows[i][1].ToString());
// cells.AddValueCell(i + 2, 3, dt.Rows[i][2].ToString());
// cells.AddValueCell(i + 2, 4, dt.Rows[i][3].ToString());
//}
//xls.FileName = ResultName;
bl = true;
}
return bl;
} /// <summary>
/// 上传文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btn_upfile_Click(object sender, EventArgs e)
{
bool bl = Upfile(); //得到完整路径
String filePath = this.upfile.PostedFile.FileName;
//得到上传的文件名
int Index1 = filePath.LastIndexOf(".");
int Index2 = filePath.LastIndexOf("\\");
int ResultIndex = Index1 - Index2 - 1;
String ResultName = filePath.Substring(Index2 + 1, ResultIndex);
//得到后缀名
String fileExtName = filePath.Substring(filePath.LastIndexOf(".") + 1); //设置上传路径
String mPath = AppDomain.CurrentDomain.BaseDirectory.ToString() + "上传文件目录"; if (bl)
{
this.upfile.PostedFile.SaveAs(mPath + "\\" + ResultName + "." + fileExtName);
Response.Write("<script>alert('上传成功');</script>");
}
else
{
Response.Write("<script>alert('超过4列,上传失败');</script>");
}
}
}小弟遇到的问题就是 上传Excel需要控制4列,我例外写了个方法,导出datalist成Excel,只有4列(没有打开过),
结果String _td = dt.Columns.Count.ToString() == 1;当我在上传前打开那个文件并且保存下,String _td = dt.Columns.Count.ToString() == 4了 很奇怪 希望高手给答案 (难道上传Excel之前要格式化下???)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货