在c#中使用NPOI结合Magicodes.IE.excel将xlsx文件内存中转换为xls文件

项目中使用Magicodes.IE作为导出excel的组件，但只支持新格式xlsx，有需求要导出旧格式xls文件，因此只能考虑转换的方案，经多种方案尝试和查找相关解决方案，在一份使用NPOI转换的xlsx到xls的文章到找到相关代码，但代码中只支持XSSFWorkbook转换以HSSFWorkbook，扩展后支持byte[]原始数据转换，可以在内存中直接处理。同时原来代码不能处理单元格格式，这里修复后加入单元格格式支持，暂时不支持样式。以下为xlsx转换xls工具类代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.Util;
using NPOI.XSSF.UserModel;namespace Application.Util;
public static class ConvertXLSXToXLS
{/// <summary>/// 转换xlsx到xls/// </summary>/// <param name="source"></param>/// <returns></returns>public static byte[] ConvertWorkbookXSSFToHSSF(byte[] source){using (var stream = new MemoryStream(source)){XSSFWorkbook xwb = new XSSFWorkbook(stream);HSSFWorkbook hwb = ConvertWorkbookXSSFToHSSF(xwb);ByteArrayOutputStream bos = new ByteArrayOutputStream();hwb.Write(bos);return bos.ToByteArray();}}/// <summary>/// 转换xlsx到xls/// </summary>/// <param name="source"></param>/// <returns></returns>public static HSSFWorkbook ConvertWorkbookXSSFToHSSF(XSSFWorkbook source){//Install-Package NPOI -Version 2.0.6HSSFWorkbook retVal = new HSSFWorkbook();for (int i = 0; i < source.NumberOfSheets; i++){HSSFSheet hssfSheet = (HSSFSheet)retVal.CreateSheet(source.GetSheetAt(i).SheetName);XSSFSheet xssfsheet = (XSSFSheet)source.GetSheetAt(i);CopySheets(xssfsheet, hssfSheet, retVal);}return retVal;}private static void CopySheets(XSSFSheet source, HSSFSheet destination, HSSFWorkbook retVal){int maxColumnNum = 0;Dictionary<int, XSSFCellStyle> styleMap = new Dictionary<int, XSSFCellStyle>();for (int i = source.FirstRowNum; i <= source.LastRowNum; i++){XSSFRow srcRow = (XSSFRow)source.GetRow(i);HSSFRow destRow = (HSSFRow)destination.CreateRow(i);if (srcRow != null){CopyRow(source, destination, srcRow, destRow, styleMap, retVal);if (srcRow.LastCellNum > maxColumnNum){maxColumnNum = srcRow.LastCellNum;}}}for (int i = 0; i <= maxColumnNum; i++){destination.SetColumnWidth(i, source.GetColumnWidth(i));}}private static void CopyRow(XSSFSheet srcSheet, HSSFSheet destSheet, XSSFRow srcRow, HSSFRow destRow,Dictionary<int, XSSFCellStyle> styleMap, HSSFWorkbook retVal){// manage a list of merged zone in order to not insert two times a// merged zoneList<CellRangeAddress> mergedRegions = new List<CellRangeAddress>();destRow.Height = srcRow.Height;// pour chaque rowfor (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j++){XSSFCell oldCell = (XSSFCell)srcRow.GetCell(j); // ancienne cellHSSFCell newCell = (HSSFCell)destRow.GetCell(j); // new cellif (oldCell != null){if (newCell == null){newCell = (HSSFCell)destRow.CreateCell(j);}// copy chaque cellCopyCell(oldCell, newCell, styleMap, retVal);// copy les informations de fusion entre les cellulesCellRangeAddress mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum,(short)oldCell.ColumnIndex);if (mergedRegion != null){CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);if (IsNewMergedRegion(newMergedRegion, mergedRegions)){mergedRegions.Add(newMergedRegion);destSheet.AddMergedRegion(newMergedRegion);}if (newMergedRegion.FirstColumn == 0 && newMergedRegion.LastColumn == 6 && newMergedRegion.FirstRow == newMergedRegion.LastRow){HSSFCellStyle style2 = (HSSFCellStyle)retVal.CreateCellStyle();style2.VerticalAlignment = VerticalAlignment.Center;style2.Alignment = HorizontalAlignment.Left;style2.FillForegroundColor = HSSFColor.Teal.Index;style2.FillPattern = FillPattern.SolidForeground;for (int i = destRow.FirstCellNum; i <= destRow.LastCellNum; i++){if (destRow.GetCell(i) != null)destRow.GetCell(i).CellStyle = style2;}}}}}}private static void CopyCell(XSSFCell oldCell, HSSFCell newCell, Dictionary<int, XSSFCellStyle> styleMap, HSSFWorkbook retVal){if (styleMap != null){int stHashCode = oldCell.CellStyle.Index;XSSFCellStyle sourceCellStyle = null;if (styleMap.TryGetValue(stHashCode, out sourceCellStyle)) { }HSSFCellStyle destnCellStyle = (HSSFCellStyle)newCell.CellStyle;if (sourceCellStyle == null){//sourceCellStyle = (XSSFCellStyle)oldCell.Sheet.Workbook.CreateCellStyle();sourceCellStyle = (XSSFCellStyle)oldCell.CellStyle;}//destnCellStyle.CloneStyleFrom(oldCell.CellStyle);CloneCellStyle(sourceCellStyle,ref destnCellStyle, retVal);if (!styleMap.Any(p => p.Key == stHashCode)){styleMap.Add(stHashCode, sourceCellStyle);}destnCellStyle.VerticalAlignment = VerticalAlignment.Top;newCell.CellStyle = (HSSFCellStyle)destnCellStyle;}switch (oldCell.CellType){case CellType.String:newCell.SetCellValue(oldCell.StringCellValue);break;case CellType.Numeric:newCell.SetCellValue(oldCell.NumericCellValue);break;case CellType.Blank:newCell.SetCellType(CellType.Blank);break;case CellType.Boolean:newCell.SetCellValue(oldCell.BooleanCellValue);break;case CellType.Error:newCell.SetCellErrorValue(oldCell.ErrorCellValue);break;case CellType.Formula:newCell.SetCellFormula(oldCell.CellFormula);break;default:break;}}private static CellRangeAddress GetMergedRegion(XSSFSheet sheet, int rowNum, short cellNum){for (int i = 0; i < sheet.NumMergedRegions; i++){CellRangeAddress merged = sheet.GetMergedRegion(i);if (merged.IsInRange(rowNum, cellNum)){return merged;}}return null;}private static bool IsNewMergedRegion(CellRangeAddress newMergedRegion,List<CellRangeAddress> mergedRegions){return !mergedRegions.Contains(newMergedRegion);}public static void CloneCellStyle(XSSFCellStyle sourceCellStyle, ref HSSFCellStyle destnCellStyle, HSSFWorkbook retVal){IDataFormat dataformat = retVal.CreateDataFormat();string cellStyleString = sourceCellStyle.GetDataFormatString();ICellStyle dateStyle = retVal.CreateCellStyle();dateStyle.DataFormat = dataformat.GetFormat(cellStyleString);destnCellStyle = (HSSFCellStyle)dateStyle;}
}

使用方式，使用了Magicodes.IE.excel，具体组件自行下载：

    /// <summary>/// 导出XLS/// </summary>/// <returns></returns>[ApiDescriptionSettings(Name = "ExportXLS"), NonUnify][DisplayName("导出XLS")]public async Task<IActionResult> ExportXLS(RecordInput input){var query = QueryData(input);IExcelExporter excelExporter = new ExcelExporter();var res = await excelExporter.ExportAsByteArray(query.ToList());return new FileStreamResult(new MemoryStream(ConvertXLSXToXLS.ConvertWorkbookXSSFToHSSF(res)), "application/octet-stream") { FileDownloadName = "导出数据" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls" };}

通过这种一行行转换的方案，理论上也可以将旧格式xls转换为新格式xlsx。

参考：

在c#中使用NPOI将xlsx文件转换为xls文件
https://www.itbaoku.cn/post/1946308.html?view=all

HSSFWorkbook对象转换成输入流
https://www.cnblogs.com/slzys/p/13590907.html

java实现修改excel中数据格式
https://blog.csdn.net/weixin_45706856/article/details/130328932

C# NPOI 导出Excel 日期格式
https://blog.51cto.com/u_15976398/6099632