如何在 Java 中删除 Excel 行和列？

最新推荐文章于 2024-06-18 09:17:43 发布

cunxiedian8614

最新推荐文章于 2024-06-18 09:17:43 发布

阅读量3.2k

点赞数

文章标签： java excel

您可能遇到了需要从工作表中删除空白行/列或删除包含特定关键字的行/列的情况。在本文中，我将向您展示如何使用Free Spire.XLS for Java删除指定的行/列以及如何批量删除一些特殊的行/列。

将Spire.Xls.jar添加为依赖项

方法1：下载免费的Spire.XLS for Java包，将其解压缩，您将从“ lib”文件夹中获取Spire.Doc.jar文件。将jar文件作为依赖项导入到您的项目中。

方法2：如果要创建Maven项目，则可以通过将以下配置添加到pom.xml来轻松添加jar依赖项。

<span style="color:#f8f8f2"><span style="color:#eff0f9"><code><span style="color:#f9690e"><</span><span style="color:#f8f8f2">repositories</span><span style="color:#f9690e">></span>
        <span style="color:#f9690e"><</span><span style="color:#f8f8f2">repository</span><span style="color:#f9690e">></span>
            <span style="color:#f9690e"><</span><span style="color:#f8f8f2">id</span><span style="color:#f9690e">></span><span style="color:#f8f8f2">com</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">e</span><span style="color:#f9690e">-</span><span style="color:#f8f8f2">iceblue</span><span style="color:#f9690e"></</span><span style="color:#f8f8f2">id</span><span style="color:#f9690e">></span>
            <span style="color:#f9690e"><</span><span style="color:#f8f8f2">name</span><span style="color:#f9690e">></span><span style="color:#f8f8f2">e</span><span style="color:#f9690e">-</span><span style="color:#f8f8f2">iceblue</span><span style="color:#f9690e"></</span><span style="color:#f8f8f2">name</span><span style="color:#f9690e">></span>
            <span style="color:#f9690e"><</span><span style="color:#f8f8f2">url</span><span style="color:#f9690e">></span><span style="color:#f8f8f2">http:</span><span style="color:#808080">//repo.e-iceblue.com/nexus/content/groups/public/</url></span>
        <span style="color:#f9690e"></</span><span style="color:#f8f8f2">repository</span><span style="color:#f9690e">></span>
<span style="color:#f9690e"></</span><span style="color:#f8f8f2">repositories</span><span style="color:#f9690e">></span>
<span style="color:#f9690e"><</span><span style="color:#f8f8f2">dependencies</span><span style="color:#f9690e">></span>
    <span style="color:#f9690e"><</span><span style="color:#f8f8f2">dependency</span><span style="color:#f9690e">></span>
        <span style="color:#f9690e"><</span><span style="color:#f8f8f2">groupId</span><span style="color:#f9690e">></span> <span style="color:#f8f8f2">e</span><span style="color:#f9690e">-</span><span style="color:#f8f8f2">iceblue</span> <span style="color:#f9690e"></</span><span style="color:#f8f8f2">groupId</span><span style="color:#f9690e">></span>
        <span style="color:#f9690e"><</span><span style="color:#f8f8f2">artifactId</span><span style="color:#f9690e">></span><span style="color:#f8f8f2">spire</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">xls</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">free</span><span style="color:#f9690e"></</span><span style="color:#f8f8f2">artifactId</span><span style="color:#f9690e">></span>
        <span style="color:#f9690e"><</span><span style="color:#f8f8f2">version</span><span style="color:#f9690e">></span><span style="color:#dda0dd">2.2</span><span style="color:#f9690e">.</span><span style="color:#dda0dd">0</span><span style="color:#f9690e"></</span><span style="color:#f8f8f2">version</span><span style="color:#f9690e">></span>
    <span style="color:#f9690e"></</span><span style="color:#f8f8f2">dependency</span><span style="color:#f9690e">></span>
<span style="color:#f9690e"></</span><span style="color:#f8f8f2">dependencies</span><span style="color:#f9690e">></span>
</code></span></span>

例子1.删除指定的行和列

