问题描述
在我的excel记录中如下所示
星期一
会话
日期课程session1 session2
副学院副学院
BTech1 A RJ B RK
BTech2 B SK C SV
18年8月18日Btech3 C MV D SJ
GP E SV F MJ
DME3 GRY H RN
BTech4 i VJ j SK
BTech5 k RK L GV
19年8月19日Btech6 M MV N TY
GP6 O SV P MJ
BME Q MY R VB
我正在将以下excel记录导出到Gridview中，如下所示.
该代码如下；
受保护的无效btnimport_Click(对象发送者，EventArgs e)
{
import();
}
私人void import()
{
字符串路径= @"C:\ FACULTYT.xlsx
GvSch.DataSource = ImportExcelXLS(path，false);
GvSch.DataBind();
GvSch.Visible = true;
}
公共静态数据集ImportExcelXLS(string FileName，bool hasHeaders)
{
字符串HDR = hasHeaders吗? 是":否"；
字符串strConn;
如果(FileName.Substring(FileName.LastIndexOf(''.'')).ToLower()==".xlsx")
strConn ="Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + FileName +;扩展属性= \" Excel 12.0; HDR ="+ HDR +"; IMEX = 0 \";
其他
strConn ="Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + FileName +;扩展属性= \" Excel 8.0; HDR ="+ HDR +"; IMEX = 0 \";
DataSet输出=新的DataSet();
使用(OleDbConnection conn =新的OleDbConnection(strConn))
{
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables，新对象[] {null，null，null，"TABLE"});
foreach(schemaTable.Rows中的DataRow schemaRow)
{
字符串表= schemaRow ["TABLE_NAME"].ToString();
如果(！sheet.EndsWith("_"))
{
试试
{
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet +]"，conn);
cmd.CommandType = CommandType.Text;
DataTable outputTable = new DataTable(sheet);
output.Tables.Add(outputTable);
新的OleDbDataAdapter(cmd).Fill(outputTable);
DataTable表=新的DataTable();
table.Columns.Add("Date"，typeof(string));
table.Columns.Add("Course"，typeof(string));
table.Columns.Add("Session"，typeof(string));
table.Columns.Add("Subject"，typeof(string));
table.Columns.Add("Faculty"，typeof(string));
int irow = 0;
foreach(outputTable.Rows中的DataRow行)
{
if(row [1] .ToString()！=课程"&& row [1] .ToString()！="&& row [1] .ToString()！= null)
{
DataRow row1 = table.NewRow();
row1 ["Date"] = row [0] .ToString();
row1 ["Course"] = row [1] .ToString();
row1 ["Session"] ="1";
row1 ["Subject"] = row [2] .ToString();
row1 ["Faculty"] = row [3] .ToString();
if(row [2]！="&& row [3]！=")
table.Rows.Add(row1);
row1 = table.NewRow();
row1 ["Date"] = row [0] .ToString();
row1 ["Course"] = row [1] .ToString();
row1 ["Session"] ="2";
row1 ["Subject"] = row [4] .ToString();
row1 ["Faculty"] = row [5] .ToString();
if(row [4]！="&& row [5]！=")
table.Rows.Add(row1);
}
In My excel record as follows
Monday
Sessions
Date course session1 session2
Sub Faculty Sub Faculty
BTech1 A RJ B RK
BTech2 B SK C SV
18-Aug Btech3 C MV D SJ
GP E SV F MJ
DME3 G RY H RN
BTech4 i VJ j SK
BTech5 k RK L GV
19-Aug Btech6 M MV N TY
GP6 O SV P MJ
BME Q MY R VB
I am exporting the above excel records in Gridview as follows.
for that code as follows;
protected void btnimport_Click(object sender, EventArgs e)
{
import();
}
private void import()
{
string path = @"C:\FACULTYT.xlsx
GvSch.DataSource = ImportExcelXLS(path, false);
GvSch.DataBind();
GvSch.Visible = true;
}
public static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
{
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (FileName.Substring(FileName.LastIndexOf(''.'')).ToLower() == ".xlsx")
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
DataSet output = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow schemaRow in schemaTable.Rows)
{
string sheet = schemaRow["TABLE_NAME"].ToString();
if (!sheet.EndsWith("_"))
{
try
{
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
cmd.CommandType = CommandType.Text;
DataTable outputTable = new DataTable(sheet);
output.Tables.Add(outputTable);
new OleDbDataAdapter(cmd).Fill(outputTable);
DataTable table = new DataTable();
table.Columns.Add("Date", typeof(string));
table.Columns.Add("Course", typeof(string));
table.Columns.Add("Session", typeof(string));
table.Columns.Add("Subject", typeof(string));
table.Columns.Add("Faculty", typeof(string));
int irow = 0;
foreach (DataRow row in outputTable.Rows)
{
if (row[1].ToString() != "Course" && row[1].ToString() != "" && row[1].ToString() != null)
{
DataRow row1 = table.NewRow();
row1["Date"] = row[0].ToString();
row1["Course"] = row[1].ToString();
row1["Session"] = "1";
row1["Subject"] = row[2].ToString();
row1["Faculty"] = row[3].ToString();
if (row[2] != "" && row[3] != "")
table.Rows.Add(row1);
row1 = table.NewRow();
row1["Date"] = row[0].ToString();
row1["Course"] = row[1].ToString();
row1["Session"] = "2";
row1["Subject"] = row[4].ToString();
row1["Faculty"] = row[5].ToString();
if (row[4] != "" && row[5] != "")
table.Rows.Add(row1);
}
irow++;
}
output.Tables.Clear();
output.Tables.Add(table);
}
catch (Exception ex)
{
throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, FileName), ex);
}
finally
{
}
}
}
}
return output;
}
When i export the above excel record in to Gridview output as follows
Date Course Session Subject Faculty
18-Aug BTech1 1 A RJ
18-Aug Btech1 2 B RK
BTech2 1 B SK
Btech2 2 C SV
Btech3 1 C MV
Btech3 2 D SJ
GP 1 E SV
GP 2 F MJ
DME3 1 G RY
DME3 2 H RN
19-Aug BTech4 1 I VJ
19-Aug Btech4 2 J SK
Btech5 1 K RK
Btech5 2 L GV
Btech6 1 M MV
Btech6 2 N TY
GP6 1 O SV
GP6 2 P MJ
BME 1 Q MY
BME 2 R VB
from my above output i want the date 18-aug and 19-aug to be display for all the courses.
The 18-Aug date to be displayed onely Btech1 only for session 1 and 2.
for remaining courses the 18-Aug not displayed in the gridview.
The 19-Aug date to be displayed onely Btech4 only for session 1 and 2.
for remaining courses the 19-Aug not displayed in the gridview.
from my code what is the mistake i made please help me,
Regards,
Narasiman P.推荐答案
string dateValue = string.empty;
foreach (DataRow row in outputTable.Rows)
{
if (row[1].ToString() != "Course" && row[1].ToString() != "" && row[1].ToString() != null)
{
if (!(row[0].ToString().equals(string.empty))
{
dateValue = row[0].ToString();
}
DataRow row1 = table.NewRow();
row1["Date"] = dateValue;
row1["Course"] = row[1].ToString();
row1["Session"] = "1";
row1["Subject"] = row[2].ToString();
row1["Faculty"] = row[3].ToString();
if (row[2] != "" && row[3] != "")
table.Rows.Add(row1);
row1 = table.NewRow();
row1["Date"] = dateValue;
row1["Course"] = row[1].ToString();
row1["Session"] = "2";
row1["Subject"] = row[4].ToString();
row1["Faculty"] = row[5].ToString();
if (row[4] != "" && row[5] != "")
table.Rows.Add(row1);
}
irow++;
}
这篇关于我尝试过我的代码我希望所有课程都显示日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！