发布一个Excel导入数据到GridView的类


public class DataUtil
{
public DataUtil()
{
}
#region 导入数据到GridView
/// <summary>
/// 导入数据到GridView
/// </summary>
/// <param name="fileName">源Excel文件</param>
/// <param name="gridview">目标GridView</param>
/// <param name="keyColIndex">导入主键列</param>
/// <param name="rtnMsg">返回消息</param>
/// <returns>返回没有导入的数据DataSet</returns>
public static DataSet ExcelToGridView(string fileName, GridView gridview, int keyColIndex,string rtnMsg)
{
DataSet ds = null ;
string errMsg = string.Empty;
try
{
if ( ! fileName.EndsWith(".xls"))
{
errMsg += "只能导入Excel格式的文件。";
}
ds = ExcelHelper.ExcelToDS(fileName);
DataTable dt = ds.Tables[0];
for (int i = 0; i < gridview.Rows.Count; i++)
{
if (gridview.Rows[i].RowType != DataControlRowType.DataRow)
{
continue;
}
string key = gridview.Rows[i].Cells[keyColIndex].Text;
if (String.IsNullOrEmpty(key))
{
continue;
}
int k = 0;
for (; k < dt.Rows.Count; k++)
{
if (dt.Rows[k][gridview.Columns[keyColIndex].HeaderText].ToString().Replace(" ", "").Replace(" ", "") == key)
{
break;
}
}
if (k == dt.Rows.Count)
{
continue;
}
else
{
for (int j = 0; j < gridview.Columns.Count; j++)
{
if (gridview.Columns[j].Visible == false)
{
continue;
}
if (gridview.Rows[i].Cells[j].Controls.Count == 0)
{
continue;
}
for (int m = 0; m < gridview.Rows[i].Cells[j].Controls.Count; m++)
{
if (gridview.Rows[i].Cells[j].Controls[m].Visible == false)
{
continue;
}
if (gridview.Rows[i].Cells[j].Controls[m] is TextBox)
{
if (!dt.Columns.Contains(gridview.Columns[j].HeaderText))
{
continue;
}
TextBox txt = gridview.Rows[i].Cells[j].Controls[m] as TextBox;
string temp = dt.Rows[k][gridview.Columns[j].HeaderText].ToString();
try
{
// int intTemp = int.Parse(temp);
// txt.Text = intTemp.ToString();
txt.Text = temp; //Translate(temp);
}
catch (Exception)
{
}
break;
}
}
}
dt.Rows[k].Delete();
dt.AcceptChanges();
}
}
}
catch (Exception ex)
{
errMsg +="导入失败." + ex.Message ;
}
rtnMsg = errMsg;
return ds;
}
/// <summary>
/// 导入数据到GridView
/// </summary>
/// <param name="fileUpload">文件上传控件对象</param>
/// <param name="filePath">要存放文件的服务器路径</param>
/// <param name="gridview">目标GridView</param>
/// <param name="keyColIndex">用于导入的主键列</param>
/// <returns>返回没有导入的数据DataSet</returns>
public static DataSet ExcelToGridView(FileUpload fileUpload, string filePath, GridView gridview, int keyColIndex, string strMsg)
{
string errMsg = string.Empty;
DataSet ds = null ;
try
{
if ( ! filePath.EndsWith("\\"))
{
filePath += "\\";
}
string fileName = fileUpload.FileName.Replace(".xls", "") + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string fullFileName = filePath + fileName;
fileUpload.SaveAs(fullFileName);
ds = ExcelToGridView(fullFileName, gridview, keyColIndex,errMsg);
if ( errMsg != string.Empty)
{
errMsg += "将数据导入GridView失败." + errMsg ;
};
}
catch (Exception ex)
{
errMsg += "上传excel文件失败." ;
}
strMsg = errMsg;
return ds ;
}
#endregion
/// <summary>
/// 选定主键列后,用这方法将重复行用黄色背景标志
/// </summary>
/// <param name="gridview">目标GridView</param>
/// <param name="keyColIndex">导入主键列</param>
public static void MarkRepetitionRows(GridView gridView, int keyColIndex)
{
for (int i = 0; i < gridView.Rows.Count; i++)
{
for (int j = i + 1; j < gridView.Rows.Count; j++)
{
if (gridView.Rows[i].Cells[keyColIndex].Text == gridView.Rows[j].Cells[keyColIndex].Text)
{
gridView.Rows[i].BackColor = Color.Yellow;
gridView.Rows[j].BackColor = Color.Yellow;
}
}
}
}
}
{
public DataUtil()
{
}
#region 导入数据到GridView
/// <summary>
/// 导入数据到GridView
/// </summary>
/// <param name="fileName">源Excel文件</param>
/// <param name="gridview">目标GridView</param>
/// <param name="keyColIndex">导入主键列</param>
/// <param name="rtnMsg">返回消息</param>
/// <returns>返回没有导入的数据DataSet</returns>
public static DataSet ExcelToGridView(string fileName, GridView gridview, int keyColIndex,string rtnMsg)
{
DataSet ds = null ;
string errMsg = string.Empty;
try
{
if ( ! fileName.EndsWith(".xls"))
{
errMsg += "只能导入Excel格式的文件。";
}
ds = ExcelHelper.ExcelToDS(fileName);
DataTable dt = ds.Tables[0];
for (int i = 0; i < gridview.Rows.Count; i++)
{
if (gridview.Rows[i].RowType != DataControlRowType.DataRow)
{
continue;
}
string key = gridview.Rows[i].Cells[keyColIndex].Text;
if (String.IsNullOrEmpty(key))
{
continue;
}
int k = 0;
for (; k < dt.Rows.Count; k++)
{
if (dt.Rows[k][gridview.Columns[keyColIndex].HeaderText].ToString().Replace(" ", "").Replace(" ", "") == key)
{
break;
}
}
if (k == dt.Rows.Count)
{
continue;
}
else
{
for (int j = 0; j < gridview.Columns.Count; j++)
{
if (gridview.Columns[j].Visible == false)
{
continue;
}
if (gridview.Rows[i].Cells[j].Controls.Count == 0)
{
continue;
}
for (int m = 0; m < gridview.Rows[i].Cells[j].Controls.Count; m++)
{
if (gridview.Rows[i].Cells[j].Controls[m].Visible == false)
{
continue;
}
if (gridview.Rows[i].Cells[j].Controls[m] is TextBox)
{
if (!dt.Columns.Contains(gridview.Columns[j].HeaderText))
{
continue;
}
TextBox txt = gridview.Rows[i].Cells[j].Controls[m] as TextBox;
string temp = dt.Rows[k][gridview.Columns[j].HeaderText].ToString();
try
{
// int intTemp = int.Parse(temp);
// txt.Text = intTemp.ToString();
txt.Text = temp; //Translate(temp);
}
catch (Exception)
{
}
break;
}
}
}
dt.Rows[k].Delete();
dt.AcceptChanges();
}
}
}
catch (Exception ex)
{
errMsg +="导入失败." + ex.Message ;
}
rtnMsg = errMsg;
return ds;
}
/// <summary>
/// 导入数据到GridView
/// </summary>
/// <param name="fileUpload">文件上传控件对象</param>
/// <param name="filePath">要存放文件的服务器路径</param>
/// <param name="gridview">目标GridView</param>
/// <param name="keyColIndex">用于导入的主键列</param>
/// <returns>返回没有导入的数据DataSet</returns>
public static DataSet ExcelToGridView(FileUpload fileUpload, string filePath, GridView gridview, int keyColIndex, string strMsg)
{
string errMsg = string.Empty;
DataSet ds = null ;
try
{
if ( ! filePath.EndsWith("\\"))
{
filePath += "\\";
}
string fileName = fileUpload.FileName.Replace(".xls", "") + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string fullFileName = filePath + fileName;
fileUpload.SaveAs(fullFileName);
ds = ExcelToGridView(fullFileName, gridview, keyColIndex,errMsg);
if ( errMsg != string.Empty)
{
errMsg += "将数据导入GridView失败." + errMsg ;
};
}
catch (Exception ex)
{
errMsg += "上传excel文件失败." ;
}
strMsg = errMsg;
return ds ;
}
#endregion
/// <summary>
/// 选定主键列后,用这方法将重复行用黄色背景标志
/// </summary>
/// <param name="gridview">目标GridView</param>
/// <param name="keyColIndex">导入主键列</param>
public static void MarkRepetitionRows(GridView gridView, int keyColIndex)
{
for (int i = 0; i < gridView.Rows.Count; i++)
{
for (int j = i + 1; j < gridView.Rows.Count; j++)
{
if (gridView.Rows[i].Cells[keyColIndex].Text == gridView.Rows[j].Cells[keyColIndex].Text)
{
gridView.Rows[i].BackColor = Color.Yellow;
gridView.Rows[j].BackColor = Color.Yellow;
}
}
}
}
}
ExcelHelper代码
转载请注明出处.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 上周热点回顾(2.17-2.23)
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章