本文通过上传一个excle文件，并规定具体格式，读取数据填充到gridview中
前台页面代码如下：
<div style=" width:500px; height:400px; text-align:center">
<form id="form1" runat="server" enctype="multipart/form-data">
<asp:Label runat="server">我们只支持excel(.xls/.xlsx)的上传,模板点击这里下载</asp:Label>
<asp:Button ID="LoadTemplate" runat="server" Text="下载模板" OnClick="LoadTemplate_Click"/>
<div id="MyFile" style=" float:left; margin-top:20px; margin-bottom:20px">
<input name="File" type="file" class="FileUpStyle" id="File1" />
<asp:Button runat="server" Text="上传" ID="Upload" BorderColor="Desktop" BorderWidth="1px"
Height="20px" Width="60px" OnClick="Upload_Click"></asp:Button>
</div>
<asp:GridView ID="GridView1" BorderColor="Black" runat="server" AutoGenerateColumns="False"
Font-Size="12px" Width="530px" AllowSorting="True">
<Columns>
<asp:BoundField DataField="holiday" HeaderText="holiday" />
</Columns>
<HeaderStyle BackColor="Azure" Font-Size="12px" HorizontalAlign="Center" />
<RowStyle HorizontalAlign="Center" />
<PagerStyle HorizontalAlign="Center" />
</asp:GridView>
</form>
</div>
?
方法一是使用office提供的类库来获取：
static string filePath = string.Empty;
/// <summary>
/// 删除文件
/// </summary>
/// <param name="path"></param>
private void DeleteFile(DirectoryInfo path)
{
foreach (DirectoryInfo d in path.GetDirectories())
{
DeleteFile(d);
}
foreach (FileInfo f in path.GetFiles())
{
f.Delete();
}
}
/// <summary>
/// 上传文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Upload_Click(object sender, EventArgs e)
{
HttpFileCollection files = System.Web.HttpContext.Current.Request.Files;
for (int i = 0; i < files.Count; i++)
{
string ShortFileName = files[i].FileName.Substring(files[i].FileName.LastIndexOf("\\") + 1);
string type = ShortFileName.Substring(files[i].FileName.LastIndexOf(".") + 1);
if (ShortFileName == "")
{
Response.Write("<script>alert('未选择文件！');</script>");
}
else if (!type.ToLower().Equals("xls") && !type.ToLower().Equals("xlsx"))
{
Response.Write("<script>alert('文件格式不正确！必须是(.xls/.xlsx)');</script>");
}
else if (ShortFileName != "" && (type.ToLower().Equals("xls") || type.ToLower().Equals("xlsx")))
{
filePath = Server.MapPath("~/bin/Files/ExcelFiles/" + ShortFileName);
DeleteFile(new DirectoryInfo(Server.MapPath("~/bin/Files/ExcelFiles/")));
files[i].SaveAs(filePath);
DataSet ds = ReadExcel(filePath,type);
if (ds == null)
{
Response.Write("<script>alert('文件格式不正确！');</script>");
return;
}
bool folat = ISQualified(ds);
if (folat)
{
Response.Write("<script>alert('日期格式不正确！应该为yyyy-MM-dd');</script>");
}
else
{
Save(ds);
}
}
}
}
/// <summary>
/// 保存到数据库
/// </summary>
/// <param name="ds"></param>
private void Save(DataSet ds)
{
GridView1.DataSource = ds;
GridView1.DataBind();
}
/// <summary>
/// 读取excel
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
private DataSet ReadExcel(string path,string type)
{
DataSet ds;
try
{
ReadExcel readExcel = new ReadExcel();
string sheetName = readExcel.ExcelSheetName(filePath,type)[0].ToString();
ds = readExcel.ExcelDataSource(filePath,type,sheetName);
}
catch (Exception e)
{
return null;
}
return ds;
}
/// <summary>
/// 判断时间格式
/// </summary>
/// <param name="ds"></param>
/// <returns></returns>
private bool ISQualified(DataSet ds)
{
bool folat = false;
if (ds == null || ds.Tables[0].Rows.Count < 1)
return true;
foreach (DataRow d in ds.Tables[0].Rows)
{
try
{
string holidayyear = d["holiday"].ToString().Trim();
if (string.IsNullOrEmpty(holidayyear))
continue;
DateTime b = DateTime.ParseExact(holidayyear, "yyyy-MM-dd 0:00:00", CultureInfo.InvariantCulture);
folat = false;
}
catch (Exception e)
{
folat = true;
return folat;
}
}
return folat;
}
/// <summary>
/// 文件模板下载
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void LoadTemplate_Click(object sender, EventArgs e)
{
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=Template.xls");
string filename = Server.MapPath("~/bin/Files/Template/Template.xls");
Response.TransmitFile(filename);
}
?方法二是引用npoi类库进行读取，相比上一个方法，这个更灵活，可操作性更高：
static string filePath = string.Empty;
/// <summary>
/// 删除文件
/// </summary>
/// <param name="path"></param>
private void DeleteFile(DirectoryInfo path)
{
foreach (DirectoryInfo d in path.GetDirectories())
{
DeleteFile(d);
}
foreach (FileInfo f in path.GetFiles())
{
f.Delete();
}
}
/// <summary>
/// 文件模板下载
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void LoadTemplate_Click(object sender, EventArgs e)
{
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=Template.xls");
string filename = Server.MapPath("~/bin/Files/Template/Template.xls");
Response.TransmitFile(filename);
}
/// <summary>
/// 上传文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Upload_Click(object sender, EventArgs e)
{
HttpFileCollection files = System.Web.HttpContext.Current.Request.Files;
for (int i = 0; i < files.Count; i++)
{
string ShortFileName = files[i].FileName.Substring(files[i].FileName.LastIndexOf("\\") + 1);
string type = ShortFileName.Substring(files[i].FileName.LastIndexOf(".") + 1);
if (ShortFileName == "")
{
Response.Write("<script>alert('未选择文件！');</script>");
}
else if (!type.ToLower().Equals("xls") && !type.ToLower().Equals("xlsx"))
{
Response.Write("<script>alert('文件格式不正确！必须是(.xls/.xlsx)');</script>");
}
else if (ShortFileName != "" && (type.ToLower().Equals("xls") || type.ToLower().Equals("xlsx")))
{
filePath = Server.MapPath("~/bin/Files/ExcelFiles/" + ShortFileName);
DeleteFile(new DirectoryInfo(Server.MapPath("~/bin/Files/ExcelFiles/")));
files[i].SaveAs(filePath);
DataTable dt = GetExcelInfo(filePath);
if (dt == null)
{
Response.Write("<script>alert('文件格式不正确！');</script>");
return;
}
bool folat = ISQualified(dt);
if (folat)
{
Response.Write("<script>alert('日期格式不正确！应该为yyyy-MM-dd');</script>");
}
else
{
Save(dt);
}
}
}
}
private DataTable GetExcelInfo(string path)
{
using (FileStream stream = new FileStream(@path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
HSSFWorkbook workbook = new HSSFWorkbook(stream);
//获取excel的第一个sheet
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
DataTable table = new DataTable();
//获取sheet的首行
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum;
for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
HSSFCell hsc = (HSSFCell)row.GetCell(j);
if (hsc != null && !string.IsNullOrEmpty(hsc.ToString()))
{
try
{
string date = row.GetCell(j).DateCellValue.ToString();
dataRow[j] = date;
}
catch (Exception e)
{
dataRow[j] = null;
}
}
}
table.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
return table;
}
}
private bool ISQualified(DataTable dt)
{
bool folat = false;
if (dt == null || dt.Rows.Count < 1)
return true;
foreach (DataRow d in dt.Rows)
{
try
{
string holidayyear = d["holiday"].ToString().Trim();
if (string.IsNullOrEmpty(holidayyear))
continue;
DateTime b = DateTime.ParseExact(holidayyear, "yyyy-MM-dd 0:00:00", CultureInfo.InvariantCulture);
folat = false;
}
catch (Exception e)
{
folat = true;
return folat;
}
}
return folat;
}
/// <summary>
/// 保存到数据库
/// </summary>
/// <param name="ds"></param>
private void Save(DataTable dt)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
?
?