Lung-Yu,Tsai 的部落格

1

<asp:FileUpload ID="FileUpload1" runat="server" />

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

//判斷有沒有選擇檔案
if (FileUpload1.HasFile)
{
	String filetype = System.IO.Path.GetExtension(FileUpload1.FileName);
	if (filetype != ".xls")
	{
		String error = @"<script>alert('檔案格式錯誤，請上傳.xls檔');</script>";
		//傳回JS片段並執行，第二個參數自行命名即可
		ClientScript.RegisterStartupScript(GetType(), filetype, error);
	}
	else
	{
		List<UserModel> users = Excel2UserModelPaser(FileUpload1);
		UserModel2DB(users);
	}
}
else
{
	String scriptStr = @"<script>alert('請選擇欲上傳之檔案!');</script>";
	//傳回JS片段並執行，第二個參數自行命名即可
	ClientScript.RegisterStartupScript(GetType(), "message", scriptStr);
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

private List<Model> Excel2UserModelPaser(FileUpload fileUpload)
{

	String filetype = System.IO.Path.GetExtension(fileUpload.FileName);
	if (filetype != ".xls")
		return null;

	List<Model> result = new List<Model>();

	HSSFWorkbook wb = new HSSFWorkbook(fileUpload.PostedFile.InputStream);
	HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);
	//由第一列取標題做為欄位名稱
	HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
	int cellCount = headerRow.LastCellNum;

	for (int i = 1; i <= sheet.LastRowNum; i++)
	{
		try
		{
			HSSFRow row = (HSSFRow)sheet.GetRow(i);
			if (row == null)
				continue;

			Model model = new Model();
			model.col_1 = row.GetCell(0).ToString().Trim();
			model.col_2 = row.GetCell(1).ToString().Trim();
			model.col_3 = row.GetCell(2).ToString().Trim();

			result.Add(userModel);
		}
		catch (NullReferenceException)
		{
			continue;
		}
	}

	return result;
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

private void UserModel2DB(List<model> models )
{
	SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
	string sqlStr = @"SQL 語法";

	SqlCommand cmd = new SqlCommand(sqlStr, conn);
	conn.Open();
	

	cmd.CommandText = sqlStr;
	foreach (UserModel model in models)
	{
		cmd.Parameters.Clear();
		cmd.Parameters.AddWithValue("@1", model.col_1);
		cmd.Parameters.AddWithValue("@2", model.col_2);
		cmd.Parameters.AddWithValue("@3", model.col_3);
		int rows = cmd.ExecuteNonQuery();
	}

	conn.Close();
}