123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311 |
-
- using NPOI.XSSF.UserModel;
- using NPOI.SS.UserModel;
- using NPOI.SS.Util;
- using NPOI.HSSF.UserModel;
- using System.Collections.Generic;
- using System.Linq;
- using System.IO;
- namespace Uninpho.Tools
- {
- public static class NPOIExt
- {
- public static bool ConvertToXlsx(string xlsPath, string newExcelPath)
- {
- var streamRead = new FileStream(xlsPath, FileMode.Open, FileAccess.Read);
- var pReadByte = Convert(streamRead);
- FileStream pFileStream = null;
- try
- {
- pFileStream = new FileStream(newExcelPath, FileMode.OpenOrCreate);
- pFileStream.Write(pReadByte, 0, pReadByte.Length);
- }
- catch
- {
- return false;
- }
- finally
- {
- if (pFileStream != null)
- pFileStream.Close();
- }
- return true;
- }
- private static byte[] Convert(Stream sourceStream)
- {
- var source = new HSSFWorkbook(sourceStream);
- var destination = new XSSFWorkbook();
- for (int i = 0; i < source.NumberOfSheets; i++)
- {
- var xssfSheet = (XSSFSheet)destination.CreateSheet(source.GetSheetAt(i).SheetName);
- var hssfSheet = (HSSFSheet)source.GetSheetAt(i);
- CopyStyles(hssfSheet, xssfSheet);
- CopySheets(hssfSheet, xssfSheet);
- }
- using (var ms = new MemoryStream())
- {
- destination.Write(ms);
- return ms.ToArray();
- }
- }
- private static void CopyStyles(HSSFSheet from, XSSFSheet to)
- {
- for (short i = 0; i <= from.Workbook.NumberOfFonts; i++)
- {
- CopyFont(to.Workbook.CreateFont(), from.Workbook.GetFontAt(i));
- }
- for (short i = 0; i < from.Workbook.NumCellStyles; i++)
- {
- CopyStyle(to.Workbook.CreateCellStyle(), from.Workbook.GetCellStyleAt(i), to.Workbook, from.Workbook);
- }
- }
- private static void CopyFont(IFont toFront, IFont fontFrom)
- {
- toFront.Boldweight = fontFrom.Boldweight;
- toFront.Charset = fontFrom.Charset;
- toFront.Color = fontFrom.Color;
- toFront.FontHeightInPoints = fontFrom.FontHeightInPoints;
- toFront.FontName = fontFrom.FontName;
- toFront.IsBold = fontFrom.IsBold;
- toFront.IsItalic = fontFrom.IsItalic;
- toFront.IsStrikeout = fontFrom.IsStrikeout;
-
- }
- private static void CopyStyle(ICellStyle toCellStyle, ICellStyle fromCellStyle, IWorkbook toWorkbook, IWorkbook fromWorkbook)
- {
- toCellStyle.Alignment = fromCellStyle.Alignment;
- toCellStyle.BorderBottom = fromCellStyle.BorderBottom;
- toCellStyle.BorderDiagonal = fromCellStyle.BorderDiagonal;
- toCellStyle.BorderDiagonalColor = fromCellStyle.BorderDiagonalColor;
- toCellStyle.BorderDiagonalLineStyle = fromCellStyle.BorderDiagonalLineStyle;
- toCellStyle.BorderLeft = fromCellStyle.BorderLeft;
- toCellStyle.BorderRight = fromCellStyle.BorderRight;
- toCellStyle.BorderTop = fromCellStyle.BorderTop;
- toCellStyle.BottomBorderColor = fromCellStyle.BottomBorderColor;
- toCellStyle.DataFormat = fromCellStyle.DataFormat;
- toCellStyle.FillBackgroundColor = fromCellStyle.FillBackgroundColor;
- toCellStyle.FillForegroundColor = fromCellStyle.FillForegroundColor;
- toCellStyle.FillPattern = fromCellStyle.FillPattern;
- toCellStyle.Indention = fromCellStyle.Indention;
- toCellStyle.IsHidden = fromCellStyle.IsHidden;
- toCellStyle.IsLocked = fromCellStyle.IsLocked;
- toCellStyle.LeftBorderColor = fromCellStyle.LeftBorderColor;
- toCellStyle.RightBorderColor = fromCellStyle.RightBorderColor;
- toCellStyle.Rotation = fromCellStyle.Rotation;
- toCellStyle.ShrinkToFit = fromCellStyle.ShrinkToFit;
- toCellStyle.TopBorderColor = fromCellStyle.TopBorderColor;
- toCellStyle.VerticalAlignment = fromCellStyle.VerticalAlignment;
- toCellStyle.WrapText = fromCellStyle.WrapText;
- toCellStyle.SetFont(toWorkbook.GetFontAt((short)(fromCellStyle.GetFont(fromWorkbook).Index + 1)));
- }
- private static void CopySheets(HSSFSheet source, XSSFSheet destination)
- {
- var maxColumnNum = 0;
- var mergedRegions = new List<CellRangeAddress>();
- var styleMap = new Dictionary<int, HSSFCellStyle>();
- for (int i = source.FirstRowNum; i <= source.LastRowNum; i++)
- {
- var srcRow = (HSSFRow)source.GetRow(i);
- var destRow = (XSSFRow)destination.CreateRow(i);
- if (srcRow != null)
- {
- CopyRow(source, destination, srcRow, destRow, mergedRegions);
- if (srcRow.LastCellNum > maxColumnNum)
- {
- maxColumnNum = srcRow.LastCellNum;
- }
- }
- }
- for (int i = 0; i <= maxColumnNum; i++)
- {
- destination.SetColumnWidth(i, source.GetColumnWidth(i));
- }
- }
- private static void CopyRow(HSSFSheet srcSheet, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, List<CellRangeAddress> mergedRegions)
- {
- destRow.Height = srcRow.Height;
- for (int j = srcRow.FirstCellNum; srcRow.LastCellNum >= 0 && j <= srcRow.LastCellNum; j++)
- {
- var oldCell = (HSSFCell)srcRow.GetCell(j);
- var newCell = (XSSFCell)destRow.GetCell(j);
- if (oldCell != null)
- {
- if (newCell == null)
- {
- newCell = (XSSFCell)destRow.CreateCell(j);
- }
- CopyCell(oldCell, newCell);
- var mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum,
- (short)oldCell.ColumnIndex);
- if (mergedRegion != null)
- {
- var newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,
- mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);
- if (IsNewMergedRegion(newMergedRegion, mergedRegions))
- {
- mergedRegions.Add(newMergedRegion);
- destSheet.AddMergedRegion(newMergedRegion);
- }
- }
- }
- }
- }
- private static void CopyCell(HSSFCell oldCell, XSSFCell newCell)
- {
- CopyCellStyle(oldCell, newCell);
- CopyCellValue(oldCell, newCell);
- }
- private static void CopyCellValue(HSSFCell oldCell, XSSFCell newCell)
- {
- 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 void CopyCellStyle(HSSFCell oldCell, XSSFCell newCell)
- {
- if (oldCell.CellStyle == null) return;
- newCell.CellStyle = newCell.Sheet.Workbook.GetCellStyleAt((short)(oldCell.CellStyle.Index + 1));
- }
- private static CellRangeAddress GetMergedRegion(HSSFSheet sheet, int rowNum, short cellNum)
- {
- for (var i = 0; i < sheet.NumMergedRegions; i++)
- {
- var merged = sheet.GetMergedRegion(i);
- if (merged.IsInRange(rowNum, cellNum))
- {
- return merged;
- }
- }
- return null;
- }
- private static bool IsNewMergedRegion(CellRangeAddress newMergedRegion,
- List<CellRangeAddress> mergedRegions)
- {
- return !mergedRegions.Any(r =>
- r.FirstColumn == newMergedRegion.FirstColumn &&
- r.LastColumn == newMergedRegion.LastColumn &&
- r.FirstRow == newMergedRegion.FirstRow &&
- r.LastRow == newMergedRegion.LastRow);
- }
- }
- }
|