C# 导入Excel读取图片上传

前言：

　　相信很多小伙伴都对导入Excel很熟悉，但是怎么把Excel里某一列的图片上传到服务器或OSS上，在本章节重点讲解如何读取Excel图片上传。

一、Excel导入数据到数据库

1.准备好Excel读取的帮助类

在 NuGet 安装 NPOI.dll

100

101

102

103

104

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.IO;
 
namespace ImportExcel.Api.Helper
{
    public class ExcelHelper
    {
        private static IWorkbook workbook = null;
        private static FileStream fs = null;
        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="fileName">excel文件路径</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);
 
                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
 
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; i++)
                        {
                            DataColumn column = new DataColumn(i.ToString());
                            data.Columns.Add(column);
                        }
                        startRow = sheet.FirstRowNum;
                    }
 
                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　
 
                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }
 
                return data;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }
    }
}

2.读取Excel数据导入数据库（回顾下我们常用的做法，读取了Excel就存到数据库）

using ImportExcel.Api.Helper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web;
using System.Web.Http;
 
namespace ImportExcel.Api.Controllers
{
    public class ImportExcelController : ApiController
    {
 
        public bool ImportExcel_Img()
        {
 
            HttpFileCollection files = HttpContext.Current.Request.Files;
            if (files.Count > 0)
            {
                HttpPostedFile file = files[0];
                string saveTempPath = "~/Temp/Order";
                //读取导入的文件类型
                var fileExt = file.FileName.Substring(file.FileName.LastIndexOf('.')).ToLower();
                if (!fileExt.Equals(".xlsx"))
                {
                    //提示文件类型不正确
                    return false;
                }
 
                var fileName = Guid.NewGuid();
                string xlsxFileName = fileName + ".xlsx";
                var mapPath = HttpContext.Current.Server.MapPath(saveTempPath);
                file.SaveAs(mapPath + @"\" + xlsxFileName);
                var dt = ExcelHelper.ExcelToDataTable(mapPath + @"\" + xlsxFileName);
                if (dt != null)
                {
                    //写入数据库逻辑和判断
                }
            }
            return true;
        }
    }
}

二、Excel导入数据到数据库并读取图片图文讲解（重点来了）
1.把xlsx文件改为zip文件解压到当前目录

2.进入解压后的目录找到xml文件

图片存放路径XML： \test\xl\drawings\_rels\drawing1.xml.rels

前面我们Excel有四张图片这里也会显示4张图片的路径，通过这个Xml可以知道我们图片存放在哪（注意Id="" 这个地方下面会用到）

访问图片文件夹：\test\xl\media

图片拿到了是不是想问怎么知道这个图片在哪一列上面呢，别急还有一个Xml文件可以获取当前图片所在单元格与行

获取当前图片所在单元格与行Xml：\test\\xl\drawings\drawing1.xml

讲解到现在相信大家有个底了，怎么去获取图片，图片对应哪一行哪一列！

三、Excel导入数据到数据库并且获取图片代码实现与讲解

在做之前我们先准备以下东西
1.读取Excel数据的方法（已经有了）
2.解压zip方法

需要用到的ICSharpCode.SharpZipLib.Zip.dll

/// <summary>
        /// 解压文件
        /// </summary>
        /// <param name="zipFilePath">压缩文件路径</param>
        /// <param name="path">返回压缩文件夹路径</param>
        /// <param name="unZipDir">解压文件存放路径,为空时默认与压缩文件同一级目录下，跟压缩文件同名的文件夹</param>
        /// <returns></returns>
        private bool UnZipFile(string zipFilePath, out string path, string unZipDir = null)
        {
            if (zipFilePath == string.Empty)
            {
                path = null;
                return false;
            }
 
            if (!System.IO.File.Exists(zipFilePath))
            {
                path = null;
                return false;
            }
            //解压文件夹为空时默认与压缩文件同一级目录下，跟压缩文件同名的文件夹  
            if (string.IsNullOrWhiteSpace(unZipDir))
                unZipDir = zipFilePath.Replace(Path.GetFileName(zipFilePath), Path.GetFileNameWithoutExtension(zipFilePath));
 
            if (!unZipDir.EndsWith("\\"))
                unZipDir += "\\";
 
            if (!Directory.Exists(unZipDir))
                Directory.CreateDirectory(unZipDir);
            try
            {
                using (ZipInputStream s = new ZipInputStream(System.IO.File.OpenRead(zipFilePath)))
                {
 
                    ZipEntry theEntry;
                    while ((theEntry = s.GetNextEntry()) != null)
                    {
                        string directoryName = Path.GetDirectoryName(theEntry.Name);
                        string fileName = Path.GetFileName(theEntry.Name);
                        if (directoryName.Length > 0)
                        {
                            Directory.CreateDirectory(unZipDir + directoryName);
                        }
                        if (!directoryName.EndsWith("\\"))
                            directoryName += "\\";
                        if (fileName != String.Empty)
                        {
                            using (FileStream streamWriter = System.IO.File.Create(unZipDir + theEntry.Name))
                            {
 
                                int size = 2048;
                                byte[] data = new byte[2048];
                                while (true)
                                {
                                    size = s.Read(data, 0, data.Length);
                                    if (size > 0)
                                    {
                                        streamWriter.Write(data, 0, size);
                                    }
                                    else
                                    {
                                        break;
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch
            {
                path = null;
                return false;
            }
            path = unZipDir;
            return true;
        }

在实现Excel导入数据到数据库的方法做修改

1.准备读取xml的图片信息类

namespace ImportExcel.Api.Model
{
    /// <summary>
    /// excel 图片信息
    /// </summary>
    public class o_ExcelImgModel
    {
        /// <summary>
        /// ID
        /// </summary>
        public string ID { get; set; }
        /// <summary>
        /// 行
        /// </summary>
        public int Row { get; set; }
        /// <summary>
        /// 单元格
        /// </summary>
        public int Col { get; set; }
        /// <summary>
        /// 图片文件绝对路径
        /// </summary>
        public string PathOfPicture { get; set; }
    }
}

2.重点代码演示

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

using ImportExcel.Api.Helper;
using ImportExcel.Api.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web;
using System.Web.Http;
using System.Xml;
 
namespace ImportExcel.Api.Controllers
{
    public class ImportExcelController : ApiController
    {
 
        public bool ImportExcel_Img()
        {
 
            HttpFileCollection files = HttpContext.Current.Request.Files;
            if (files.Count > 0)
            {
                HttpPostedFile file = files[0];
                string saveTempPath = "~/Temp/Order";
                //读取导入的文件类型
                var fileExt = file.FileName.Substring(file.FileName.LastIndexOf('.')).ToLower();
                if (!fileExt.Equals(".xlsx"))
                {
                    //提示文件类型不正确
                    return false;
                }
 
                //转换保存zip
                var fileName = Guid.NewGuid();
                string zipFileName = fileName + ".zip";
                string xlsxFileName = fileName + ".xlsx";
                var mapPath = HttpContext.Current.Server.MapPath(saveTempPath);
                //保存xlsx到服务器
                file.SaveAs(mapPath + "\\" + xlsxFileName);
                //保存zip到服务器
                file.SaveAs(mapPath + "\\" + zipFileName);
                var dt = ExcelHelper.ExcelToDataTable(mapPath + @"\" + xlsxFileName);
                //解压，如果解压成功则根据xml处理 (应为方便我就放在ExcelHelper里面了)
                if (ExcelHelper.UnZipFile(HttpContext.Current.Server.MapPath(saveTempPath) + "\\" + zipFileName, out string path))
                {
                    //图片路径文件夹
                    var mediaFolderPath = path + "\\xl\\media";
                    //判断是否存在此文件夹如果有则处理（如果没有图片他是不会有这个文件夹的）
                    if (System.IO.Directory.Exists(mediaFolderPath))
                    {
                        //解压成功获取xml 节点做处理
                        var exclNode = GetXmlExclNodeList(path);
                        var pictNode = GetXmlPictNodeList(path);
                        //excel 图片信息
                        List<o_ExcelImgModel> o_ExcelImgModelList = new List<o_ExcelImgModel>();
                        //获取图片信息与地址
                        foreach (var nl in exclNode)
                        {
                            XmlElement sondNode = (XmlElement)nl;
 
                            XmlNodeList descendDodeList = sondNode.ChildNodes;
 
                            XmlNodeList fromNodeList = descendDodeList[0].ChildNodes;
 
                            //取得行号
 
                            var row = Convert.ToInt32(fromNodeList.Item(2).InnerText.Trim());
 
                            var col = Convert.ToInt32(fromNodeList.Item(0).InnerText.Trim());
 
                            XmlNodeList picNodeList = descendDodeList[2].ChildNodes;
 
                            XmlNodeList blipFillNodeList = picNodeList[1].ChildNodes;
 
                            XmlElement picElement = (XmlElement)blipFillNodeList.Item(0);
 
                            string id = picElement.GetAttribute("r:embed").ToString();
 
                            foreach (XmlNode xn in pictNode)
                            {
 
                                XmlElement xe = (XmlElement)xn;
                                if (xe.GetAttribute("Id").ToString() == id)
                                {
                                    var pathOfPicture = xe.GetAttribute("Target").ToString().Replace("..", "").Replace("/", @"\");
 
                                    pathOfPicture = path + "\\xl" + pathOfPicture;
                                    o_ExcelImgModelList.Add(new o_ExcelImgModel()
                                    {
                                        ID = id,
                                        Col = col,
                                        Row = row,
                                        PathOfPicture = pathOfPicture
                                    });
 
                                    break;
                                }
                            }
                        }
                        //图片对应dt的哪一列，存到dt然后再循环dt去处理（这个是小编的思维，如果有更好的做法可以随缘发挥）
                        foreach (var item in o_ExcelImgModelList)
                        {
                            dt.Rows[item.Row - 1][item.Col] += string.IsNullOrWhiteSpace(dt.Rows[item.Row - 1][item.Col].ToString()) ? item.PathOfPicture : "," + item.PathOfPicture;
                        }
                    }
                    //现在dt某一列存放了图片的绝对路径就可以通过table去处理了
                    //循环表插入数据及上传
                    foreach (DataRow item in dt.Rows)
                    {
                        //获取图片然后做上传逻辑，这个自己实现我就不多讲了
                    }
                }
                else {
                    //解压时报直接返回，这个返回啥类型或者啥数据自己定义就好我这边demo 随缘来个bool意思下
                    return false;
                }
                //业务逻辑处理完了就把原来的文件和解压的文件夹删除
                Directory.Delete(mapPath + "\\" + fileName, true);
                File.Delete(mapPath + "\\" + xlsxFileName);
                File.Delete(mapPath + "\\" + zipFileName);
            }
            return true;
        }
        /// <summary>
        /// Xml图片表格位置及路径ID
        /// </summary>
        private const string _XmlExcel = @"\xl\drawings\drawing1.xml";
        /// <summary>
        /// Xml图片路径
        /// </summary>
        private const string _XmlPict = @"\xl\drawings\_rels\drawing1.xml.rels";
 
        /// <summary>
        /// 获取图片路径 Xml节点
        /// </summary>
        /// <param name="path">解压后的文件夹路径</param>
        /// <returns></returns>
        private XmlNodeList GetXmlPictNodeList(string path)
        {
            XmlDocument doc = new XmlDocument();
            doc.Load(path + _XmlPict);
            XmlNode root = doc.DocumentElement;
            return root.ChildNodes;
        }
 
        /// <summary>
        /// 获取图片表格位置及路径ID Xml节点
        /// </summary>
        /// <param name="path">解压后的文件夹路径</param>
        /// <returns></returns>
        private XmlNodeList GetXmlExclNodeList(string path)
        {
            XmlDocument doc = new XmlDocument();
            doc.Load(path + _XmlExcel);
            XmlNode root = doc.DocumentElement;
            return root.ChildNodes;
        }
 
    }
}