将EXCEL表格文字图片信息转成RTF格式写入数据库BLOB字段

最近在项目开发过程中，有一个需求是将Excel的文字图片信息导入数据库。

数据库中已存在的数据格式是RTF富文本的内容转成byte[]写入Oracle数据库的Blob字段的，

因此在不影响软件和保证已存在数据格式的前提下，需要将Excel的文字图片信息做转换处理。

流程如下：

需要导入的Excel文件内容

软件界面显示效果

读取EXCEL文件文字、图片信息，并将EXCEL文字、图片转换成RTF格式

这里我用的NPOI 2.3.0.0

//RTF格式开始的固定字符串
private const string RtfStartTxt = @"{
tf1deff0{fonttbl{f0 Calibri;}{f1fcharset134 Tahoma;}}{colortbl
ed0green0lue0 ;
ed0green0lue255 ;}{*defchp fs22}{*listoverridetable}{stylesheet {qlfs22 Normal;}{*cs1f1fs18 Default Paragraph Font;}{*cs2sbasedon1f1fs18 Line Number;}{*cs3ulfs22cf1 Hyperlink;}{*	s4	srowdfs22ql	rautofit1	scellpaddfl3	scellpaddl108	scellpaddfr3	scellpaddr108	svertaltcltxlrtb Normal Table;}{*	s5	srowdsbasedon4fs22ql	rbrdrtrdrsrdrw10	rbrdrlrdrsrdrw10	rbrdrbrdrsrdrw10	rbrdrrrdrsrdrw10	rbrdrhrdrsrdrw10	rbrdrvrdrsrdrw10	rautofit1	scellpaddfl3	scellpaddl108	scellpaddfr3	scellpaddr108	svertaltcltxlrtb Table Simple 1;}}
ouicompatsplytwninehtmautspsectdpardplainql";
//RTF代表文本的首字符串
private const string RtfTxtStartTxt = @"{f1fs18cf0 ";
//RTF格式中代表一个回车
private const string RtfEnter = @"f2fs18cf0parpardplainql";
//RTF格式结束的固定字符串
private const string RtfEndTxt = @"f1fs18cf0par}";
//将字符串转换成RTF格式的内容
private static string GetRichText(string str)
{
    return str.Select(b => ((int) b).ToString(CultureInfo.InvariantCulture)).Select(tmp1 => @"u" + tmp1 + "?").Aggregate("", (current, tmp2) => current + tmp2);
}