<span style="color:#f8f8f2"><span style="color:#eff0f9"><code><span style="color:#f9690e">import</span> <span style="color:#f8f8f2">com.spire.xls.ExcelVersion</span><span style="color:#f9690e">;</span>
<span style="color:#f9690e">import</span> <span style="color:#f8f8f2">com.spire.xls.Workbook</span><span style="color:#f9690e">;</span>
<span style="color:#f9690e">import</span> <span style="color:#f8f8f2">com.spire.xls.Worksheet</span><span style="color:#f9690e">;</span>

<span style="color:#f39c12">public</span> <span style="color:#f39c12">class</span> <span style="color:#7ed07e">DeleteSpecifiedRowsAndColumns</span> <span style="color:#f9690e">{</span>

    <span style="color:#f39c12">public</span> <span style="color:#f39c12">static</span> <span style="color:#f39c12">void</span> <span style="color:#7ed07e">main</span><span style="color:#f9690e">(</span><span style="color:#7ed07e">String</span><span style="color:#f9690e">[]</span> <span style="color:#f8f8f2">args</span><span style="color:#f9690e">)</span> <span style="color:#f9690e">{</span>

        <span style="color:#808080">//Load the sample document</span>
        <span style="color:#7ed07e">Workbook</span> <span style="color:#f8f8f2">wb</span> <span style="color:#f9690e">=</span> <span style="color:#f39c12">new</span> <span style="color:#7ed07e">Workbook</span><span style="color:#f9690e">();</span>
        <span style="color:#f8f8f2">wb</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">loadFromFile</span><span style="color:#f9690e">(</span><span style="color:#f2ca27">"C:\\Users\\Administrator\\Desktop\\test1.xlsx"</span><span style="color:#f9690e">);</span>

        <span style="color:#808080">//Get the first worksheet</span>
        <span style="color:#7ed07e">Worksheet</span> <span style="color:#f8f8f2">sheet</span> <span style="color:#f9690e">=</span> <span style="color:#f8f8f2">wb</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">getWorksheets</span><span style="color:#f9690e">().</span><span style="color:#7ed07e">get</span><span style="color:#f9690e">(</span><span style="color:#dda0dd">0</span><span style="color:#f9690e">);</span>

        <span style="color:#808080">//Delete specific row by index</span>
        <span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">deleteRow</span><span style="color:#f9690e">(</span><span style="color:#dda0dd">4</span><span style="color:#f9690e">);</span>

        <span style="color:#808080">//Delete specific column by index</span>
        <span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">deleteColumn</span><span style="color:#f9690e">(</span><span style="color:#dda0dd">5</span><span style="color:#f9690e">);</span>

        <span style="color:#808080">//Save the file</span>
        <span style="color:#f8f8f2">wb</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">saveToFile</span><span style="color:#f9690e">(</span><span style="color:#f2ca27">"DeleteSpecificRowsAndColumns.xlsx"</span><span style="color:#f9690e">,</span> <span style="color:#7ed07e">ExcelVersion</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">Version2016</span><span style="color:#f9690e">);</span>
    <span style="color:#f9690e">}</span>
<span style="color:#f9690e">}</span>
</code></span></span>

输出量

例子2.删除空白行和列

<span style="color:#f8f8f2"><span style="color:#eff0f9"><code><span style="color:#f9690e">import</span> <span style="color:#f8f8f2">com.spire.xls.ExcelVersion</span><span style="color:#f9690e">;</span>
<span style="color:#f9690e">import</span> <span style="color:#f8f8f2">com.spire.xls.Workbook</span><span style="color:#f9690e">;</span>
<span style="color:#f9690e">import</span> <span style="color:#f8f8f2">com.spire.xls.Worksheet</span><span style="color:#f9690e">;</span>

<span style="color:#f39c12">public</span> <span style="color:#f39c12">class</span> <span style="color:#7ed07e">DeleteBlankRowsAndColumns</span> <span style="color:#f9690e">{</span>

    <span style="color:#f39c12">public</span> <span style="color:#f39c12">static</span> <span style="color:#f39c12">void</span> <span style="color:#7ed07e">main</span><span style="color:#f9690e">(</span><span style="color:#7ed07e">String</span><span style="color:#f9690e">[]</span> <span style="color:#f8f8f2">args</span><span style="color:#f9690e">)</span> <span style="color:#f9690e">{</span>

        <span style="color:#808080">//Load the sample document</span>
        <span style="color:#7ed07e">Workbook</span> <span style="color:#f8f8f2">wb</span> <span style="color:#f9690e">=</span> <span style="color:#f39c12">new</span> <span style="color:#7ed07e">Workbook</span><span style="color:#f9690e">();</span>
        <span style="color:#f8f8f2">wb</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">loadFromFile</span><span style="color:#f9690e">(</span><span style="color:#f2ca27">"C:\\Users\\Administrator\\Desktop\\test2.xlsx"</span><span style="color:#f9690e">);</span>

        <span style="color:#808080">//Get the first worksheet</span>
        <span style="color:#7ed07e">Worksheet</span> <span style="color:#f8f8f2">sheet</span> <span style="color:#f9690e">=</span> <span style="color:#f8f8f2">wb</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">getWorksheets</span><span style="color:#f9690e">().</span><span style="color:#7ed07e">get</span><span style="color:#f9690e">(</span><span style="color:#dda0dd">0</span><span style="color:#f9690e">);</span>

        <span style="color:#808080">//Loop through the rows</span>
        <span style="color:#f39c12">for</span> <span style="color:#f9690e">(</span><span style="color:#f39c12">int</span> <span style="color:#f8f8f2">i</span> <span style="color:#f9690e">=</span> <span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">getLastRow</span><span style="color:#f9690e">();</span> <span style="color:#f8f8f2">i</span> <span style="color:#f9690e">>=</span> <span style="color:#dda0dd">1</span><span style="color:#f9690e">;</span> <span style="color:#f8f8f2">i</span><span style="color:#f9690e">--)</span>
        <span style="color:#f9690e">{</span>
            <span style="color:#808080">//Detect if a row is blank</span>
            <span style="color:#f39c12">if</span> <span style="color:#f9690e">(</span><span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">getRows</span><span style="color:#f9690e">()[</span><span style="color:#f8f8f2">i</span><span style="color:#f9690e">-</span><span style="color:#dda0dd">1</span><span style="color:#f9690e">].</span><span style="color:#7ed07e">isBlank</span><span style="color:#f9690e">())</span>
            <span style="color:#f9690e">{</span>
                <span style="color:#808080">//Remove blank row</span>
                <span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">deleteRow</span><span style="color:#f9690e">(</span><span style="color:#f8f8f2">i</span><span style="color:#f9690e">);</span>
            <span style="color:#f9690e">}</span>
        <span style="color:#f9690e">}</span>

        <span style="color:#808080">//Loop through the columns</span>
        <span style="color:#f39c12">for</span> <span style="color:#f9690e">(</span><span style="color:#f39c12">int</span> <span style="color:#f8f8f2">j</span> <span style="color:#f9690e">=</span> <span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">getLastColumn</span><span style="color:#f9690e">();</span> <span style="color:#f8f8f2">j</span> <span style="color:#f9690e">>=</span> <span style="color:#dda0dd">1</span><span style="color:#f9690e">;</span> <span style="color:#f8f8f2">j</span><span style="color:#f9690e">--)</span>
        <span style="color:#f9690e">{</span>
            <span style="color:#808080">//Detect if a column is blank</span>
            <span style="color:#f39c12">if</span> <span style="color:#f9690e">(</span><span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">getColumns</span><span style="color:#f9690e">()[</span><span style="color:#f8f8f2">j</span><span style="color:#f9690e">-</span><span style="color:#dda0dd">1</span><span style="color:#f9690e">].</span><span style="color:#7ed07e">isBlank</span><span style="color:#f9690e">())</span>
            <span style="color:#f9690e">{</span>
                <span style="color:#808080">//Remove blank column</span>
                <span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">deleteColumn</span><span style="color:#f9690e">(</span><span style="color:#f8f8f2">j</span><span style="color:#f9690e">);</span>
            <span style="color:#f9690e">}</span>
        <span style="color:#f9690e">}</span>

        <span style="color:#808080">//Save the file</span>
        <span style="color:#f8f8f2">wb</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">saveToFile</span><span style="color:#f9690e">(</span><span style="color:#f2ca27">"DeleteBlankRowsAndColumns.xlsx"</span><span style="color:#f9690e">,</span> <span style="color:#7ed07e">ExcelVersion</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">Version2016</span><span style="color:#f9690e">);</span>
    <span style="color:#f9690e">}</span>
<span style="color:#f9690e">}</span>
</code></span></span>

输出量

例子3.删除包含关键字的行

<span style="color:#f8f8f2"><span style="color:#eff0f9"><code><span style="color:#f9690e">import</span> <span style="color:#f8f8f2">com.spire.xls.*</span><span style="color:#f9690e">;</span>
<span style="color:#f9690e">import</span> <span style="color:#f8f8f2">java.util.ArrayList</span><span style="color:#f9690e">;</span>
<span style="color:#f9690e">import</span> <span style="color:#f8f8f2">java.util.List</span><span style="color:#f9690e">;</span>

<span style="color:#f39c12">public</span> <span style="color:#f39c12">class</span> <span style="color:#7ed07e">DeleteRowsByKeyword</span> <span style="color:#f9690e">{</span>

    <span style="color:#f39c12">public</span> <span style="color:#f39c12">static</span> <span style="color:#f39c12">void</span> <span style="color:#7ed07e">main</span><span style="color:#f9690e">(</span><span style="color:#7ed07e">String</span><span style="color:#f9690e">[]</span> <span style="color:#f8f8f2">args</span><span style="color:#f9690e">)</span> <span style="color:#f9690e">{</span>

        <span style="color:#808080">//Load the sample document</span>
        <span style="color:#7ed07e">Workbook</span> <span style="color:#f8f8f2">wb</span> <span style="color:#f9690e">=</span> <span style="color:#f39c12">new</span> <span style="color:#7ed07e">Workbook</span><span style="color:#f9690e">();</span>
        <span style="color:#f8f8f2">wb</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">loadFromFile</span><span style="color:#f9690e">(</span><span style="color:#f2ca27">"C:\\Users\\Administrator\\Desktop\\test3.xlsx"</span><span style="color:#f9690e">);</span>

        <span style="color:#808080">//Get the first worksheet</span>
        <span style="color:#7ed07e">Worksheet</span> <span style="color:#f8f8f2">sheet</span> <span style="color:#f9690e">=</span> <span style="color:#f8f8f2">wb</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">getWorksheets</span><span style="color:#f9690e">().</span><span style="color:#7ed07e">get</span><span style="color:#f9690e">(</span><span style="color:#dda0dd">0</span><span style="color:#f9690e">);</span>

        <span style="color:#808080">//Call getRowList method to get the rows containing the keyword</span>
        <span style="color:#7ed07e">List</span><span style="color:#f9690e"><</span><span style="color:#7ed07e">Integer</span><span style="color:#f9690e">></span> <span style="color:#f8f8f2">list</span> <span style="color:#f9690e">=</span> <span style="color:#f8f8f2">getRowList</span><span style="color:#f9690e">(</span><span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">,</span><span style="color:#f2ca27">"Hello World"</span><span style="color:#f9690e">);</span>

        <span style="color:#808080">//Loop through the list</span>
        <span style="color:#f39c12">for</span> <span style="color:#f9690e">(</span><span style="color:#f39c12">int</span> <span style="color:#f8f8f2">i</span> <span style="color:#f9690e">=</span> <span style="color:#dda0dd">0</span><span style="color:#f9690e">;</span> <span style="color:#f8f8f2">i</span> <span style="color:#f9690e"><</span> <span style="color:#f8f8f2">list</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">size</span><span style="color:#f9690e">();</span> <span style="color:#f8f8f2">i</span><span style="color:#f9690e">++)</span> <span style="color:#f9690e">{</span>

            <span style="color:#808080">//Delete the specified row</span>
            <span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">deleteRow</span><span style="color:#f9690e">(</span><span style="color:#f8f8f2">list</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">get</span><span style="color:#f9690e">(</span><span style="color:#f8f8f2">i</span><span style="color:#f9690e">));</span>
        <span style="color:#f9690e">}</span>

        <span style="color:#808080">//Save the file</span>
        <span style="color:#f8f8f2">wb</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">saveToFile</span><span style="color:#f9690e">(</span><span style="color:#f2ca27">"DeleteRowByKeywords.xlsx"</span><span style="color:#f9690e">,</span><span style="color:#7ed07e">ExcelVersion</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">Version2016</span><span style="color:#f9690e">);</span>
    <span style="color:#f9690e">}</span>

    <span style="color:#808080">//Create getRowList method to get the numbers of the rows containing keyword</span>
    <span style="color:#f39c12">static</span> <span style="color:#7ed07e">List</span><span style="color:#f9690e"><</span><span style="color:#7ed07e">Integer</span><span style="color:#f9690e">></span> <span style="color:#7ed07e">getRowList</span><span style="color:#f9690e">(</span><span style="color:#7ed07e">Worksheet</span> <span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">,</span> <span style="color:#7ed07e">String</span> <span style="color:#f8f8f2">keyword</span><span style="color:#f9690e">)</span> <span style="color:#f9690e">{</span>

        <span style="color:#f39c12">int</span> <span style="color:#f8f8f2">rowNum</span><span style="color:#f9690e">;</span>
        <span style="color:#7ed07e">List</span><span style="color:#f9690e"><</span><span style="color:#7ed07e">Integer</span><span style="color:#f9690e">></span> <span style="color:#f8f8f2">rowList</span> <span style="color:#f9690e">=</span> <span style="color:#f39c12">new</span> <span style="color:#7ed07e">ArrayList</span><span style="color:#f9690e"><>();</span>
        <span style="color:#f39c12">for</span> <span style="color:#f9690e">(</span><span style="color:#f39c12">int</span> <span style="color:#f8f8f2">i</span> <span style="color:#f9690e">=</span> <span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">getLastRow</span><span style="color:#f9690e">();</span> <span style="color:#f8f8f2">i</span> <span style="color:#f9690e">>=</span> <span style="color:#dda0dd">1</span><span style="color:#f9690e">;</span> <span style="color:#f8f8f2">i</span><span style="color:#f9690e">--)</span> <span style="color:#f9690e">{</span>
            <span style="color:#f8f8f2">rowNum</span> <span style="color:#f9690e">=</span> <span style="color:#f8f8f2">i</span><span style="color:#f9690e">;</span>
            <span style="color:#f39c12">for</span> <span style="color:#f9690e">(</span><span style="color:#f39c12">int</span> <span style="color:#f8f8f2">j</span> <span style="color:#f9690e">=</span> <span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">getLastColumn</span><span style="color:#f9690e">();</span> <span style="color:#f8f8f2">j</span> <span style="color:#f9690e">>=</span> <span style="color:#dda0dd">1</span><span style="color:#f9690e">;</span> <span style="color:#f8f8f2">j</span><span style="color:#f9690e">--)</span> <span style="color:#f9690e">{</span>

                <span style="color:#f39c12">if</span> <span style="color:#f9690e">(</span><span style="color:#f8f8f2">sheet</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">get</span><span style="color:#f9690e">(</span><span style="color:#f8f8f2">i</span><span style="color:#f9690e">,</span> <span style="color:#f8f8f2">j</span><span style="color:#f9690e">).</span><span style="color:#7ed07e">getText</span><span style="color:#f9690e">().</span><span style="color:#7ed07e">contains</span><span style="color:#f9690e">(</span><span style="color:#f8f8f2">keyword</span><span style="color:#f9690e">))</span> <span style="color:#f9690e">{</span>

                    <span style="color:#f8f8f2">rowList</span><span style="color:#f9690e">.</span><span style="color:#7ed07e">add</span><span style="color:#f9690e">(</span><span style="color:#f8f8f2">rowNum</span><span style="color:#f9690e">);</span>
                    <span style="color:#f39c12">break</span><span style="color:#f9690e">;</span>
                <span style="color:#f9690e">}</span>
            <span style="color:#f9690e">}</span>
        <span style="color:#f9690e">}</span>
        <span style="color:#f39c12">return</span> <span style="color:#f8f8f2">rowList</span><span style="color:#f9690e">;</span>
    <span style="color:#f9690e">}</span>
<span style="color:#f9690e">}</span>
</code></span></span>

输出量