В этой статье я постараюсь рассказать об одном из возможных путей конвертации документов формата xls в xml.


Непосредственно моя задача выглядела примерно так:


Дано:


  1. 1-2 дня времени на поиск и реализацию решения
  2. Найденное решение должно быть настолько бесплатным, насколько это возможно
  3. Известна примерная структура, размер и содержание файла, которые должны быть обработаны, что снимает необходимость читать графики, диаграммы и иные медианные из документа.
  4. Использовать Interop, равно как и иные библиотеки, которые требуют установки Office нельзя, так как это создает лишнюю нагрузку на сервер и требует дополнительной установки офисного пакета.

Должен заметить, эта задача отнюдь не из простых, и решить ее, также как это было сделано с новыми офисными форматами (docx и xlsx), не получится по следующим причинам:


  • Во-первых, Microsoft не предлагает каких-либо решений для чтения указанных форматов (Interopt в расчет не берем, так как его использование требует установки офисного пакета на сервере, а чтение данных из одного файлика будет сжирать необоснованно много оперативы).
  • Во-вторых, вся информация в документах xls (равно как и в doc) хранится в бинарном виде и недоступна для свободного чтения.
  • В-третьих, в интернете практически нет информации о библиотеках, которые бы решали задачу чтения информации из старых форматов excel и word, равно как и задачу преобразования старых форматов в новые.

Перерыв stackoverflow, Хабр, проконсультировавшись со старшими товарищами, а также обратившись к нижегородскому сообществу .Net разработчиков стало понятно, что наиболее верным способом решения поставленной задачи является конвертация xls в xlsx, ну а как работать с xlsx я уже знал.


Однако, нативной библиотеки от Microsoft, которая бы решала, казалось бы, столь очевидную проблему совместимости старых и новых офисных форматов, в природе не существует. Платные библиотеки требовали обоснования необходимости их покупки и, также, отдаляли момент начала их использования (на срок проведения закупочных процедур), а бесплатные решения, написанные для питона и линукса, требовали внесения существенных изменений в архитектуру проекта. И вот тут, казалось бы, самое время начинать рвать волосы из подмышек, но, как говорится, всегда найдется азиат!


Tony Qu является автором крутой библиотеки, написанной как раз для работы с офисными файлами. Изначально либа была написана для джавы, после чего портирована под .Net, правда с некоторыми ограничениями (в версии под .Net отсутствует возможность работы с документами doc).
Но, возможно, данная статья не появилась бы на свет, если бы не огромная ложка дегтя: пользователи библиотеки NPOI напрочь лишены какой-либо документации, описывающей алгорит��ы ее использования. Разумеется, ничто не мешает получить персональную поддержку от разработчика, уже за материальное вознаграждение.


Поэтому, думаю, представленное ниже решение, можно использовать не только для конвертации xls в xlsx, но и в качестве небольшого примера, демонстрирующего возможности либы.


Давайте кодить


Подготовка


  1. Устанавливаем через nuget библиотеку NPOI (в nuget лежит две либы: одна официальная от Tony Qu, а вторая не понятно от кого и якобы для netcore — ставим первую. Ничего плохого про вторую сказать не могу, так как я ей не пользовался, но миллионы мух пользователей не могут ошибаться):
  2. Создаем класс XlsToXlsx и метод public MemoryStream Convert(Stream sourceStream):
    public MemoryStream Convert(Stream sourceStream)
{
// Открытие xls
var source = new HSSFWorkbook(sourceStream);
// Создание объекта для будущего xlsx
var destination = new XSSFWorkbook();
// Копируем листы из xls и добавляем в xlsx
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);
    CopySheet(hssfSheet, xssfSheet);
}
// Возвращаем сконвертированный результат
using (var ms = new MemoryStream())
{
    destination.Write(ms);
    return ms;
}
}

Как видите, основной метод конвертации работает со стримами. Не переживайте, для результата выполнения метода мы создадим обертки, которые позволят нам сохранять результат конвертации в файл (причем, как из файла, так и из потока).
За работу с файлами xls в библиотеке NPOI отвечает класс HSSFWorkbook, конструктор которого может принимать как стрим, так и путь до файла.
Наш будущий сконвертированный xlsx будет находиться в переменной типа XSSFWorkbook.
Далее запускаем цикл, в котором будет происходить копирование данных из xls в xlsx. Как мы видим, на каждой итерации цикла создаются по два объекта. Обратите внимание, что при создании объекта xssfSheet сразу же происходит копирование имени листа из объекта класса HSSFSheet.
В последствии, указанные объекты используются в качестве аргументов в функциях, копирующих стили листов и их содержимое.
Результат конвертации возвращается в виде потока типа MemoryStream


Разберем метод, отвечающий за копирование стилей


private 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);
    }
}

Как вы видите, для того, чтобы сохранить аутентичное внешнее представление, нам необходимо произвести копирование шрифтов из одного файла в другой, а также применить стили старого файла к соответствующим ячейкам нового.


За копирование шрифтов отвечает метод void CopyFont(IFont toFront, IFont fontFrom)


