protected void Button1_Click(object sender, EventArgs e)
{
if (ExcelFileUpload.HasFile == false)//HasFile用来检查FileUpload是否有文件
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return; //当无文件时,返回
}
string IsXls = Path.GetExtension(ExcelFileUpload.FileName).ToString().ToLower(); //System.IO.Path.GetExtension获得文件的扩展名
if (IsXls != ".xlsx" && IsXls != ".xls")
{
Response.Write(ExcelFileUpload.FileName);
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return; //当选择的不是Excel文件时,返回
}
string filename = ExcelFileUpload.FileName; //获取Execle文件名 ]
string savePath = Server.MapPath(("UploadExcel\\") + filename); //Server.MapPath 服务器上的指定虚拟路径相对应的物理文件路径
DataTable ds = new DataTable();
ExcelFileUpload.SaveAs(savePath); //将文件保存到指定路径
DataTable dt = GetExcelDatatable(savePath); //读取excel数据
List <Model精度检测> regList = ConvertDtToInfo(dt); //将datatable转为list
}
protected void btnUpload_Click(object sender, EventArgs e)
{
string UploadDir = Server.MapPath("~/faculty/QuestionImports/");
string conString = string.Empty;
string FileName = Path.GetFileName(ExcelFileUpload.PostedFile.FileName);
string extension = Path.GetExtension(ExcelFileUpload.PostedFile.FileName);
string excelPath = UploadDir + "QuestionsImport-" + DateTime.Now.ToString(("dd-MM-yyyy--hh-mm--tt")) + extension;
ExcelFileUpload.SaveAs(excelPath);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
dtExcelData.Columns.AddRange(new DataColumn[12] {
new DataColumn("Department Id", typeof(int)),
new DataColumn("Semester", typeof(int)),
new DataColumn("Subject", typeof(int)),
new DataColumn("Exam Code", typeof(string)),
new DataColumn("Question Type", typeof(string)),
new DataColumn("Question", typeof(string)),
new DataColumn("Option A", typeof(string)),
new DataColumn("Option B", typeof(string)),
new DataColumn("Option C", typeof(string)),
new DataColumn("Option D", typeof(string)),
new DataColumn("Correct Answer", typeof(int)),
new DataColumn("Marks", typeof(int)),
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(db.DbConnect()))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.questions";
sqlBulkCopy.ColumnMappings.Add("Department Id", "dept_id");
sqlBulkCopy.ColumnMappings.Add("Semester", "sem_id");
sqlBulkCopy.ColumnMappings.Add("Subject", "subject_id");
sqlBulkCopy.ColumnMappings.Add("Exam Code", "exam_code");
sqlBulkCopy.ColumnMappings.Add("Question Type", "question_type");
sqlBulkCopy.ColumnMappings.Add("Question", "question");
sqlBulkCopy.ColumnMappings.Add("Option A", "opt_a");
sqlBulkCopy.ColumnMappings.Add("Option B", "opt_b");
sqlBulkCopy.ColumnMappings.Add("Option C", "opt_c");
sqlBulkCopy.ColumnMappings.Add("Option D", "opt_d");
sqlBulkCopy.ColumnMappings.Add("Correct Answer", "correct_ans");
sqlBulkCopy.ColumnMappings.Add("Marks", "marks");
sqlBulkCopy.WriteToServer(dtExcelData);
}
}
}
protected DataTable ParseExcelFile()
{
DataTable dtLocal = new DataTable();
int recctr = 0;
bool FileOK = false;
if (Path.GetExtension(ExcelFileUpload.FileName).ToLower().ToString() == ".txt")
{
dtLocal.Columns.Add("Item", typeof(String));
dtLocal.Columns.Add("Cat", typeof(String));
dtLocal.Columns.Add("Plate", typeof(String));
dtLocal.Columns.Add("Var", typeof(String));
//Stream LoadingStream = ExcelFileUpload.FileContent();
Stream st = ExcelFileUpload.FileContent;
byte[] bytes = new byte[1000];
int numBytesToRead = (int)st.Length;
int numBytesRead = 0;
//lblSuccessMessage.Text = "File length = " + numBytesToRead.ToString();
string itemNo;
while (numBytesToRead > 0)
{
// Read may return anything from 0 to numBytesToRead.
int n = st.Read(bytes, 0, 16);
//lblSuccessMessage.Text = lblSuccessMessage.Text.TrimEnd() + "," + n.ToString() + "," + System.Text.Encoding.Default.GetString(bytes);
// The end of the file is reached.
if (n == 0)
{
break;
}
numBytesRead += n;
numBytesToRead -= n;
recctr++;
itemNo = System.Text.Encoding.Default.GetString(bytes).TrimEnd();
dtLocal.Rows.Add(new Object[] { itemNo.Substring(0, 14), itemNo.Substring(0, 5), itemNo.Substring(13, 1), itemNo.Substring(11, 3) });
//lblErrorMessage.Text = System.Text.Encoding.Default.GetString(bytes);
}
lblSuccessMessage.Text = "Text file processed. " + dtLocal.Rows.Count.ToString() + " lines.";
FileOK = true;
}
if (Path.GetExtension(ExcelFileUpload.FileName).ToLower().ToString() == ".xls")
{
// Upload the file to the Excel folder under the app folder to we will then have permission to open it.
//string SiteFileName = "D:\\software\\PFCApps\\IntranetSite\\CPR\\Excel\\Uploaded.xls";
string SiteFileName = Server.MapPath(
@"Excel\"
+ Session["UserName"].ToString()
+ "Upload.xls");
//SiteFileName.Replace(@"\", @"\\");
ExcelFileUpload.SaveAs(SiteFileName);
OleDbConnection cn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=" +
SiteFileName +
";Extended Properties=Excel 8.0;");
// Select the data from Sheet1 of the workbook.
OleDbDataAdapter cmd = new OleDbDataAdapter("select Item from [Sheet1$]", cn);
try
{
cn.Open();
cmd.Fill(ds);
dtLocal = ds.Tables[0];
DataColumn FirstColumn = dtLocal.Columns[0];
dtLocal.Columns.Add("Cat", typeof(String));
dtLocal.Columns.Add("Plate", typeof(String));
dtLocal.Columns.Add("Var", typeof(String));
string item = "";
foreach (DataRow row in dtLocal.Rows)
{
item = row["Item"].ToString();
row["Cat"] = item.Substring(0, 5);
row["Plate"] = item.Substring(13, 1);
row["Var"] = item.Substring(11, 3);
}
FileOK = true;
cn.Close();
lblSuccessMessage.Text = "Excel file " + ExcelFileUpload.FileName.ToString() + " processed. " + dtLocal.Rows.Count.ToString() + " lines.";
}
catch { }
}
if (!FileOK)
{
lblErrorMessage.Text = "File must be a list of items in a text file (.txt) or an Excel file (.xls). Click on Help for more info.";
}
return(dtLocal.DefaultView.ToTable());
}
protected void UploadBtn_Click(object sender, EventArgs e)
{
// Upload file
var originalFileNamefileName = Path.GetFileName(ExcelFileUpload.FileName);
// Get file extension
if (string.IsNullOrEmpty(originalFileNamefileName))
{
return;
}
var ext = originalFileNamefileName.Substring(originalFileNamefileName.LastIndexOf(".", StringComparison.Ordinal) + 1).ToLower();
var newFileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + "." + ext;
// This can be moved to Web.config
var uploadPath = Server.MapPath("~/Uploads/");
// Create directory if it does not exists
var directoryInfo = new FileInfo(uploadPath).Directory;
directoryInfo?.Create();
var uploadLocation = uploadPath + newFileName;
// Save file
ExcelFileUpload.SaveAs(uploadLocation);
// Insert into MuatNaikExcel
const string sql = @"
INSERT INTO MuatNaikExcel (NamaAsal, NamaBaru, TarikhMuatNaik, Lokasi)
VALUES (@NamaAsal, @NamaBaru, @TarikhMuatNaik, @Lokasi);
SELECT CAST(SCOPE_IDENTITY() as int)";
int muatNaikId;
using (var c = ConnectionFactory.GetConnection())
{
var excelFile = new MuatNaikExcel
{
NamaAsal = originalFileNamefileName,
NamaBaru = newFileName,
TarikhMuatNaik = DateTime.Now,
Lokasi = uploadLocation
};
muatNaikId = c.QuerySingle <int>(sql, excelFile);
}
// Baca excel menggunakan ExcelDataReader
using (var stream = ExcelFileUpload.PostedFile.InputStream)
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
// Skip first row - header row
reader.Read();
// Loop rows
while (reader.Read())
{
// Insert masuk PencapaianProgramExcel
const string insert = @"
INSERT INTO PencapaianProgramExcel (KodProgram, TarikhProgram, BilanganHari, Lulus, IdMuatNaikExcel, Ralat)
VALUES (@KodProgram, @TarikhProgram, @BilanganHari, @Lulus, @IdMuatNaikExcel, @Ralat)";
var ralat = new List <string>();
// Semak tarikh
var tarikhProgram = reader.GetValue(1);
var tarikhProgramStr = tarikhProgram?.ToString() ?? "";
try
{
DateTime.Parse(tarikhProgramStr);
}
catch (Exception exception)
{
ralat.Add(exception.ToString());
}
var row = new PencapaianProgramExcel
{
KodProgram = reader.GetValue(0).ToString(),
TarikhProgram = tarikhProgramStr,
BilanganHari = reader.GetValue(2).ToString(),
Lulus = reader.GetValue(3).ToString(),
IdMuatNaikExcel = muatNaikId,
Ralat = string.Join(", ", ralat)
};
using (var c = ConnectionFactory.GetConnection())
{
c.Execute(insert, row);
}
}
}
}
Response.Redirect($"~/PencapaianProgram/ViewExcel.aspx?Id={muatNaikId}");
}