上傳excel，再把excel的內容用gridview的方式顯示，加上一些判斷
前台很簡單，一個內建的FileUpLoad、一個Button、一個Gridview，利用multview做切換，平時gridview是隱藏。
<asp:MultiView ID="MultiView1" runat="server">
<asp:View ID="View1" runat="server">
<table width="95%">
<tr>
<td style="background-color: #dbf1fc;">
檔案上傳：<asp:FileUpload ID="fuBtachFile" runat="server" />
</td>
</tr>
<tr>
<td rowspan="2" style="height: 24px; text-align: center; background-color: #dbf1fc;">
<asp:Button ID="btnGoStep2" runat="server" Text="下一步：檢核作業" OnClick="btnGoStep2_Click" />
</td>
</tr>
</table>
</asp:View>
<asp:View ID="View2" runat="server">
<table width="100%">
<tr>
<td>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" Width="100%">
<Columns>
<asp:BoundField DataField="EEMSPSerNo" HeaderText="流水號" ItemStyle-Width="9%" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="ViolateName" HeaderText="行為人姓名" ItemStyle-Width="15%" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="IdNo" HeaderText="身分證字號/統編" ItemStyle-Width="15%" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="IsPass" HeaderText="是否通過" ItemStyle-Width="12%" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="NoPassReason" HeaderText="未通過原因" ItemStyle-Width="28%" ItemStyle-HorizontalAlign="Center" />
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</asp:View>
</asp:MultiView>
其中上傳的檔案只有行為人姓名和身分證字號，但gridview會顯示流水號、姓名、身分證字號、是否通過、未通過原因
後台像這樣，下面這段是把上傳excel先上傳，之後再抓下來轉成gridview。
protected void btnGoStep2_Click(object sender, EventArgs e)
{
if (!fuBtachFile.HasFile)
{
this.Controls.Add(new LiteralControl("<script language=javascript>alert('請上傳檔案');</script>"));
return;
}
if (System.IO.Path.GetExtension(fuBtachFile.FileName) != ".xls")
{
this.Controls.Add(new LiteralControl("<script language=javascript>alert('上傳檔案必須為xls');</script>"));
return;
}
//檔案名稱
string FileName = System.IO.Path.GetRandomFileName() + ".xls";
//檔案路徑
string PathFileName = Server.MapPath(ConfigurationManager.AppSettings["PenaltyUploadFilePath"]) + FileName;
fuBtachFile.PostedFile.SaveAs(PathFileName);
SetStep2(PathFileName, FileName);
}
下面先把路徑跟名稱存在session，其中totcount、badcount是DEBUG用的，方便知道錯多少行，原本的資料量很多有80幾行，修改後才丟上來。
private void SetStep2(string aPathFileName, string sFileName)//檢核作業
{
Session["ReNewBatchNew_UpPathFileName"] = aPathFileName;
Session["Regulate_FileName"] = sFileName;
int TotCount, BadCount;
string DataNull = "";
DataTable tUpFileTable;
bool GetUpFileDataIsOK = UpFileDataToDataTable(aPathFileName, out tUpFileTable);
bool GetCheckDataIsOK = CheckUpFileData(tUpFileTable, out TotCount, out BadCount);
Session["ReNewBatchNew_FileTable"] = tUpFileTable;
GridView1.DataSource = (DataTable)tUpFileTable;
GridView1.DataBind();
if (GetCheckDataIsOK && GetDataIsNull)
{
btnInsert.Visible = true;
}
else
{
btnInsert.Visible = false;
}
MultiView1.ActiveViewIndex = 1;
}
下面是建立table跟把excel資料填入該table。
private bool UpFileDataToDataTable(string aPathFileName, out DataTable tTable)//資料填入table
{
tTable = PayBatchNew_EmptyDataTable();
HSSFWorkbook hssfworkbook;
//open file
System.IO.FileStream fs = new FileStream(aPathFileName, FileMode.Open);
hssfworkbook = new HSSFWorkbook(fs);
fs.Close();
//select sheet
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
//loop data
int rowCount = sheet.LastRowNum;
int RowStart = sheet.FirstRowNum + 1;
try
{
for (int i = RowStart; i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = tTable.NewRow();
// 給dateRow值
dataRow["EEMSPSerNo"] = i - RowStart + 1;//流水號
if (row.GetCell(0) != null) { dataRow["ViolateName"] = row.GetCell(0).ToString().Trim(); }//名稱
if (row.GetCell(1) != null) { dataRow["IdNo"] = row.GetCell(1).ToString().Trim(); }//身分證字號
dataRow["IsPass"] = "";
dataRow["NoPassReason"] = "";
tTable.Rows.Add(dataRow);
}
}
catch
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.GetType(), "", "alert('上傳檔案不符合格式!');history.back(-1);", true);
}
return true;
}
private DataTable PayBatchNew_EmptyDataTable()//建立table
{
DataTable workTable = new DataTable("ReNewBatchNew");
workTable.Columns.Add("EEMSPSerNo", typeof(int));//流水號
workTable.Columns.Add("ViolateName", typeof(String));//行為人姓名
workTable.Columns.Add("IdNo", typeof(String));//身分證字號/統編
workTable.Columns.Add("IsPass", typeof(String));//是否通過檢核
workTable.Columns.Add("NoPassReason", typeof(String));//未通過檢核原因
return workTable;
}
最後是檢查，利用foreach把datarow中每一行做檢查，這邊只做了檢查空白而已。
private bool CheckUpFileData(DataTable tUpFileTable, out int TotCount, out int BadCount)//檢查資料行
{
TotCount = 0;
BadCount = 0;
foreach (DataRow tRow in tUpFileTable.Rows)
{
string ErrMsg;
TotCount += 1;
tRow["IsPass"] = "";
tRow["NoPassReason"] = "";
if (tRow["ViolateName"].ToString().Trim() == "")
{
DataNull += "行為人姓名不可為空白 ";
tRow["NoPassReason"] += DataNull;
result = false;
}
if (tRow["NoPassReason"].ToString().Trim() != "")
{
tRow["IsPass"] = "否";
BadCount += 1;
}
else
{
tRow["IsPass"] = "是";
}
}
if (TotCount > 0 && BadCount == 0)//有資料且無錯誤
{
return true;
}
else
{
return false;
}
}
以上。
沒有留言:
張貼留言