private 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 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)));
}

Опять же, из названия свойств можно сделать вывод о том, что и куда копируется. Единственное, обратите внимание на то, что применение конкретных семейств шрифтов происходит через вызов метода SetFont, а не путем прямого присвоения значения через свойства класса.


Копирование данных из файла xls в новый файл xlsx


Перейдем к процессу копирования содержимого из файла xls в новый файл xlsx.


private void CopySheet(HSSFSheet source, XSSFSheet destination)
{
    var maxColumnNum = 0;
    var mergedRegions = new List<CellRangeAddress>();
    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));
    }
}

Логика данного метода сводится к вызову метода CopyRow(source, destination, srcRow, destRow, mergedRegions) — что происходит в нем расскажу чуть ниже. Помимо вызова указанного метода, в CopySheet происходит поиск номер последней заполненной ячейки в строке. Это необходимо для того, чтобы верно скопировать ширину каждого столбца (да, конвертация xls в xlsx предусматривает даже такой нюанс).


Разберем метод void CopyRow(HSSFSheet srcSheet, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, List<CellRangeAddress> mergedRegions):


private 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);
                }
            }
        }
    }
}

Этот метод выполняет две функции:


  • Во-первых, метод копирует данные из "простых" ячеек файла с расширением xls в "простые" ячейки файла с расширением xlsx
  • Во-вторых, метод обрабатывает случаи, когда ячейка находится внутри группы объединенных ячеек. При этом, проверяется, относится ли данная ячейка к уже существующей группе объединенных ячеек, или же является первой выявленной ячейкой новой группы.

Метод копирования данных и стилей для каждой ячейки:


private void CopyCell(HSSFCell oldCell, XSSFCell newCell)
{
    CopyCellStyle(oldCell, newCell);
    CopyCellValue(oldCell, newCell);
}

В этом методе происходит следующее:


  • Во-первых, копируются стили:
    private void CopyCellStyle(HSSFCell oldCell, XSSFCell newCell)
{
if (oldCell.CellStyle == null)
    return;
newCell.CellStyle = newCell.Sheet.Workbook.GetCellStyleAt((short)(oldCell.CellStyle.Index + 1));
}
  • Во-вторых, копируется содержание каждой ячейки:
    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;
}
}

Как можно видеть, в указанном методе происходит не слепое копирование содержимого ячеек, а с сохранением типов данных и исходного функционала (когда, ячейка содержит функцию).


Обработка объединенных ячеек


Вернемся к обработке ячеек, включенных в группу объединенных.
Как было показано выше, за это отвечают методы CellRangeAddress GetMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) и bool IsNewMergedRegion(CellRangeAddress newMergedRegion, List<CellRangeAddress> mergedRegions):


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;
}

Так, мы просто получаем все области объединенных ячеек для конкретного листа, после чего проверяем, входит ли наша ячейка хоть в какой-нибудь из диапазонов объединенных ячеек. Если входит, то то метод возвращает тот диапазон объединенных ячеек, к которому относится проверяемая ячейка. В противно случае, вернется null.


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);
}

Указанный метод сопоставляет текущую область объединенных ячеек с той областью, к которой относится текущая ячейка, которая входит в состав нескольких объединенных ячеек. Как можно увидеть, сравнение происходит по 4-м крайним точкам каждой из областей (первая и последняя колонка, первая и последняя строчка проверяемой области).


На этом процесс преобразования xls в xlsx можно считать законченным.


С моей стороны будет свинством не сказать, что данный код был нагло сплагиачен со stackoverflow, который в свою очередь представляет собой переработанное решение по конвертации xlsx в xls, которое также лежит на stackoverflow.


Дополнительный функционал


Предлагаю добавить возможность конвертации с записью полученного результата в файл:


  • из потока
    public void ConvertToXlsxFile(MemoryStream stream, string path)
{
var result = Convert(stream);
using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate))
{
    fs.Write(result.ToArray());
}
}
  • из файла:
    public void ConvertToXlsxFile(string xlsPath, string destPath)
{
MemoryStream result;
using (FileStream fs = new FileStream(xlsPath, FileMode.Open))
{
    result = Convert(fs);
}
using (FileStream fs = new FileStream(destPath, FileMode.OpenOrCreate))
{
    fs.Write(result.ToArray());
}
}

Также, не будем забывать, что цикл статей посвящен конвертации разных текстовых файлов в xml:


public class XlsToXml : IConvertable
{
public string Convert(Stream stream)
{
    XlsToXlsx excelConvert = new XlsToXlsx();
    Stream str = excelConvert.Convert(stream);
    XlsxToXml converter = new XlsToXml();
    return converter.Convert(str);
}
public string ConvertByFile(string path)
{
    using (FileStream fs = File.OpenRead(path))
    {
        return Convert(fs);
    }
}
}

Как видите, в методе данного класса используется объект класса XlsxToXml, который реализует логику конвертации xlsx в xml, описанную в статье по этой ссылке.


С полным кодом программы Вы можете ознакомиться здесь