In the project, Magicodes.IE is used as the component to export excel, but it only supports the new format xlsx. If there is a need to export the old format xls file, we can only consider the conversion solution. After trying and searching through various solutions Related solutions: I found relevant code in an article about converting xlsx to xls using NPOI, but the code only supports XSSFWorkbook conversion to HSSFWorkbook. After expansion, itsupports byte[] raw data conversion, which can be processed directly in memory. . At the same time, the original code cannot handle cell format. After the repair, cell format support is added. Styles are not supported for the time being. The following is the xlsx conversion xls tool class code:
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>
/// Convert xlsx to 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>
/// Convert xlsx to xls
/// </summary>
/// <param name="source"></param>
/// <returns></returns>
public static HSSFWorkbook ConvertWorkbookXSSFToHSSF(XSSFWorkbook source)
{<!-- -->
//Install-Package NPOI-Version 2.0.6
HSSFWorkbook 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 zone
List<CellRangeAddress> mergedRegions = new List<CellRangeAddress>();
destRow.Height = srcRow.Height;
// pour chaque row
for (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j + + )
{<!-- -->
XSSFCell oldCell = (XSSFCell)srcRow.GetCell(j); // ancienne cell
HSSFCell newCell = (HSSFCell)destRow.GetCell(j); // new cell
if (oldCell != null)
{<!-- -->
if (newCell == null)
{<!-- -->
newCell = (HSSFCell)destRow.CreateCell(j);
}
// copy chaque cell
CopyCell(oldCell, newCell, styleMap, retVal);
//copy of information into fusion entities
CellRangeAddress 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 & amp; & amp; newMergedRegion.LastColumn == 6 & amp; & amp; 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;
}
}
How to use: Magicodes.IE.excel is used, and the specific components are downloaded by yourself:
/// <summary>
/// Export XLS
/// </summary>
/// <returns></returns>
[ApiDescriptionSettings(Name = "ExportXLS"), NonUnify]
[DisplayName("Export 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 = "Export data" + DateTime.Now.ToString(" yyyyMMddHHmmss") + ".xls" };
}
Through this line-by-line conversion scheme, the old format xls can theoretically be converted into the new format xlsx.
refer to:
Convert xlsx file to xls file using NPOI in c# https://www.itbaoku.cn/post/1946308.html?view=all
Convert the HSSFWorkbook object into an input stream https://www.cnblogs.com/slzys/p/13590907.html
Java implements modification of data format in excel https://blog.csdn.net/weixin_45706856/article/details/130328932
C# NPOI export Excel date format https://blog.51cto.com/u_15976398/6099632