有没有办法使用 POI 将工作表的水平滚动条向左移动一列?

Question

我写了一个读取Excel模板表的程序。其中隐藏了第一列。现在，我有一个以编程方式取消隐藏 excel 列的代码(因此列从 A1 开始(。

我使用的是Apache POI 3.16版本。

当我打开一个文件时，它应该显示来自 A1 的列，而不是显示来自 B1 列的我。 当我为 XLS 编写以下代码时，它工作正常，但不适用于 XLSX 格式。

sheet.showInPane(0, 0);

我需要手动移动水平滚动条才能查看我的第一列。我应该如何以编程方式实现此目的以自动滚动到 XLSX 格式的第一列？

这是我的完整代码。

public Workbook readWorkBookAndWriteErrors(String bufId,String inputFile, String ext) throws Exception {
Workbook workBook =null;
Sheet sheet = null; 
if(GlobalVariables.EXCEL_FORMAT_XLS.equalsIgnoreCase(ext)){
// Get the workbook instance for XLS file
workBook = new HSSFWorkbook(new FileInputStream(inputFile));
}else{
// Get the workbook instance for XLSX file
workBook = new XSSFWorkbook(new FileInputStream(inputFile));
}
sheet = workBook.getSheetAt(0);
Row row = null;
if(sheet.isColumnHidden(0)){
sheet.setColumnHidden(0, false);
sheet.setActiveCell(new CellAddress("A1"));
sheet.showInPane(0, 0);
sheet.createFreezePane(0, 1);
Iterator<Row> rowIterator = sheet.iterator();
int rowIndex = 1;
while (rowIterator.hasNext()) {
row = rowIterator.next();
if(rowIndex == 1){
rowIndex++;
continue;
}
Cell cell = row.createCell(0);
cell.setCellValue("error message");
rowIndex++;
}
}
return workBook;
}

Answer 1

这是我问题的答案。请参考此来源

public Workbook readWorkBookAndWriteErrors(String bufId,String inputFile, String ext) throws Exception {
Workbook workBook =null;
Sheet sheet = null; 
if(GlobalVariables.EXCEL_FORMAT_XLS.equalsIgnoreCase(ext)){
// Get the workbook instance for XLS file
workBook = new HSSFWorkbook(new FileInputStream(inputFile));
}else{
// Get the workbook instance for XLSX file
workBook = new XSSFWorkbook(new FileInputStream(inputFile));
}
sheet = workBook.getSheetAt(0);
Row row = null;
if(sheet.isColumnHidden(0)){
sheet.setColumnHidden(0, false);
if(sheet instanceof XSSFSheet){
CTWorksheet ctWorksheet = null; 
CTSheetViews ctSheetViews = null; 
CTSheetView ctSheetView = null; 
XSSFSheet tempSheet = (XSSFSheet) sheet;
// First step is to get at the CTWorksheet bean underlying the worksheet. 
ctWorksheet = tempSheet.getCTWorksheet(); 
// From the CTWorksheet, get at the sheet views. 
ctSheetViews = ctWorksheet.getSheetViews(); 
// Grab a single sheet view from that array 
ctSheetView = ctSheetViews.getSheetViewArray(ctSheetViews.sizeOfSheetViewArray() - 1); 
// Se the address of the top left hand cell. 
ctSheetView.setTopLeftCell("A1"); 
}else{
sheet.setActiveCell(new CellAddress("A1"));
sheet.showInPane(0, 0);
}

Iterator<Row> rowIterator = sheet.iterator();
int rowIndex = 1;
while (rowIterator.hasNext()) {
row = rowIterator.next();
if(rowIndex == 1){
rowIndex++;
continue;
}
Cell cell = row.createCell(0);
cell.setCellValue("error message");
rowIndex++;
}
}
return workBook;
}

Answer 2

如果您使用的是最新的 C# NPOI，我制作了这个实用程序函数：

/// <summary>Set view position at given coordinates.</summary>
/// <param name="sheet">A reference to the Excel sheet.</param>
/// <param name="topLeftCell">The coordinates of the cell that will show in top left corner when you open the Excel sheet (example: "A1").</param>
/// <param name="onlyFirstView">If true, only first sheet view will have position adjusted. If false, every views from the sheet will have have position adjusted.</param>
public static void SetViewPosition(ref NPOI.SS.UserModel.ISheet sheet, string topLeftCell = "A1", bool onlyFirstView = true)
{
NPOI.OpenXmlFormats.Spreadsheet.CT_Worksheet worksheet = (NPOI.OpenXmlFormats.Spreadsheet.CT_Worksheet)(typeof(NPOI.XSSF.UserModel.XSSFSheet).GetField("worksheet", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance)?.GetValue((NPOI.XSSF.UserModel.XSSFSheet)sheet));
if (worksheet?.sheetViews?.sheetView != null && worksheet.sheetViews.sheetView.Count > 0)
{
if (onlyFirstView)
worksheet.sheetViews.sheetView[0].topLeftCell = topLeftCell;
else
foreach (NPOI.OpenXmlFormats.Spreadsheet.CT_SheetView view in worksheet.sheetViews.sheetView)
view.topLeftCell = topLeftCell;
}
}

使用示例：

NPOI.SS.UserModel.ISheet mySheet = myWorkbook.GetSheetAt(0);
// First sheet from our workbook opens with view set to top left corner (cell A1 visible in top left corner).
SetViewPosition(ref mySheet, "A1");

Answer 3

下面的代码为我将水平滚动条移动到所需位置。

使用apache poi libraries(poi， poi-ooxml(.

((XSSFSheet(sheet(.getCTWorksheet((.getSheetViews((.getSheetViewArray(0(.setTopLeftCell("AE11"(;