need ddl
npoi vision 2.1.3.1
aspx
<td colspan="3">
<telerik:RadAsyncUpload ID="Q_btnEXCELChk" runat="server" MaxFileInputsCount="1" Localization-Remove="移除"
Localization-Select="瀏覽" ><%--Style="padding: 8px 0px 0px 0px !important; width: 220px!important; font-family: Microsoft JhengHei;"--%>
</telerik:RadAsyncUpload>
</td>
<td colspan="3">
<asp:Label ID="Label2" runat="server" Text="" Width="20%"></asp:Label>
<asp:Button ID="Q_btnEXCEL" runat="server" Text="匯入預算" OnClick="Q_btnEXCEL_Click"/>
</td>
--------------------------------------------
aspx.cs
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using ICSharpCode.SharpZipLib.Zip;
protected void Q_btnEXCEL_Click(object sender, EventArgs e)
{
string MSG = string.Empty;
UploadedFile file = Q_btnEXCELChk.UploadedFiles[0];
//string FileNm = DateTime.Now.ToString("yyyyMMddhhmmss_") + this.USER.EMPNO + ".xls";
//string fileth = "~/Apps/HDGBJ/Upload" + FileNm;
//Q_btnEXCELChk.UploadedFiles[0].SaveAs(Server.MapPath(fileth));
DataTable dtImport = new DataTable();
if (file.GetExtension() == ".xls")
{
HSSFWorkbook workbook_I = new HSSFWorkbook(file.InputStream);
HSSFSheet sheet_I = (HSSFSheet)workbook_I.GetSheetAt(0);
if (sheet_I != null)
{
int DataRowFrom = 1;
int DataColFrom = 0;
int HeaderRowCount = 1;
int RowCount = sheet_I.PhysicalNumberOfRows;
//* 實際匯入資料筆數
int DataRowCount = RowCount - HeaderRowCount;
//* 表頭資料列的欄位總數
int DataColCount = sheet_I.GetRow(0).PhysicalNumberOfCells;
#region 欄位
dtImport.Columns.Add("SEG", typeof(string));
dtImport.Columns.Add("CCID", typeof(string));
dtImport.Columns.Add("ACCIDNM", typeof(string));
dtImport.Columns.Add("M1", typeof(string));
dtImport.Columns.Add("M2", typeof(string));
dtImport.Columns.Add("M3", typeof(string));
dtImport.Columns.Add("M4", typeof(string));
dtImport.Columns.Add("M5", typeof(string));
dtImport.Columns.Add("M6", typeof(string));
dtImport.Columns.Add("M7", typeof(string));
dtImport.Columns.Add("M8", typeof(string));
dtImport.Columns.Add("M9", typeof(string));
dtImport.Columns.Add("M10", typeof(string));
dtImport.Columns.Add("M11", typeof(string));
dtImport.Columns.Add("M12", typeof(string));
//dtImport.Columns.Add("ITEMS", typeof(Int32));
//dtImport.Columns.Add("TRANSFEE", typeof(decimal));
#endregion
#region 塞值--先塞入datatable在做處理
for (int i = 1; DataRowCount > i; i++) //列計數
{
DataRow drImport = dtImport.NewRow();
for (int j = 0; 15 > j; j++) //資料欄位計數
{
drImport[j] = sheet_I.GetRow(i).GetCell(j);
//drImport.Table.Rows[i-1][j]= sheet_I.GetRow(i).GetCell(j);
//dtImport.Rows[i-1][j] =
}
dtImport.Rows.Add(drImport);
}
#endregion
}
}
else if (file.GetExtension() == ".xlsx")
{
///建立Excel 2007檔案
XSSFWorkbook workbook_I = new XSSFWorkbook(file.InputStream);
XSSFSheet sheet_I = (XSSFSheet)workbook_I.GetSheetAt(0);
if (sheet_I != null)
{
int DataRowFrom = 1;
int DataColFrom = 0;
int HeaderRowCount = 1;
int RowCount = sheet_I.PhysicalNumberOfRows;
//* 實際匯入資料筆數
int DataRowCount = RowCount - HeaderRowCount;
//* 表頭資料列的欄位總數
int DataColCount = sheet_I.GetRow(0).PhysicalNumberOfCells;
#region 欄位
dtImport.Columns.Add("SEG", typeof(string));
dtImport.Columns.Add("CCID", typeof(string));
dtImport.Columns.Add("ACCIDNM", typeof(string));
dtImport.Columns.Add("M1", typeof(string));
dtImport.Columns.Add("M2", typeof(string));
dtImport.Columns.Add("M3", typeof(string));
dtImport.Columns.Add("M4", typeof(string));
dtImport.Columns.Add("M5", typeof(string));
dtImport.Columns.Add("M6", typeof(string));
dtImport.Columns.Add("M7", typeof(string));
dtImport.Columns.Add("M8", typeof(string));
dtImport.Columns.Add("M9", typeof(string));
dtImport.Columns.Add("M10", typeof(string));
dtImport.Columns.Add("M11", typeof(string));
dtImport.Columns.Add("M12", typeof(string));
//dtImport.Columns.Add("ITEMS", typeof(Int32));
//dtImport.Columns.Add("TRANSFEE", typeof(decimal));
#endregion
#region 塞值--先塞入datatable在做處理
for (int i = 1; DataRowCount > i; i++) //列計數
{
DataRow drImport = dtImport.NewRow();
for (int j = 0; 15 > j; j++) //資料欄位計數
{
drImport[j] = sheet_I.GetRow(i).GetCell(j);
//drImport.Table.Rows[i-1][j]= sheet_I.GetRow(i).GetCell(j);
//dtImport.Rows[i-1][j] =
}
dtImport.Rows.Add(drImport);
}
#endregion
}
}
else
{
this.Master.AjaxManager.Alert("檔案格式錯誤");
MSG = "檔案格式錯誤";
//檔案錯誤
}
if (MSG == "")
{
//整理datatable
if (dtImport.Rows.Count > 0)
{
//移除沒有CCID的資料列
for (int i = 0; dtImport.Rows.Count > i; i++)
{
if (dtImport.Rows[i]["CCID"].ToString() == "")
{
// dt.Rows[i].Delete();
dtImport.Rows.RemoveAt(i);
}
}
db.Inport(dtImport, COMPID, Q_ddlDATAYEAR.SelectedValue.ToString(), Q_ddlYEARTYPE.SelectedValue.ToString());
}
}
}