将Excel文档中的数据转为Datatable并上传到oracle数据库

将Excel文档中的数据转为Datatable并上传到oracle数据库

将Excel文档中的数据转为Datatable

///
///
///
/// 路径
/// 是否有表头名
///
public DataTable ExcelToDataTable(string filePath, bool isColumnName)
{
DataTable dataTable = new DataTable();
FileStream fs = null;
DataColumn column = null;
DataRow dataRow = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
ICell firstCell = null;
int startRow = 0;
try
{
using (fs = File.OpenRead(filePath))
{
// 2007版本
if (filePath.IndexOf(".xlsx") > 0)
workbook = new XSSFWorkbook(fs);
// 2003版本
else if (filePath.IndexOf(".xls") > 0)
workbook = new HSSFWorkbook(fs);

                if (workbook != null)
                {
                    int SheetCount = workbook.NumberOfSheets;//获取表的数量
                    for (int k = 0; k < SheetCount; k++)
                    {
                        sheet = workbook.GetSheetAt(k);//读取第一个sheet，当然也可以循环读取每个sheet  

                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数  
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行  
                                int cellCount = firstRow.LastCellNum;//列数  

                                //构建datatable的列  
                                if (k == 0)//第一张表建立表头，从第二表开始直接填充行
                                {
                                    if (isColumnName)
                                    {
                                        startRow = 1;//如果第一行是列名，则从第二行开始读取  
                                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                        {
                                            cell = firstRow.GetCell(i);
                                            if (cell != null)
                                            {
                                                if (cell.StringCellValue != null)
                                                {
                                                    column = new DataColumn(cell.StringCellValue);
                                                    dataTable.Columns.Add(column);
                                                }
                                            }
                                        }
                                    }
                                    else
                                    {
                                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                        {
                                            column = new DataColumn("column" + (i + 1));
                                            dataTable.Columns.Add(column);
                                        }
                                    }
                                }

                                //填充行  
                                for (int i = startRow; i < rowCount + 1; i++)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;

                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; j++)
                                    {
                                        cell = row.GetCell(j);
                                        firstCell = row.GetCell(row.FirstCellNum);
                                        if (cell == null)
                                        {
                                            dataRow[j] = "";
                                        }
                                        else if (firstCell.ToString() == "")
                                        {
                                            return dataTable;
                                        }
                                        else
                                        {
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    dataRow[j] = "";
                                                    break;
                                                case CellType.Numeric:
                                                    short format = cell.CellStyle.DataFormat;
                                                    //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理  
                                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                                        dataRow[j] = cell.DateCellValue;
                                                    else
                                                        dataRow[j] = cell.NumericCellValue;
                                                    break;
                                                case CellType.String:
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                            }
                                        }
                                    }
                                    dataTable.Rows.Add(dataRow);
                                }
                            }

                        }//if (sheet != null)结束
                    }//for (int k = 0; k < SheetCount; k++)结束
                }
            }
            return dataTable;
        }
        catch (Exception)
        {
            if (fs != null)
            {
                fs.Close();
            }
            return null;
        }
    }

将dataTable批量上传到数据库

public static String GetOracleConnection()
{

        String ConnStr = "Password=fy2;User ID=fy2;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=171.188.42.57)(PORT=1521))(CONNECT_DATA=(SID=tops)));";
        return ConnStr;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="dataTable">表数据</param>
    /// <param name="sql">默认</param>
    /// <returns></returns>
    public static bool insertValueWithDt(DataTable dataTable, string sql)
    {
        String ConnStr = GetOracleConnection();
        using (OracleConnection conn = new OracleConnection(ConnStr))
        {
            try
            {
                OracleCommand cmd = new OracleCommand(sql, conn);
                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                OracleCommandBuilder cb = new OracleCommandBuilder(adapter);
                DataTable dsNew = new DataTable();
                int count = adapter.Fill(dsNew);
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    DataRow dr = dsNew.NewRow();
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        dr[dsNew.Columns[j].ColumnName] = dataTable.Rows[i][j];
                    }
                    dsNew.Rows.Add(dr);
                }
                count = adapter.Update(dsNew);

                return true;
            }
            catch (Exception e)
            {

                return false;
            }
        }
    }

使用方式

protected void UploadButton_Click(object sender, EventArgs e)
{
try
{
if (FileUpload1.HasFile)
{
string fileExrensio = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();//ToLower转化为小写
string FileType = FileUpload1.PostedFile.ContentType;
string UploadURL = Server.MapPath("~/upload/");//上传的目录

                try
                {
                    if (!System.IO.Directory.Exists(UploadURL))//判断文件夹是否已经存在
                    {
                        System.IO.Directory.CreateDirectory(UploadURL);//创建文件夹
                    }
                    path = UploadURL + DateTime.Now.ToString("yyyyMMddhhmmss") + "_" + FileUpload1.FileName;

                    FileUpload1.PostedFile.SaveAs(path);//上传文件   

                    DataTable dt = ExcelToDataTable(path, false);
                    if(dt.Rows.Count>2)
                    {
                        for (int i = 0; i < 1; i++)
                        {
                            dt.Rows.Remove(dt.Rows[0]);//删除行
                        }

                        dt.Columns.Add("NUM");//新增列
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            dt.Rows[i]["NUM"] = i + 1;
                        }
                        dt.Columns["NUM"].SetOrdinal(1);

                        try
                        {
                            insertValueWithDt(dt, "select * from SYS_YJH_DATA_TMP WHERE ROWNUM=0 ");
                            Response.Write("<script>alert('导入成功');location='javascript:history.go(-1);'</script>");
                        }
                        catch (Exception ex)
                        {
                            Response.Write(ex.Message);
                        }
                    }
                    

                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                }
            }
            else
            {
                Response.Write("<script>alert('请选择文件！')</script>");
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }