SQL server 导入Excel数据

编辑：洪伟富  2018-06-07

第一步：对表格数据的处理，这一列数据中有数字，又有中文。如果不做处理，导入数据库会默认为float,从而导致“公教楼201”等字符全部为null。解决办法：用筛选功能把它们分开进行导入即可。



第二步：打开导入数据功能















 

点击编辑映射，选择自己需要的字段（建议表的标题和数据库表的字段一致）

 











注意：Excel文件类型为97-2003工作表   

如果有错误欢迎各位指出，联系方式：1798274010@qq.com

【干货】建议收藏！！全网最完整的Python操作Excel数据封装函数
1. 写在前面
2. 前期准备
2.1. 初识Excel
2.2. 格式区别
2.3. 库的使用
2.4. 整体思路
3. 代码展示
3.1. xls格式
3.1.1. 读取xls格式文件
3.1.2. 写入xls格式文件
3.1.3. 追加写入xls格式文件
3.2. xlsx格式
3.2.1. 读取xlsx格式文件
3.2.2. 写入xlsx格式文件
3.2.3. 追加写入xlsx格式文件
4. 结果测试
4.1. 读取测试
4.2. 写入测试
4.3. 追加写入测试
5. 总结


1. 写在前面
对比其它编程语言，我们都知道Python最大的优势是代码简单，有丰富的第三方开源库供开发者使用。伴随着近几年数据分析的热度，Python也成为最受欢迎的编程语言之一。而对于数据的读取和存储，对于普通人来讲，除了数据库之外，最常见的就是微软的Excel。
2. 前期准备
2.1. 初识Excel

Microsoft Excel是Microsoft为使用Windows和Apple Macintosh操作系统的电脑编写的一款电子表格软件。

2.2. 格式区别
Excel中有xls和xlsx两种格式，它们之间的区别是：

文件格式不同。xls是一个特有的二进制格式，其核心结构是复合文档类型的结构，而xlsx的核心结构是XML类型的结构，采用的是基于 XML的压缩方式，使其占用的空间更小。xlsx 中最后一个 x 的意义就在于此。
版本不同。xls是Excel2003及以前版本生成的文件格式，而xlsx是Excel2007及以后版本生成的文件格式。
兼容性不同。xlsx格式是向下兼容的，可兼容xls格式。

2.3. 库的使用
当我们要操作xls格式文件时，需要使用到xlrd和xlwt这两个第三方库；当我们要操作xlsx格式文件时，则需要使用到openpyxl第三方库。




模块
来源
读
写
支持格式




xlrd
第三方库
√
×
xls


xlwt
第三方库
×
√
xls


openpyxl
第三方库
√
√
xlsx


2.4. 整体思路
当使用以上几个模块的时候，从理论上我们就可以完全操作不同格式的Excel的读和写，很多人就疑惑，那这篇文章的作用是什么？我们直接学习对应的这三个模块不就好了吗？答案就是：虽然这几个库已经把Excel的文件、表、行、列的概念完全转换为Python中的对象，但每次操作都需要遍历每一个单元格，甚至很多时候我们要花费大量的时间在思考循环单元格的边界上，这本身就是在重复造轮子，因此我花了半天时间整理了以下六个函数。




方法名
作用
参数
参数注释
返回值
返回值注释




read_xls_excel
读取xls格式Excel数据
url,index
①文件路径，②工作表序号（第几个工作表，传入参数从1开始数）
data
表格中的数据


write_xls_excel
写入xls格式Excel数据
url,sheet_name,two_dimensional_data
①文件路径，②表名，③将要写入表格的数据（二维列表）




write_xls_excel_add
追加写入xls格式Excel数据
url, two_dimensional_data, index
①文件路径，②将要写入表格的数据（二维列表），③指定要追加的表的序号（第几个工作表，传入参数从1开始数）




read_xlsx_excel
读取xlsx格式Excel数据
url,index
①文件路径，②工作表序号（第几个工作表，传入参数从1开始数）
data
表格中的数据


write_xlsx_excel
写入xlsx格式Excel数据
url, sheet_name, two_dimensional_data
①文件路径，②表名，③将要写入表格的数据（二维列表）




write_xlsx_excel_add
追加写入xlsx格式Excel数据
url, sheet_name, two_dimensional_data
①文件路径，②表名，③将要写入表格的数据（二维列表）




3. 代码展示
3.1. xls格式
3.1.1. 读取xls格式文件
'''
读取xls格式文件
参数：
    url:文件路径
    index：工作表序号（第几个工作表，传入参数从1开始数）
返回：
    data:表格中的数据
'''
def read_xls_excel(url,index):
    # 打开指定的工作簿
    workbook = xlrd.open_workbook(url)
    # 获取工作簿中的所有表格
    sheets = workbook.sheet_names()
    # 获取工作簿中所有表格中的的第 index 个表格
    worksheet = workbook.sheet_by_name(sheets[index-1])
    # 定义列表存储表格数据
    data = []
    # 遍历每一行数据
    for i in range(0, worksheet.nrows):
        # 定义表格存储每一行数据
        da = []
        # 遍历每一列数据
        for j in range(0, worksheet.ncols):
            # 将行数据存储到da列表
            da.append(worksheet.cell_value(i, j))
        # 存储每一行数据
        data.append(da)
    # 返回数据
    return data

3.1.2. 写入xls格式文件
'''
写入xls格式文件
参数：
    url:文件路径
    sheet_name:表名
    two_dimensional_data：将要写入表格的数据（二维列表）
'''
def write_xls_excel(url,sheet_name,two_dimensional_data):
    # 创建工作簿对象
    workbook = xlwt.Workbook()
    # 创建工作表对象
    sheet = workbook.add_sheet(sheet_name)
    # 遍历每一行数据
    for i in range(0,len(two_dimensional_data)):
        # 遍历每一列数据
        for j in range(0,len(two_dimensional_data[i])):
            # 写入数据
            sheet.write(i,j,two_dimensional_data[i][j])
    # 保存
    workbook.save(url)
    print("写入成功")

3.1.3. 追加写入xls格式文件
'''
追加写入xls格式文件
参数：
    url:文件路径
    two_dimensional_data：将要写入表格的数据（二维列表）
    index：指定要追加的表的序号（第几个工作表，传入参数从1开始数）
'''
def write_xls_excel_add(url, two_dimensional_data, index):
    # 打开指定的工作簿
    workbook = xlrd.open_workbook(url)
    # 获取工作簿中的所有表格
    sheets = workbook.sheet_names()
    # 获取指定的表
    worksheet = workbook.sheet_by_name(sheets[index-1])
    # 获取表格中已存在的数据的行数
    rows_old = worksheet.nrows
    # 将xlrd对象拷贝转化为xlwt对象
    new_workbook = copy(workbook)
    # 获取转化后工作簿中的第index个表格
    new_worksheet = new_workbook.get_sheet(index-1)
    # 遍历每一行数据
    for i in range(0, len(two_dimensional_data)):
        # 遍历每一列数据
        for j in range(0, len(two_dimensional_data[i])):
            # 追加写入数据，注意是从i+rows_old行开始写入
            new_worksheet.write(i+rows_old, j, two_dimensional_data[i][j])
    # 保存工作簿
    new_workbook.save(url)
    print("追加写入成功")

3.2. xlsx格式
3.2.1. 读取xlsx格式文件
'''
读取xlsx格式文件
参数：
    url:文件路径
    sheet_name:表名
返回：
    data:表格中的数据
'''
def read_xlsx_excel(url, sheet_name):
    # 使用openpyxl加载指定路径的Excel文件并得到对应的workbook对象
    workbook = openpyxl.load_workbook(url)
    # 根据指定表名获取表格并得到对应的sheet对象
    sheet = workbook[sheet_name]
    # 定义列表存储表格数据
    data = []
    # 遍历表格的每一行
    for row in sheet.rows:
        # 定义表格存储每一行数据
        da = []
        # 从每一行中遍历每一个单元格
        for cell in row:
            # 将行数据存储到da列表
            da.append(cell.value)
        # 存储每一行数据
        data.append(da)
    # 返回数据
    return data

3.2.2. 写入xlsx格式文件
'''
写入xlsx格式文件
参数：
    url:文件路径
    sheet_name:表名
    two_dimensional_data：将要写入表格的数据（二维列表）
'''
def write_xlsx_excel(url, sheet_name, two_dimensional_data):
    # 创建工作簿对象
    workbook = openpyxl.Workbook()
    # 创建工作表对象
    sheet = workbook.active
    # 设置该工作表的名字
    sheet.title = sheet_name
    # 遍历表格的每一行
    for i in range(0, len(two_dimensional_data)):
        # 遍历表格的每一列
        for j in range(0, len(two_dimensional_data[i])):
            # 写入数据（注意openpyxl的行和列是从1开始的，和我们平时的认知是一样的）
            sheet.cell(row=i + 1, column=j + 1, value=str(two_dimensional_data[i][j]))
    # 保存到指定位置
    workbook.save(url)
    print("写入成功")
    

3.2.3. 追加写入xlsx格式文件
'''
追加写入xlsx格式文件
参数：
    url:文件路径
    sheet_name:表名
    two_dimensional_data：将要写入表格的数据（二维列表）
'''
def write_xlsx_excel_add(url, sheet_name, two_dimensional_data):
    # 使用openpyxl加载指定路径的Excel文件并得到对应的workbook对象
    workbook = openpyxl.load_workbook(url)
    # 根据指定表名获取表格并得到对应的sheet对象
    sheet = workbook[sheet_name]
    for tdd in two_dimensional_data:
        sheet.append(tdd)
    # 保存到指定位置
    workbook.save(url)
    print("追加写入成功")

4. 结果测试
4.1. 读取测试
先准备两个Excel文件，如图所示



其内容如下：





测试代码：



输出结果：



结论：表明读取并没有问题！！！接下来测试写入
4.2. 写入测试
测试代码：



结果输出：



看到数据被覆盖了！！！接下来在上面修改后的数据的基础上测试追加写入
4.3. 追加写入测试
测试代码：



结果输出：



追加也没有问题！！
5. 总结
我一直认为数据分析就应该以数据为主，而不应该把大量的代码花费在设计Excel表格的样式上，这样多少就有点主客颠倒的意思了。总之，希望这篇文章对正在学习Python的你有一定的帮助，如果对你有帮助的话，不妨点个赞和收藏吧！如果有什么意见和建议也欢迎在评论区留言！

  今天在做项目时，需要将excel的数据导入的mysql表中。本来想自己写一个将读取Excel数据并输出成sql语句的工具类，在查阅资料时发现Navicat将然就可以实现，所以就偷个懒使用这个便捷的工具。当然在导入过程中遇到了一些问题，特此在这做个记录。

 本次实例为民族代码数据。详细步骤如下：

  （1）首先在数据库中建立相应的表。注意：我设置id是自增的。



（2）在excel文件的第一行增加和表字段一样的字段



（3）将excel文件转成 .csv文件

 为什么不直接用.xlsx后缀呢？ Navicat明明可以导入.xlsx  但在我是导入.xlsx文件时，出现如下问题。具体原因还没有去查，也有可能是软件问题，所以可以亲自试试。



 excel转.csv文件步骤(软件office)

a. 打开excel文件--->另存为--->选择CSV(逗号隔开)(*.csv)格式--->工具--->web选项---->编码->选择utf-8格式





 注意导入excel编码格式必须和数据库格式表格式一致，我数据库使用格式为utf-8。否则导入的数据会乱码。

但是用office设置的编码格式后，导入数据还是会乱码，可能是这边设置不起作用吧（如果你知道原因，请在评论中指导一下吧）。 所以就得用其他工具来改变编码格式了。

我用的是notepad++。具体操作如下：

导航栏中选择编码--->选择转为utf-8编码-->保存。

(4)msyql导入数据

a、



b、



c、直接下一步下一步到此。.csv第一行的字段会自动填入到源码栏中



d、直接下一步到完，即完成导入

C#读取Excel数据的几种方式（包含大量数据读取）


C#读取Excel数据的几种方式（包含大量数据读取）

OleDB方式
COM组件的方式
NPOI方式读取（此处未测试，参考其他博文）




常用的Excel数据读取方式有三种：
OleDB方式

这种方式需要安装AccessDatabaseEngine，实际上就是一种利用ACCESS数据库的方式读取数据至DataTable。下载链接
这种方法使用简单，读取速度快，尤其适合小批量数据读取。WHC.OrderWater.Commons中有完成的操作封装，日常使用足矣。下载链接
缺点是当数据量非常大时（比如超过2w行），会非常占用内存，甚至可能存在内存溢出的异常，需要使用其他方式。

public static DataSet ExcelToDataSet(string excelPath, bool header, ExcelType eType)
{
    string connectstring = GetExcelConnectstring(excelPath, header, eType);
    return ExcelToDataSet(connectstring);
}

public static DataSet ExcelToDataSet(string connectstring)
{
    using (OleDbConnection conn = new OleDbConnection(connectstring))
    {
        DataSet ds = new DataSet();
        List<string> tableNames = GetExcelTablesName(conn);

        foreach (string tableName in tableNames)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + tableName + "]", conn);
            adapter.Fill(ds, tableName);
        }
        return ds;
    }
}

COM组件的方式

这种方式需要添加Microsoft.Office.Interp.Excel引用，可用NuGet安装引用。
优点是能够非常灵活的读取Excel中的数据，还可以灵活调用各种函数。如果通过WorkSheet.Range()方式读取可以极大地提升读取速度，适用于批量数据读取。
缺点是需要后台打开Excel，数据量较小时读取速度优势不明显。

/// <summary>
/// 读取Excel中某一范围的数据
/// </summary>
/// <param name="excelPath">待读取的Excel文件路径</param>
/// <param name="stCell">起始单元格编号</param>
/// <param name="edCell">终止单元格编号</param>
/// <returns>存放连续读取的数据的二维数组</returns>
public static object[,] GetExcelRangeData(string excelPath, string stCell, string edCell)
{
    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
    Workbook workBook = null;
    object oMissiong = Missing.Value;
    try
    {
        workBook = app.Workbooks.Open(excelPath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
            oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
        if (workBook == null)
            return null;

        Worksheet workSheet = (Worksheet) workBook.Worksheets.Item[1];
        //使用下述语句可以从头读取到最后，按需使用
        //var maxN = workSheet.Range[startCell].End[XlDirection.xlDown].Row;
        return workSheet.Range[stCell + ":" + edCell].Value2;
    }
    catch (Exception e)
    {
        return null;
    }
    finally
    {
        //COM组件方式调用完记得释放资源
        if (workBook != null)
        {
            workBook.Close(false, oMissiong, oMissiong);
            Marshal.ReleaseComObject(workBook);
            app.Workbooks.Close();
            app.Quit();
            Marshal.ReleaseComObject(app);
        }
    }
}

//随便写的一个调用案例
public static List<Edge> ReadAllEdgesFromFile()
{
    List<Edge> lstEdges = new List<Edge>();
    object[,] data = ExcelHelper.GetExcelRangeData(FilePath, "A1", "C82412");
    int length = data.GetLength(0);
    //注意这里是从1开始的，调试的时候才发现
    for (int i = 1; i <= length; i++)
    {
        Edge edge = new Edge();
        //注意这里是从1开始的，调试的时候才发现
        edge.EdgeID = Convert.ToInt32(data[i, 1]);
        edge.EdgeStartIndex = Convert.ToInt32(data[i, 2]);
        edge.EdgeEndIndex = Convert.ToInt32(data[i, 3]);
        edge.StSelected = false;
        edge.EdSelected = false;

        lstEdges.Add(edge);
    }

    return lstEdges;
}

NPOI方式读取（此处未测试，参考其他博文）

NPOI是POI项目的.NET版本，用于读写Excel/Word等微软OLE2组件文档。
优点是读取Excel速度较快，读取方式灵活。
缺点是需要下载相关插件。
此处代码请参考以下博文链接：C#读取Excel的三种方式以及比较