//将转化成字符串的图片信息按每200个字符加一个回车形成RTF图片信息
private static string GetSplitString(string str)
{
    string temp = string.Empty;
    for (int i = 0; i < str.Length; i += 200)
        temp += (i + 200 < str.Length) ? (str.Substring(i, 200) + "
") : str.Substring(i);
    return temp;
}
/// <summary>
/// 根据Excel列类型获取列的值
/// </summary>
/// <param name="cell">Excel列</param>
/// <returns></returns>
private static string GetCellValue(ICell cell)
{
    if (cell == null)
        return string.Empty;
    switch (cell.CellType)
    {
        case CellType.Blank:
            return string.Empty;
        case CellType.Boolean:
            return cell.BooleanCellValue.ToString();
        case CellType.Error:
            return cell.ErrorCellValue.ToString(CultureInfo.InvariantCulture);
        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)
                return cell.DateCellValue.ToString(CultureInfo.InvariantCulture);
            return cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
        default:
            return cell.ToString();
        case CellType.String:
            return cell.StringCellValue;
        case CellType.Formula:
            try
            {
                var e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                e.EvaluateInCell(cell);
                return cell.ToString();
            }
            catch
            {
                return cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
            }
    }
}
//替换字符串前面内容
public static string TrimStarString(string s, string searchStr, string replaceStr)
{
    var result = s;
    try
    {
        if (string.IsNullOrEmpty(result))
            return result;
        if (s.Length < searchStr.Length)
            return result;
        if (s.IndexOf(searchStr, 0, searchStr.Length, StringComparison.Ordinal) > -1)
            result = s.Substring(searchStr.Length);
        return result;
    }
    catch (Exception)
    {
        return result;
    }
}
//替换字符串后面内容
public static string TrimEndString(string s, string searchStr, string replaceStr)
{
    var result = s;
    try
    {
        if (string.IsNullOrEmpty(result))
            return result;
        if (s.Length < searchStr.Length)
            return result;
        if (s.IndexOf(searchStr, s.Length - searchStr.Length, searchStr.Length, StringComparison.Ordinal) > -1)
            result = s.Substring(0, s.Length - searchStr.Length);
        return result;
    }
    catch (Exception)
    {
        return result;
    }
}
/// <summary>
/// 将excel导入到datatable
/// </summary>
/// <param name="filePath">excel路径</param>
/// <param name="sheetName"></param>
/// <param name="isColumnName">第一行是否是列名</param>
/// <param name="dataTable"></param>
/// <returns>返回datatable</returns>
public static bool ExcelToDataTable(string filePath, string sheetName, bool isColumnName, out DataTable dataTable)
{
    dataTable = null;
    FileStream fs = null;
    IWorkbook workbook = null;
    int startRow = 0;
    var pictures = new List<PicturesInfo>();
    try
    {
        using (fs = File.OpenRead(filePath))
        {
            // 2007版本
            if (filePath.ToUpper().EndsWith(".XLSX"))
                workbook = new XSSFWorkbook(fs);
            // 2003版本
            else if (filePath.ToUpper().EndsWith(".XLS"))
                workbook = new HSSFWorkbook(fs);
            if (workbook != null)
            {
                ISheet sheet = workbook.GetSheet(sheetName);
                dataTable = new DataTable();
                if (sheet != null)
                {
                    pictures = NpoiExtend.GetAllPictureInfos(sheet);//获取图片信息
                    int rowCount = sheet.LastRowNum; //总行数
                    if (rowCount > 0)
                    {
                        IRow firstRow = sheet.GetRow(0); //第一行
                        int cellCount = firstRow.LastCellNum; //列数
                        //构建datatable的列
                        ICell cell;
                        DataColumn column;
                        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; ++i)
                        {
                            IRow row = sheet.GetRow(i);
                            if (row == null) continue;
                            DataRow dataRow = dataTable.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                cell = row.GetCell(j);
                                if (cell == null)
                                    dataRow[j] = "";
                                else
                                    dataRow[j] = GetCellValue(cell);
                            }
                            dataTable.Rows.Add(dataRow);
                        }
                    }
                }
            }
        }
        if (dataTable != null && dataTable.Columns.Count >= 0 && pictures.Count > 0)
        {
            var columnIndex = 0;
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                if (dataTable.Columns[i].ColumnName == "说明")//找到说明列
                {
                    columnIndex = i;
                    break;
                }
            }
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                DataRow row = dataTable.Rows[i];
                var str = row[columnIndex].ToString();
                var pics = pictures.Where(x => x.MinCol == columnIndex && x.MinRow == i + 1).ToList();
                if (pics.Count > 0)
                {
                    var allPicStr = "";
                    foreach (PicturesInfo pic in pics)
                    {
                        byte[] picdata = pic.PictureData;//Excel 中的图片信息，byte[]格式
                        var picstr = "";
                        foreach (byte b in picdata)
                        {
                            string tempStr = Convert.ToString(b, 16);
                            if (tempStr.Length == 1)
                                tempStr = "0" + tempStr;//不足一位需要前面补0
                            picstr += tempStr;
                        }//将byte[]格式图片=>转换成16进制
                        picstr = GetSplitString(picstr);//转成RTF格式需要的内容
                        //RTF图片格式{*shppict{pictpngblippicw8494pich3175picwgoal4815pichgoal1800picscalex100picscaley100 [图片16进制信息]}}
                        picstr = @"{*shppict{pictpngblip" +
                                    @"picw" + pic.SizeWidth * 26 +
                                    @"pich" + pic.SizeHeight * 26 +
                                    @"picwgoal" + pic.SizeWidth * 26 * 72 / 127 +
                                    @"pichgoal" + pic.SizeHeight * 26 * 72 / 127 +
                                    @"picscalex100picscaley100 " +
                                    picstr + @"}}";
                        allPicStr += picstr + RtfEnter;
                    }
                    var allTextStr = RtfTxtStartTxt + GetRichText(str) + "}" + RtfEnter;//Excel单元格文字内容转RTF格式
                    var textPic = RtfStartTxt + allTextStr + allPicStr + RtfEndTxt;//将Excel文字和图片拼接到一起
                    row[columnIndex] = textPic;
                }
            }
            var k = dataTable.Rows.Count - 1;
            for (int i = k; i > 0; i--)//将多个单元格合并到一起，处理如图Excel格式内容
            {
                DataRow row = dataTable.Rows[i];
                if (string.IsNullOrEmpty(row[0].ToString()) && !string.IsNullOrEmpty(row[columnIndex].ToString()))
                {
                    var str1 = dataTable.Rows[i - 1][columnIndex].ToString();
                    str1 = TrimEndString(str1, RtfEndTxt, "");
                    var str2 = dataTable.Rows[i][columnIndex].ToString();
                    str2 = TrimEndString(str2, RtfEndTxt, "");
                    str2 = TrimStarString(str2, RtfStartTxt, "");
                    dataTable.Rows[i - 1][columnIndex] = str1 + RtfEnter + str2 + RtfEndTxt;
                    dataTable.Rows.RemoveAt(i);
                }
            }
        }
        return true;
    }
    catch (Exception ex)
    {
        if (fs != null)
            fs.Close();
        return false;
    }
}

读取Excel图片我参考的是这篇博文：https://www.cnblogs.com/hanzhaoxin/p/4442369.html

