• 发布一个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;
                        }
                    }
                }
            }

        }

    ExcelHelper代码

    转载请注明出处.
  • 相关阅读:
    HTML5 jQuery图片上传前预览
    html5中form表单新增属性以及改良的input标签元素的种类
    PHP邮箱的正则表达式
    PHP手机号码正则表达式
    CSS中设置div垂直居中
    Linux服务器查看内存占用命令
    linux压缩解压文件
    网页是静态还是伪静态?
    帝国cms内容批量替换
    帝国CMS【操作类型】说明详解
  • 原文地址:https://www.cnblogs.com/shiningrise/p/751391.html
Copyright © 2020-2023  润新知