Excelファイルのインポート処理

以下のコードは、OpenFileDialogを使用して.xls/.xlsx形式のExcelファイルを選択し、Aspose.Cellsライブラリを利用してデータを読み込む処理を示しています。

private void ExcelImport()
{
    OpenFileDialog dialog = new OpenFileDialog();
    dialog.Filter = "Excelファイル(*.xls,*.xlsx)|*.xls;*.xlsx";
    
    if (dialog.ShowDialog() == DialogResult.OK)
    {
        string filePath = dialog.FileName;
        Workbook workbook = new Workbook(filePath);
        List<string[]> dataList = ExtractExcelData(workbook);
    }
}

ワークシートデータの抽出メソッド

public List<string[]> ExtractExcelData(Workbook workbook)
{
    List<string[]> result = new List<string[]>();
    int sheetCount = workbook.Worksheets.Count;

    for (int i = 0; i < sheetCount; i++)
    {
        Worksheet sheet = workbook.Worksheets[i];
        Cells cells = sheet.Cells;
        int maxRow = cells.MaxRow;
        int maxCol = cells.MaxColumn;

        int nameCol = -1;
        int attachCol = -1;
        int descCol = -1;
        int detailCol = -1;

        // 列位置の特定
        for (int c = 0; c <= maxCol; c++)
        {
            string header = cells[2, c].StringValue.Trim();
            if (header == "備考") descCol = c;
        }

        for (int c = 0; c <= maxCol; c++)
        {
            string header = cells[3, c].StringValue.Trim();
            if (header == "承認明細事項") nameCol = c;
            if (header == "細項") detailCol = c;
            if (header == "事前条件および作業要件") attachCol = c;
        }

        // データ行の処理
        if (nameCol >= 0 && attachCol >= 0 && descCol >= 0)
        {
            for (int r = 4; r <= maxRow; r++)
            {
                string[] rowData = new string[6];
                string currentName = "";
                string currentDetail = "";
                string currentAttach = "";
                string currentDesc = "";

                for (int c = 0; c <= maxCol; c++)
                {
                    bool isMerged = cells[r, c].IsMerged;
                    string cellValue = cells[r, c].StringValue.Trim();

                    if (c == nameCol)
                    {
                        currentName = cellValue;
                        if (isMerged && string.IsNullOrEmpty(currentName))
                            currentName = GetPreviousValue(result, 0);
                    }

                    if (c == detailCol)
                    {
                        currentDetail = cellValue;
                        if (isMerged && string.IsNullOrEmpty(currentDetail))
                            currentDetail = GetPreviousValue(result, 1);
                    }

                    if (c == attachCol)
                        currentAttach = cellValue;

                    if (c == descCol)
                        currentDesc = cellValue;
                }

                rowData[0] = currentName;
                rowData[1] = currentDetail;
                rowData[2] = currentAttach;
                rowData[3] = currentDesc;
                result.Add(rowData);
            }
        }
    }
    return result;
}

private string GetPreviousValue(List<string[]> list, int index)
{
    if (list == null || list.Count == 0)
        return string.Empty;
        
    return list[list.Count - 1][index];
}

Excelファイルのエクスポート処理

以下のコードは、SaveFileDialogを使用して保存先を指定し、データをExcel形式で出力する処理を示しています。

private void ExcelExport()
{
    SaveFileDialog dialog = new SaveFileDialog();
    dialog.Filter = "Excelファイル(*.xls,*.xlsx)|*.xls;*.xlsx";
    dialog.FileName = flowName + ".xlsx";

    if (dialog.ShowDialog() == DialogResult.OK)
    {
        string filePath = dialog.FileName;
        Workbook workbook;

        if (File.Exists(filePath))
            workbook = new Workbook(filePath);
        else
            workbook = new Workbook();

        Worksheet sheet = workbook.Worksheets[0];
        Cells cells = sheet.Cells;

        try
        {
            GenerateExcelContent(workbook, cells);
            MessageBox.Show("エクスポート成功！");
        }
        catch
        {
            MessageBox.Show("エクスポート失敗！");
        }
    }
}

Excel書式設定とデータ出力

public void GenerateExcelContent(Workbook workbook, Cells cells)
{
    int rowCount = 4 + routeCount;
    int columnCount = 25;

    for (int i = 0; i < rowCount; i++)
    {
        Style style = CreateCellStyle(workbook);
        
        if (i == 0)
        {
            style.Font.Color = Color.Red;
            style.Font.Size = 16;
            cells.Merge(0, 0, 1, columnCount);
            cells[i, 0].PutValue("統合管路決定権体系事項");
            cells[0, 0].SetStyle(style);
            cells.SetRowHeight(0, 38);
            cells.SetColumnWidth(1, 20);
        }
        else if (i > 0)
        {
            ProcessRowData(i, cells, style);
        }
    }
}

private void ProcessRowData(int rowIndex, Cells cells, Style style)
{
    if (rowIndex > 3)
    {
        cells.SetRowHeight(rowIndex, 42);
        // 各列のデータ処理とセルへの設定
        // 実装内容はデータ構造に応じて変更
    }

    for (int colIndex = 0; colIndex < 25; colIndex++)
    {
        cells[rowIndex, colIndex].SetStyle(style);
        
        if (rowIndex > 3)
        {
            // カラムごとのデータ設定
        }
        else
        {
            ConfigureHeaderCells(cells, rowIndex, colIndex);
        }
    }
}

セルスタイルの設定

public Style CreateCellStyle(Workbook workbook)
{
    Style style = workbook.Styles[workbook.Styles.Add()];
    style.HorizontalAlignment = TextAlignmentType.Center;
    style.Font.Name = "MS UI Gothic";
    style.Font.Size = 10;
    style.IsLocked = false;
    style.Font.IsBold = false;
    style.ForegroundColor = Color.FromArgb(255, 255, 255);
    style.Pattern = BackgroundType.Solid;
    style.IsTextWrapped = true;

    CellBorderType borderStyle = CellBorderType.Thin;
    style.Borders[BorderType.LeftBorder].LineStyle = borderStyle;
    style.Borders[BorderType.RightBorder].LineStyle = borderStyle;
    style.Borders[BorderType.TopBorder].LineStyle = borderStyle;
    style.Borders[BorderType.BottomBorder].LineStyle = borderStyle;

    return style;
}

public void ConfigureHeaderCells(Cells cells, int row, int column)
{
    if (row == 1)
    {
        if (column == 0)
        {
            cells.Merge(1, column, 3, 1);
            cells[row, column].PutValue("番号");
            cells.SetColumnWidth(column, 5);
        }
        if (column == 1)
        {
            cells.Merge(1, column, 3, 1);
            cells.SetColumnWidth(column, 5);
        }
    }
}