PicturesInfo.cs

在原博文的基础上，我加了SizeWidth 和 SizeHeight两个属性，用于后续生成RTF格式图片Size内容。

    public class PicturesInfo
    {
        public int MinRow { get; set; }
        public int MaxRow { get; set; }
        public int MinCol { get; set; }
        public int MaxCol { get; set; }
        public Byte[] PictureData { get; private set; }
        public int SizeWidth { get; set; }
        public int SizeHeight { get; set; }

        public PicturesInfo(int minRow, int maxRow, int minCol, int maxCol, Byte[] pictureData)
        {
            this.MinRow = minRow;
            this.MaxRow = maxRow;
            this.MinCol = minCol;
            this.MaxCol = maxCol;
            this.PictureData = pictureData;
            using (var ms = new MemoryStream(pictureData))
            {
                var image = Image.FromStream(ms);
                this.SizeWidth = image.Size.Width;
                this.SizeHeight = image.Size.Height;
            }
        }
    }

NpoiExtend.cs

public static class NpoiExtend
{
    public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet)
    {
        return sheet.GetAllPictureInfos(null,null,null,null);
    }
 
    public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet,int? minRow,int? maxRow,int? minCol,int? maxCol,bool onlyInternal = true)
    {
        if (sheet is HSSFSheet)
        {
            return GetAllPictureInfos((HSSFSheet)sheet,minRow,maxRow,minCol,maxCol,onlyInternal);
        }
        else if (sheet is XSSFSheet)
        {
            return GetAllPictureInfos((XSSFSheet)sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
        }
        else
        {
            throw new Exception("未处理类型，没有为该类型添加：GetAllPicturesInfos()扩展方法！");
        }
    }
 
    private static List<PicturesInfo> GetAllPictureInfos(HSSFSheet sheet,int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
    {
        List<PicturesInfo> picturesInfoList = new List<PicturesInfo>();
 
        var shapeContainer = sheet.DrawingPatriarch as HSSFShapeContainer;
        if (null != shapeContainer)
        {
            var shapeList = shapeContainer.Children;
            foreach (var shape in shapeList)
            {
                if (shape is HSSFPicture && shape.Anchor is HSSFClientAnchor)
                {
                    var picture = (HSSFPicture)shape;
                    var anchor = (HSSFClientAnchor)shape.Anchor;
                    
                    if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
                    {
                        picturesInfoList.Add(new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data));
                    }
                }
            }
        }
 
        return picturesInfoList;
    }
 
    private static List<PicturesInfo> GetAllPictureInfos(XSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
    {
        List<PicturesInfo> picturesInfoList = new List<PicturesInfo>();
 
        var documentPartList = sheet.GetRelations();
        foreach (var documentPart in documentPartList)
        {
            if (documentPart is XSSFDrawing)
            {
                var drawing = (XSSFDrawing)documentPart;
                var shapeList = drawing.GetShapes();
                foreach (var shape in shapeList)
                {
                    if (shape is XSSFPicture)
                    {
                        var picture = (XSSFPicture)shape;
                        var anchor = picture.GetPreferredSize();
                        
                        if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
                        {
                            picturesInfoList.Add(new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data));
                        }
                    }
                }
            }
        }
 
        return picturesInfoList;
    }
 
    private static bool IsInternalOrIntersect(int? rangeMinRow, int? rangeMaxRow, int? rangeMinCol, int? rangeMaxCol,
        int pictureMinRow, int pictureMaxRow, int pictureMinCol, int pictureMaxCol, bool onlyInternal)
    {
        int _rangeMinRow = rangeMinRow ?? pictureMinRow;
        int _rangeMaxRow = rangeMaxRow ?? pictureMaxRow;
        int _rangeMinCol = rangeMinCol ?? pictureMinCol;
        int _rangeMaxCol = rangeMaxCol ?? pictureMaxCol;
 
        if (onlyInternal)
        {
            return (_rangeMinRow <= pictureMinRow && _rangeMaxRow >= pictureMaxRow &&
                    _rangeMinCol <= pictureMinCol && _rangeMaxCol >= pictureMaxCol);
        }
        else
        {
            return ((Math.Abs(_rangeMaxRow - _rangeMinRow) + Math.Abs(pictureMaxRow - pictureMinRow) >= Math.Abs(_rangeMaxRow + _rangeMinRow - pictureMaxRow - pictureMinRow)) &&
            (Math.Abs(_rangeMaxCol - _rangeMinCol) + Math.Abs(pictureMaxCol - pictureMinCol) >= Math.Abs(_rangeMaxCol + _rangeMinCol - pictureMaxCol - pictureMinCol)));
        }
    }
}

将RTF格式转成byte[]，转换完成后就可以直接写入数据库了。

 foreach (DataRow row in dataTable.Rows)
 {
     byte[] filedBlob = Encoding.Default.GetBytes(row["说明"].ToString());//转成byte[]
 }