<!DOCTYPE html>
C#处理Excel
Excel
C#
NPOI
OleDb
前言
最近需要对Excel进行加密解密操作,本身是一个简单的事情,通过 OleDbConnection
可以很容易进行操作Excel,或者也可以用第三方dll如NPOI进行操作。
OleDb
用OleDb方法几乎和SqlConnection
一模一样,基本上把前缀Sql
改成OleDb
即可,不过有几个小细节需要注意。
- 我们可以把一个Excel当成一个数据源
- 每个sheet可以看成一张表
- 需要注意的是你在Excel看到的sheet名称后面需要加上\(</strong>才是真正的sheet名字。</p><div class="md-section-divider"></div><h3 data-anchor-id="95eh" id="具体操作">具体操作</h3><p data-anchor-id="bgqn">我在此简单封装了下,取名为OleDbHelper,代码如下:</p><div class="md-section-divider"></div><pre class="prettyprint linenums prettyprinted" data-anchor-id="fqj1"><ol class="linenums"><li class="L0"><code><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">;</span></code></li><li class="L1"><code><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Collections</span><span class="pun">.</span><span class="typ">Generic</span><span class="pun">;</span></code></li><li class="L2"><code><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Data</span><span class="pun">;</span></code></li><li class="L3"><code><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Data</span><span class="pun">.</span><span class="typ">OleDb</span><span class="pun">;</span></code></li><li class="L4"><code></code></li><li class="L5"><code><span class="kwd">namespace</span><span class="pln"> </span><span class="typ">ExcelDataEncipher</span></code></li><li class="L6"><code><span class="pun">{</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">class</span><span class="pln"> </span><span class="typ">OleDbHelper</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">private</span><span class="pln"> </span><span class="kwd">readonly</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> _connstr</span><span class="pun">;</span></code></li><li class="L0"><code><span class="pln"> </span><span class="com">//当前Sheet集合</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="typ">IEnumerable</span><span class="str"><string></span><span class="pln"> </span><span class="typ">SheetNames</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> </span><span class="kwd">get</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">set</span><span class="pun">;</span><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">//通过在外部传入数据源的名称来连接</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="typ">OleDbHelper</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> name</span><span class="pun">)</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> _connstr </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">string</span><span class="pun">.</span><span class="typ">Format</span><span class="pun">(</span><span class="str">"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;ReadOnly=False;HDR=Yes;'"</span><span class="pun">,</span><span class="pln"> name</span><span class="pun">);</span></code></li><li class="L6"><code><span class="pln"> </span><span class="com">//这个连接串可以连接2003的,高版本Excel的连接串可能有所变化,由于我需要进行加密解密操作需要修改excel,所以ReadOnly设置为false </span></code></li><li class="L7"><code><span class="pln"> </span><span class="com">//同时HDR设置为Yes代表第一行是标题,不做为数据使用 </span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">this</span><span class="pun">.</span><span class="typ">GetSheetName</span><span class="pun">();</span><span class="com">//获取所有Sheet名称</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">private</span><span class="pln"> </span><span class="kwd">void</span><span class="pln"> </span><span class="typ">GetSheetName</span><span class="pun">()</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">OleDbConnection</span><span class="pln"> conn </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">OleDbConnection</span><span class="pun">(</span><span class="pln">_connstr</span><span class="pun">))</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> conn</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">();</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">DataTable</span><span class="pln"> dtSheetName </span><span class="pun">=</span><span class="pln"> conn</span><span class="pun">.</span><span class="typ">GetOleDbSchemaTable</span><span class="pun">(</span><span class="typ">OleDbSchemaGuid</span><span class="pun">.</span><span class="typ">Tables</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="kwd">object</span><span class="pun">[]</span><span class="pln"> </span><span class="pun">{</span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="str">"Table"</span><span class="pun">}))</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L8"><code><span class="pln"> </span><span class="com">//包含excel中表名的字符串数组 </span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">dtSheetName </span><span class="pun">!=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="typ">List</span><span class="str"><string></span><span class="pln"> strTableNames </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">List</span><span class="str"><string></span><span class="pun">();</span></code></li><li class="L2"><code></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> k </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> k </span><span class="pun"><</span><span class="pln"> dtSheetName</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">;</span><span class="pln"> k</span><span class="pun">++)</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> strTableNames</span><span class="pun">.</span><span class="typ">Add</span><span class="pun">(</span><span class="pln">dtSheetName</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">[</span><span class="pln">k</span><span class="pun">][</span><span class="str">"TABLE_NAME"</span><span class="pun">].</span><span class="typ">ToString</span><span class="pun">());</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span><span class="com">//获取Sheet名称</span></code></li><li class="L7"><code><span class="pln"> </span><span class="typ">SheetNames</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> strTableNames</span><span class="pun">;</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">ExecuteNonQuery</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> oleDb</span><span class="pun">,</span><span class="pln"> </span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">params</span><span class="pln"> </span><span class="typ">OleDbParameter</span><span class="pun">[]</span><span class="pln"> parameters</span><span class="pun">)</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">OleDbConnection</span><span class="pln"> conn </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">OleDbConnection</span><span class="pun">(</span><span class="pln">_connstr</span><span class="pun">))</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L8"><code><span class="pln"> conn</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">();</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">OleDbCommand</span><span class="pln"> cmd </span><span class="pun">=</span><span class="pln"> conn</span><span class="pun">.</span><span class="typ">CreateCommand</span><span class="pun">())</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> cmd</span><span class="pun">.</span><span class="typ">CommandText</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> oleDb</span><span class="pun">;</span></code></li><li class="L2"><code><span class="pln"> cmd</span><span class="pun">.</span><span class="typ">Parameters</span><span class="pun">.</span><span class="typ">AddRange</span><span class="pun">(</span><span class="pln">parameters</span><span class="pun">);</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> cmd</span><span class="pun">.</span><span class="typ">ExecuteNonQuery</span><span class="pun">();</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">object</span><span class="pln"> </span><span class="typ">ExecuteScalar</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> oleDb</span><span class="pun">,</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">params</span><span class="pln"> </span><span class="typ">OleDbParameter</span><span class="pun">[]</span><span class="pln"> parameters</span><span class="pun">)</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">OleDbConnection</span><span class="pln"> conn </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">OleDbConnection</span><span class="pun">(</span><span class="pln">_connstr</span><span class="pun">))</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L3"><code><span class="pln"> conn</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">();</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">OleDbCommand</span><span class="pln"> cmd </span><span class="pun">=</span><span class="pln"> conn</span><span class="pun">.</span><span class="typ">CreateCommand</span><span class="pun">())</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L6"><code><span class="pln"> cmd</span><span class="pun">.</span><span class="typ">CommandText</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> oleDb</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> cmd</span><span class="pun">.</span><span class="typ">Parameters</span><span class="pun">.</span><span class="typ">AddRange</span><span class="pun">(</span><span class="pln">parameters</span><span class="pun">);</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> cmd</span><span class="pun">.</span><span class="typ">ExecuteScalar</span><span class="pun">();</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> </span><span class="typ">ExecuteDataTable</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> oleDb</span><span class="pun">,</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">params</span><span class="pln"> </span><span class="typ">OleDbParameter</span><span class="pun">[]</span><span class="pln"> parameters</span><span class="pun">)</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">OleDbConnection</span><span class="pln"> conn </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">OleDbConnection</span><span class="pun">(</span><span class="pln">_connstr</span><span class="pun">))</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L8"><code><span class="pln"> conn</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">();</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">OleDbCommand</span><span class="pln"> cmd </span><span class="pun">=</span><span class="pln"> conn</span><span class="pun">.</span><span class="typ">CreateCommand</span><span class="pun">())</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> cmd</span><span class="pun">.</span><span class="typ">CommandText</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> oleDb</span><span class="pun">;</span></code></li><li class="L2"><code><span class="pln"> cmd</span><span class="pun">.</span><span class="typ">Parameters</span><span class="pun">.</span><span class="typ">AddRange</span><span class="pun">(</span><span class="pln">parameters</span><span class="pun">);</span></code></li><li class="L3"><code></code></li><li class="L4"><code><span class="pln"> </span><span class="typ">DataSet</span><span class="pln"> dataset </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">DataSet</span><span class="pun">();</span></code></li><li class="L5"><code><span class="pln"> </span><span class="typ">OleDbDataAdapter</span><span class="pln"> adapter </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">OleDbDataAdapter</span><span class="pun">(</span><span class="pln">cmd</span><span class="pun">);</span></code></li><li class="L6"><code><span class="pln"> adapter</span><span class="pun">.</span><span class="typ">Fill</span><span class="pun">(</span><span class="pln">dataset</span><span class="pun">);</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> dataset</span><span class="pun">.</span><span class="typ">Tables</span><span class="pun">[</span><span class="lit">0</span><span class="pun">];</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L1"><code></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">object</span><span class="pln"> </span><span class="typ">FromDbValue</span><span class="pun">(</span><span class="kwd">object</span><span class="pln"> value</span><span class="pun">)</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">value </span><span class="pun">==</span><span class="pln"> </span><span class="typ">DBNull</span><span class="pun">.</span><span class="typ">Value</span><span class="pun">)</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">else</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> value</span><span class="pun">;</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L3"><code></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">object</span><span class="pln"> </span><span class="typ">ToDbValue</span><span class="pun">(</span><span class="kwd">object</span><span class="pln"> value</span><span class="pun">)</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">value </span><span class="pun">==</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> </span><span class="typ">DBNull</span><span class="pun">.</span><span class="typ">Value</span><span class="pun">;</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">else</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> value</span><span class="pun">;</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code><span class="pun">}</span></code></li></ol></pre><blockquote data-anchor-id="301t" class="white-blockquote"> <p>可以发现操作和对数据库操作一模一样</p> </blockquote><p data-anchor-id="xlo4">我通过OpenFileDialog选择需要操作的文件</p><div class="md-section-divider"></div><pre class="prettyprint linenums prettyprinted" data-anchor-id="gqcf"><ol class="linenums"><li class="L0"><code><span class="kwd">foreach</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">var</span><span class="pln"> filename </span><span class="kwd">in</span><span class="pln"> openFile</span><span class="pun">.</span><span class="typ">FileNames</span><span class="pun">)</span></code></li><li class="L1"><code><span class="pun">{</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">string</span><span class="pln"> name </span><span class="pun">=</span><span class="pln"> filename</span><span class="pun">;</span></code></li><li class="L3"><code><span class="pln"> _oleDbHelper </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">OleDbHelper</span><span class="pun">(</span><span class="pln">name</span><span class="pun">);</span><span class="pln"> </span><span class="com">//获取表</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">string</span><span class="pln"> sheetName </span><span class="pun">=</span><span class="pln"> _oleDbHelper</span><span class="pun">.</span><span class="typ">SheetNames</span><span class="pun">.</span><span class="typ">SingleOrDefault</span><span class="pun">(</span><span class="pln">s </span><span class="pun">=></span><span class="pln"> s</span><span class="pun">.</span><span class="typ">Contains</span><span class="pun">(</span><span class="str">"Detail"</span><span class="pun">));</span><span class="com">//查看名称是否包含Detail的sheet</span></code></li><li class="L5"><code><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> table </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L8"><code><span class="pln"> table </span><span class="pun">=</span><span class="pln"> _oleDbHelper</span><span class="pun">.</span><span class="typ">ExecuteDataTable</span><span class="pun">(</span><span class="kwd">string</span><span class="pun">.</span><span class="typ">Format</span><span class="pun">(</span><span class="str">"select * from [{0}]"</span><span class="pun">,</span><span class="pln"> sheetName</span><span class="pun">));</span><span class="com">//将其转换为datatable</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> msg</span><span class="pun">)</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">throw</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Exception</span><span class="pun">(</span><span class="pln">msg</span><span class="pun">.</span><span class="typ">Message</span><span class="pun">);</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L4"><code></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">table</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">.</span><span class="typ">Count</span><span class="pln"> </span><span class="pun"><</span><span class="pln"> </span><span class="lit">0</span><span class="pun">)</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L7"><code><span class="pln"> </span><span class="typ">MessageBox</span><span class="pun">.</span><span class="typ">Show</span><span class="pun">(</span><span class="str">"未找到任何记录"</span><span class="pun">);</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">return</span><span class="pun">;</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> i </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> i </span><span class="pun"><</span><span class="pln"> table</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">;</span><span class="pln"> i</span><span class="pun">++)</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">...</span><span class="com">//处理数据</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">...</span><span class="com">//处理数据</span></code></li><li class="L6"><code><span class="pln"> _oleDbHelper</span><span class="pun">.</span><span class="typ">ExecuteNonQuery</span><span class="pun">(</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">string</span><span class="pun">.</span><span class="typ">Format</span><span class="pun">(</span></code></li><li class="L8"><code><span class="pln"> </span><span class="str">"update [{0}] set [AccountNumber] = '{1}',[AccountName] = '{2}',[Price] = '{3}' where [Id] = '{4}'"</span><span class="pun">,</span></code></li><li class="L9"><code><span class="pln"> sheetName</span><span class="pun">,</span><span class="pln"> table</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">[</span><span class="pln">i</span><span class="pun">][</span><span class="str">"AccountNumber"</span><span class="pun">],</span><span class="pln"> table</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">[</span><span class="pln">i</span><span class="pun">][</span><span class="str">"AccountName"</span><span class="pun">],</span><span class="pln"> table</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">[</span><span class="pln">i</span><span class="pun">][</span><span class="str">"Price"</span><span class="pun">],</span></code></li><li class="L0"><code><span class="pln"> table</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">[</span><span class="pln">i</span><span class="pun">][</span><span class="str">"Id"</span><span class="pun">]));</span></code></li><li class="L1"><code><span class="pln"> </span><span class="com">//更新excel</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> msg</span><span class="pun">)</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">throw</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Exception</span><span class="pun">(</span><span class="pln">msg</span><span class="pun">.</span><span class="typ">Message</span><span class="pun">);</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L8"><code><span class="pun">}</span></code></li></ol></pre><blockquote data-anchor-id="u6vg" class="white-blockquote"> <p>本来通过参数化的形式进行更新,但是一直报错,不知道什么情况 <br> 通过OleDb修改excel速度非常的慢,我的excel表 5行28列需要修改其中3列的数据需要1秒多,不知道是不是因为更新一个表格他会刷新整张excel还是为什么</p> </blockquote><p data-anchor-id="n997"><img src="http://d.pcs.baidu.com/thumbnail/016bfa1968119f511634dd1b6101489d?fid=2399346640-250528-393839917103481&time=1438336800&sign=FDTAER-DCb740ccc5511e5e8fedcff06b081203-L%2FdAbK2V20zjrPzGXQFRoaG3qlY%3D&rt=sh&expires=2h&r=616089685&sharesign=unknown&size=c710_u500&quality=100" alt="OleDb" title=""></p><div class="md-section-divider"></div><h2 data-anchor-id="h69p" id="npoi">NPOI</h2><p data-anchor-id="qsk7">因为速度实在太慢,我不得不寻找其他的解决方法 <br> <code>NPOI</code>是一个开源的读写Excel、Word等项目,他的优点是不需要在机子上安装office也可以进行操作,而且读写速度还是非常快的。</p><div class="md-section-divider"></div><h3 data-anchor-id="376o" id="具体操作-1">具体操作</h3><p data-anchor-id="uax4">这里我在网上下载了别人的封装好的一个帮助类,略做了修改来适配我的项目</p><div class="md-section-divider"></div><pre class="prettyprint linenums prettyprinted" data-anchor-id="q8dn"><ol class="linenums"><li class="L0"><code><span class="com">/*******************************************************************</span></code></li><li class="L1"><code><span class="com"> * 版权所有: </span></code></li><li class="L2"><code><span class="com"> * 类 名 称:ExcelHelper</span></code></li><li class="L3"><code><span class="com"> * 作 者:zk</span></code></li><li class="L4"><code><span class="com"> * 电子邮箱: </span></code></li><li class="L5"><code><span class="com"> * 创建日期:2012/2/25 10:17:21 </span></code></li><li class="L6"><code><span class="com"> * 修改描述:从excel导入datatable时,可以导入日期类型。</span></code></li><li class="L7"><code><span class="com"> * 但对excel中的日期类型有一定要求,要求至少是yyyy/mm/dd类型日期; * </span></code></li><li class="L8"><code><span class="com"> * 修改描述:将datatable导入excel中,对类型为字符串的数字进行处理,</span></code></li><li class="L9"><code><span class="com"> * 导出数字为double类型;</span></code></li><li class="L0"><code><span class="com"> * </span></code></li><li class="L1"><code><span class="com"> * </span></code></li><li class="L2"><code><span class="com"> * *******************************************************************/</span></code></li><li class="L3"><code></code></li><li class="L4"><code><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">;</span></code></li><li class="L5"><code><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Collections</span><span class="pun">;</span></code></li><li class="L6"><code><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Collections</span><span class="pun">.</span><span class="typ">Generic</span><span class="pun">;</span></code></li><li class="L7"><code><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Data</span><span class="pun">;</span></code></li><li class="L8"><code><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="pln">IO</span><span class="pun">;</span></code></li><li class="L9"><code><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Text</span><span class="pun">;</span></code></li><li class="L0"><code><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Text</span><span class="pun">.</span><span class="typ">RegularExpressions</span><span class="pun">;</span></code></li><li class="L1"><code><span class="kwd">using</span><span class="pln"> NPOI</span><span class="pun">.</span><span class="pln">HSSF</span><span class="pun">.</span><span class="typ">UserModel</span><span class="pun">;</span></code></li><li class="L2"><code><span class="kwd">using</span><span class="pln"> NPOI</span><span class="pun">.</span><span class="pln">SS</span><span class="pun">.</span><span class="typ">Formula</span><span class="pun">.</span><span class="typ">Eval</span><span class="pun">;</span></code></li><li class="L3"><code><span class="kwd">using</span><span class="pln"> NPOI</span><span class="pun">.</span><span class="pln">SS</span><span class="pun">.</span><span class="typ">UserModel</span><span class="pun">;</span></code></li><li class="L4"><code><span class="kwd">using</span><span class="pln"> NPOI</span><span class="pun">.</span><span class="pln">SS</span><span class="pun">.</span><span class="typ">Util</span><span class="pun">;</span></code></li><li class="L5"><code></code></li><li class="L6"><code><span class="kwd">namespace</span><span class="pln"> </span><span class="typ">ExcelDataEncipher</span></code></li><li class="L7"><code><span class="pun">{</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">class</span><span class="pln"> </span><span class="typ">ExcelHelper</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L0"><code><span class="pln"> </span><span class="com">//private static WriteLog wl = new WriteLog();a</span></code></li><li class="L1"><code><span class="pln"> </span><span class="com">#region 从datatable中将数据导出到excel</span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">/// <summary></span></code></li><li class="L3"><code><span class="pln"> </span><span class="com">/// DataTable导出到Excel的MemoryStream</span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">/// </summary></span></code></li><li class="L5"><code><span class="pln"> </span><span class="com">/// <param name="dtSource">源DataTable</param></span></code></li><li class="L6"><code><span class="pln"> </span><span class="com">/// <param name="strHeaderText">表头文本</param></span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="typ">MemoryStream</span><span class="pln"> </span><span class="typ">ExportDT</span><span class="pun">(</span><span class="typ">DataTable</span><span class="pln"> dtSource</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> strHeaderText</span><span class="pun">)</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pln"> workbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pun">();</span></code></li><li class="L0"><code><span class="pln"> </span><span class="typ">HSSFSheet</span><span class="pln"> sheet </span><span class="pun">=</span><span class="pln"> workbook</span><span class="pun">.</span><span class="typ">CreateSheet</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFSheet</span><span class="pun">;</span></code></li><li class="L1"><code></code></li><li class="L2"><code><span class="pln"> </span><span class="com">#region 右击文件 属性信息</span></code></li><li class="L3"><code></code></li><li class="L4"><code><span class="pln"> </span><span class="com">//{</span></code></li><li class="L5"><code><span class="pln"> </span><span class="com">// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();</span></code></li><li class="L6"><code><span class="pln"> </span><span class="com">// dsi.Company = "http://www.yongfa365.com/";</span></code></li><li class="L7"><code><span class="pln"> </span><span class="com">// workbook.DocumentSummaryInformation = dsi;</span></code></li><li class="L8"><code></code></li><li class="L9"><code><span class="pln"> </span><span class="com">// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();</span></code></li><li class="L0"><code><span class="pln"> </span><span class="com">// si.Author = "柳永法"; //填加xls文件作者信息</span></code></li><li class="L1"><code><span class="pln"> </span><span class="com">// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息</span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息</span></code></li><li class="L3"><code><span class="pln"> </span><span class="com">// si.Comments = "说明信息"; //填加xls文件作者信息</span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">// si.Title = "NPOI测试"; //填加xls文件标题信息</span></code></li><li class="L5"><code><span class="pln"> </span><span class="com">// si.Subject = "NPOI测试Demo"; //填加文件主题信息</span></code></li><li class="L6"><code><span class="pln"> </span><span class="com">// si.CreateDateTime = DateTime.Now;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="com">// workbook.SummaryInformation = si;</span></code></li><li class="L8"><code><span class="pln"> </span><span class="com">//}</span></code></li><li class="L9"><code></code></li><li class="L0"><code><span class="pln"> </span><span class="com">#endregion</span></code></li><li class="L1"><code></code></li><li class="L2"><code><span class="pln"> </span><span class="typ">HSSFCellStyle</span><span class="pln"> dateStyle </span><span class="pun">=</span><span class="pln"> workbook</span><span class="pun">.</span><span class="typ">CreateCellStyle</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFCellStyle</span><span class="pun">;</span></code></li><li class="L3"><code><span class="pln"> </span><span class="typ">HSSFDataFormat</span><span class="pln"> format </span><span class="pun">=</span><span class="pln"> workbook</span><span class="pun">.</span><span class="typ">CreateDataFormat</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFDataFormat</span><span class="pun">;</span></code></li><li class="L4"><code><span class="pln"> dateStyle</span><span class="pun">.</span><span class="typ">DataFormat</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> format</span><span class="pun">.</span><span class="typ">GetFormat</span><span class="pun">(</span><span class="str">"yyyy-mm-dd"</span><span class="pun">);</span></code></li><li class="L5"><code></code></li><li class="L6"><code><span class="pln"> </span><span class="com">//取得列宽</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">int</span><span class="pun">[]</span><span class="pln"> arrColWidth </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">[</span><span class="pln">dtSource</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">];</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">foreach</span><span class="pln"> </span><span class="pun">(</span><span class="typ">DataColumn</span><span class="pln"> item </span><span class="kwd">in</span><span class="pln"> dtSource</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">)</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L0"><code><span class="pln"> arrColWidth</span><span class="pun">[</span><span class="pln">item</span><span class="pun">.</span><span class="typ">Ordinal</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Encoding</span><span class="pun">.</span><span class="typ">GetEncoding</span><span class="pun">(</span><span class="lit">936</span><span class="pun">).</span><span class="typ">GetBytes</span><span class="pun">(</span><span class="pln">item</span><span class="pun">.</span><span class="typ">ColumnName</span><span class="pun">.</span><span class="typ">ToString</span><span class="pun">()).</span><span class="typ">Length</span><span class="pun">;</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> i </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> i </span><span class="pun"><</span><span class="pln"> dtSource</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">;</span><span class="pln"> i</span><span class="pun">++)</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> j </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> j </span><span class="pun"><</span><span class="pln"> dtSource</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">;</span><span class="pln"> j</span><span class="pun">++)</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">int</span><span class="pln"> intTemp </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Encoding</span><span class="pun">.</span><span class="typ">GetEncoding</span><span class="pun">(</span><span class="lit">936</span><span class="pun">).</span><span class="typ">GetBytes</span><span class="pun">(</span><span class="pln">dtSource</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">[</span><span class="pln">i</span><span class="pun">][</span><span class="pln">j</span><span class="pun">].</span><span class="typ">ToString</span><span class="pun">()).</span><span class="typ">Length</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">intTemp </span><span class="pun">></span><span class="pln"> arrColWidth</span><span class="pun">[</span><span class="pln">j</span><span class="pun">])</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> arrColWidth</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> intTemp</span><span class="pun">;</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">int</span><span class="pln"> rowIndex </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span></code></li><li class="L4"><code></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">foreach</span><span class="pln"> </span><span class="pun">(</span><span class="typ">DataRow</span><span class="pln"> row </span><span class="kwd">in</span><span class="pln"> dtSource</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">)</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L7"><code><span class="pln"> </span><span class="com">#region 新建表,填充表头,填充列头,样式</span></code></li><li class="L8"><code></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">rowIndex </span><span class="pun">==</span><span class="pln"> </span><span class="lit">65535</span><span class="pln"> </span><span class="pun">||</span><span class="pln"> rowIndex </span><span class="pun">==</span><span class="pln"> </span><span class="lit">0</span><span class="pun">)</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">rowIndex </span><span class="pun">!=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">)</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L3"><code><span class="pln"> sheet </span><span class="pun">=</span><span class="pln"> workbook</span><span class="pun">.</span><span class="typ">CreateSheet</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFSheet</span><span class="pun">;</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code></code></li><li class="L6"><code><span class="pln"> </span><span class="com">#region 表头及样式</span></code></li><li class="L7"><code></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="typ">HSSFRow</span><span class="pln"> headerRow </span><span class="pun">=</span><span class="pln"> sheet</span><span class="pun">.</span><span class="typ">CreateRow</span><span class="pun">(</span><span class="lit">0</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFRow</span><span class="pun">;</span></code></li><li class="L0"><code><span class="pln"> headerRow</span><span class="pun">.</span><span class="typ">HeightInPoints</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">25</span><span class="pun">;</span></code></li><li class="L1"><code><span class="pln"> headerRow</span><span class="pun">.</span><span class="typ">CreateCell</span><span class="pun">(</span><span class="lit">0</span><span class="pun">).</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="pln">strHeaderText</span><span class="pun">);</span></code></li><li class="L2"><code></code></li><li class="L3"><code><span class="pln"> </span><span class="typ">HSSFCellStyle</span><span class="pln"> headStyle </span><span class="pun">=</span><span class="pln"> workbook</span><span class="pun">.</span><span class="typ">CreateCellStyle</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFCellStyle</span><span class="pun">;</span></code></li><li class="L4"><code><span class="pln"> headStyle</span><span class="pun">.</span><span class="typ">Alignment</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> NPOI</span><span class="pun">.</span><span class="pln">SS</span><span class="pun">.</span><span class="typ">UserModel</span><span class="pun">.</span><span class="typ">HorizontalAlignment</span><span class="pun">.</span><span class="typ">Center</span><span class="pun">;</span></code></li><li class="L5"><code><span class="pln"> </span><span class="typ">HSSFFont</span><span class="pln"> font </span><span class="pun">=</span><span class="pln"> workbook</span><span class="pun">.</span><span class="typ">CreateFont</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFFont</span><span class="pun">;</span></code></li><li class="L6"><code><span class="pln"> font</span><span class="pun">.</span><span class="typ">FontHeightInPoints</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">20</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> font</span><span class="pun">.</span><span class="typ">Boldweight</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">700</span><span class="pun">;</span></code></li><li class="L8"><code><span class="pln"> headStyle</span><span class="pun">.</span><span class="typ">SetFont</span><span class="pun">(</span><span class="pln">font</span><span class="pun">);</span></code></li><li class="L9"><code></code></li><li class="L0"><code><span class="pln"> headerRow</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="lit">0</span><span class="pun">).</span><span class="typ">CellStyle</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> headStyle</span><span class="pun">;</span></code></li><li class="L1"><code></code></li><li class="L2"><code><span class="pln"> sheet</span><span class="pun">.</span><span class="typ">AddMergedRegion</span><span class="pun">(</span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Region</span><span class="pun">(</span><span class="lit">0</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pun">,</span><span class="pln"> dtSource</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">.</span><span class="typ">Count</span><span class="pln"> </span><span class="pun">-</span><span class="pln"> </span><span class="lit">1</span><span class="pun">));</span></code></li><li class="L3"><code><span class="pln"> </span><span class="com">//headerRow.Dispose();</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code></code></li><li class="L6"><code><span class="pln"> </span><span class="com">#endregion</span></code></li><li class="L7"><code></code></li><li class="L8"><code></code></li><li class="L9"><code><span class="pln"> </span><span class="com">#region 列头及样式</span></code></li><li class="L0"><code></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L2"><code><span class="pln"> </span><span class="typ">HSSFRow</span><span class="pln"> headerRow </span><span class="pun">=</span><span class="pln"> sheet</span><span class="pun">.</span><span class="typ">CreateRow</span><span class="pun">(</span><span class="lit">1</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFRow</span><span class="pun">;</span></code></li><li class="L3"><code></code></li><li class="L4"><code></code></li><li class="L5"><code><span class="pln"> </span><span class="typ">HSSFCellStyle</span><span class="pln"> headStyle </span><span class="pun">=</span><span class="pln"> workbook</span><span class="pun">.</span><span class="typ">CreateCellStyle</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFCellStyle</span><span class="pun">;</span></code></li><li class="L6"><code><span class="pln"> headStyle</span><span class="pun">.</span><span class="typ">Alignment</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> NPOI</span><span class="pun">.</span><span class="pln">SS</span><span class="pun">.</span><span class="typ">UserModel</span><span class="pun">.</span><span class="typ">HorizontalAlignment</span><span class="pun">.</span><span class="typ">Center</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="typ">HSSFFont</span><span class="pln"> font </span><span class="pun">=</span><span class="pln"> workbook</span><span class="pun">.</span><span class="typ">CreateFont</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFFont</span><span class="pun">;</span></code></li><li class="L8"><code><span class="pln"> font</span><span class="pun">.</span><span class="typ">FontHeightInPoints</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">10</span><span class="pun">;</span></code></li><li class="L9"><code><span class="pln"> font</span><span class="pun">.</span><span class="typ">Boldweight</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">700</span><span class="pun">;</span></code></li><li class="L0"><code><span class="pln"> headStyle</span><span class="pun">.</span><span class="typ">SetFont</span><span class="pun">(</span><span class="pln">font</span><span class="pun">);</span></code></li><li class="L1"><code></code></li><li class="L2"><code></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">foreach</span><span class="pln"> </span><span class="pun">(</span><span class="typ">DataColumn</span><span class="pln"> column </span><span class="kwd">in</span><span class="pln"> dtSource</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">)</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> headerRow</span><span class="pun">.</span><span class="typ">CreateCell</span><span class="pun">(</span><span class="pln">column</span><span class="pun">.</span><span class="typ">Ordinal</span><span class="pun">).</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="pln">column</span><span class="pun">.</span><span class="typ">ColumnName</span><span class="pun">);</span></code></li><li class="L6"><code><span class="pln"> headerRow</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">column</span><span class="pun">.</span><span class="typ">Ordinal</span><span class="pun">).</span><span class="typ">CellStyle</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> headStyle</span><span class="pun">;</span></code></li><li class="L7"><code></code></li><li class="L8"><code><span class="pln"> </span><span class="com">//设置列宽</span></code></li><li class="L9"><code><span class="pln"> sheet</span><span class="pun">.</span><span class="typ">SetColumnWidth</span><span class="pun">(</span><span class="pln">column</span><span class="pun">.</span><span class="typ">Ordinal</span><span class="pun">,</span><span class="pln"> </span><span class="pun">(</span><span class="pln">arrColWidth</span><span class="pun">[</span><span class="pln">column</span><span class="pun">.</span><span class="typ">Ordinal</span><span class="pun">]</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="lit">256</span><span class="pun">);</span></code></li><li class="L0"><code></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">//headerRow.Dispose();</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L4"><code></code></li><li class="L5"><code><span class="pln"> </span><span class="com">#endregion</span></code></li><li class="L6"><code></code></li><li class="L7"><code><span class="pln"> rowIndex </span><span class="pun">=</span><span class="pln"> </span><span class="lit">2</span><span class="pun">;</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L9"><code></code></li><li class="L0"><code><span class="pln"> </span><span class="com">#endregion</span></code></li><li class="L1"><code></code></li><li class="L2"><code><span class="pln"> </span><span class="com">#region 填充内容</span></code></li><li class="L3"><code></code></li><li class="L4"><code><span class="pln"> </span><span class="typ">HSSFRow</span><span class="pln"> dataRow </span><span class="pun">=</span><span class="pln"> sheet</span><span class="pun">.</span><span class="typ">CreateRow</span><span class="pun">(</span><span class="pln">rowIndex</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFRow</span><span class="pun">;</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">foreach</span><span class="pln"> </span><span class="pun">(</span><span class="typ">DataColumn</span><span class="pln"> column </span><span class="kwd">in</span><span class="pln"> dtSource</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">)</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L7"><code><span class="pln"> </span><span class="typ">HSSFCell</span><span class="pln"> newCell </span><span class="pun">=</span><span class="pln"> dataRow</span><span class="pun">.</span><span class="typ">CreateCell</span><span class="pun">(</span><span class="pln">column</span><span class="pun">.</span><span class="typ">Ordinal</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFCell</span><span class="pun">;</span></code></li><li class="L8"><code></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">string</span><span class="pln"> drValue </span><span class="pun">=</span><span class="pln"> row</span><span class="pun">[</span><span class="pln">column</span><span class="pun">].</span><span class="typ">ToString</span><span class="pun">();</span></code></li><li class="L0"><code></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">switch</span><span class="pln"> </span><span class="pun">(</span><span class="pln">column</span><span class="pun">.</span><span class="typ">DataType</span><span class="pun">.</span><span class="typ">ToString</span><span class="pun">())</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="str">"System.String"</span><span class="pun">:</span><span class="pln"> </span><span class="com">//字符串类型</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">double</span><span class="pln"> result</span><span class="pun">;</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">isNumeric</span><span class="pun">(</span><span class="pln">drValue</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">out</span><span class="pln"> result</span><span class="pun">))</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L7"><code></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">double</span><span class="pun">.</span><span class="typ">TryParse</span><span class="pun">(</span><span class="pln">drValue</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">out</span><span class="pln"> result</span><span class="pun">);</span></code></li><li class="L9"><code><span class="pln"> newCell</span><span class="pun">.</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="pln">result</span><span class="pun">);</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">else</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L4"><code><span class="pln"> newCell</span><span class="pun">.</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="pln">drValue</span><span class="pun">);</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="str">"System.DateTime"</span><span class="pun">:</span><span class="pln"> </span><span class="com">//日期类型</span></code></li><li class="L9"><code><span class="pln"> </span><span class="typ">DateTime</span><span class="pln"> dateV</span><span class="pun">;</span></code></li><li class="L0"><code><span class="pln"> </span><span class="typ">DateTime</span><span class="pun">.</span><span class="typ">TryParse</span><span class="pun">(</span><span class="pln">drValue</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">out</span><span class="pln"> dateV</span><span class="pun">);</span></code></li><li class="L1"><code><span class="pln"> newCell</span><span class="pun">.</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="pln">dateV</span><span class="pun">);</span></code></li><li class="L2"><code></code></li><li class="L3"><code><span class="pln"> newCell</span><span class="pun">.</span><span class="typ">CellStyle</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> dateStyle</span><span class="pun">;</span><span class="pln"> </span><span class="com">//格式化显示</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="str">"System.Boolean"</span><span class="pun">:</span><span class="pln"> </span><span class="com">//布尔型</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">bool</span><span class="pln"> boolV </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">false</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">bool</span><span class="pun">.</span><span class="typ">TryParse</span><span class="pun">(</span><span class="pln">drValue</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">out</span><span class="pln"> boolV</span><span class="pun">);</span></code></li><li class="L8"><code><span class="pln"> newCell</span><span class="pun">.</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="pln">boolV</span><span class="pun">);</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="str">"System.Int16"</span><span class="pun">:</span><span class="pln"> </span><span class="com">//整型</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="str">"System.Int32"</span><span class="pun">:</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="str">"System.Int64"</span><span class="pun">:</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="str">"System.Byte"</span><span class="pun">:</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">int</span><span class="pln"> intV </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">int</span><span class="pun">.</span><span class="typ">TryParse</span><span class="pun">(</span><span class="pln">drValue</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">out</span><span class="pln"> intV</span><span class="pun">);</span></code></li><li class="L6"><code><span class="pln"> newCell</span><span class="pun">.</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="pln">intV</span><span class="pun">);</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="str">"System.Decimal"</span><span class="pun">:</span><span class="pln"> </span><span class="com">//浮点型</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="str">"System.Double"</span><span class="pun">:</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">double</span><span class="pln"> doubV </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">double</span><span class="pun">.</span><span class="typ">TryParse</span><span class="pun">(</span><span class="pln">drValue</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">out</span><span class="pln"> doubV</span><span class="pun">);</span></code></li><li class="L2"><code><span class="pln"> newCell</span><span class="pun">.</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="pln">doubV</span><span class="pun">);</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="str">"System.DBNull"</span><span class="pun">:</span><span class="pln"> </span><span class="com">//空值处理</span></code></li><li class="L5"><code><span class="pln"> newCell</span><span class="pun">.</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="str">""</span><span class="pun">);</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">default</span><span class="pun">:</span></code></li><li class="L8"><code><span class="pln"> newCell</span><span class="pun">.</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="str">""</span><span class="pun">);</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L1"><code></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L3"><code></code></li><li class="L4"><code><span class="pln"> </span><span class="com">#endregion</span></code></li><li class="L5"><code></code></li><li class="L6"><code><span class="pln"> rowIndex</span><span class="pun">++;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">MemoryStream</span><span class="pln"> ms </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">MemoryStream</span><span class="pun">())</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L0"><code><span class="pln"> workbook</span><span class="pun">.</span><span class="typ">Write</span><span class="pun">(</span><span class="pln">ms</span><span class="pun">);</span></code></li><li class="L1"><code><span class="pln"> ms</span><span class="pun">.</span><span class="typ">Flush</span><span class="pun">();</span></code></li><li class="L2"><code><span class="pln"> ms</span><span class="pun">.</span><span class="typ">Position</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> ms</span><span class="pun">;</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code></code></li><li class="L7"><code><span class="pln"> </span><span class="com">/// <summary></span></code></li><li class="L8"><code><span class="pln"> </span><span class="com">/// DataTable导出到Excel文件</span></code></li><li class="L9"><code><span class="pln"> </span><span class="com">/// </summary></span></code></li><li class="L0"><code><span class="pln"> </span><span class="com">/// <param name="dtSource">源DataTable</param></span></code></li><li class="L1"><code><span class="pln"> </span><span class="com">/// <param name="strHeaderText">表头文本</param></span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">/// <param name="strFileName">保存位置</param></span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">void</span><span class="pln"> </span><span class="typ">ExportDTtoExcel</span><span class="pun">(</span><span class="typ">DataTable</span><span class="pln"> dtSource</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> strHeaderText</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> strFileName</span><span class="pun">)</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">MemoryStream</span><span class="pln"> ms </span><span class="pun">=</span><span class="pln"> </span><span class="typ">ExportDT</span><span class="pun">(</span><span class="pln">dtSource</span><span class="pun">,</span><span class="pln"> strHeaderText</span><span class="pun">))</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">FileStream</span><span class="pln"> fs </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">strFileName</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Create</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Write</span><span class="pun">))</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">byte</span><span class="pun">[]</span><span class="pln"> data </span><span class="pun">=</span><span class="pln"> ms</span><span class="pun">.</span><span class="typ">ToArray</span><span class="pun">();</span></code></li><li class="L0"><code><span class="pln"> fs</span><span class="pun">.</span><span class="typ">Write</span><span class="pun">(</span><span class="pln">data</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pun">,</span><span class="pln"> data</span><span class="pun">.</span><span class="typ">Length</span><span class="pun">);</span></code></li><li class="L1"><code><span class="pln"> fs</span><span class="pun">.</span><span class="typ">Flush</span><span class="pun">();</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code><span class="pln"> </span><span class="com">#endregion</span></code></li><li class="L6"><code></code></li><li class="L7"><code><span class="pln"> </span><span class="com">#region 从excel中将数据导出到datatable</span></code></li><li class="L8"><code><span class="pln"> </span><span class="com">/// <summary>读取excel</span></code></li><li class="L9"><code><span class="pln"> </span><span class="com">/// 默认第一行为标头</span></code></li><li class="L0"><code><span class="pln"> </span><span class="com">/// </summary></span></code></li><li class="L1"><code><span class="pln"> </span><span class="com">/// <param name="strFileName">excel文档路径</param></span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">/// <returns></returns></span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> </span><span class="typ">ImportExceltoDt</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> strFileName</span><span class="pun">,</span><span class="kwd">string</span><span class="pln"> sheetName</span><span class="pun">,</span><span class="kwd">bool</span><span class="pln"> includeTitle </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">true</span><span class="pun">)</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> dt </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">DataTable</span><span class="pun">();</span></code></li><li class="L6"><code><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pln"> hssfworkbook</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">FileStream</span><span class="pln"> file </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">strFileName</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Read</span><span class="pun">))</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> hssfworkbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pun">(</span><span class="pln">file</span><span class="pun">);</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L1"><code><span class="pln"> </span><span class="typ">HSSFSheet</span><span class="pln"> sheet </span><span class="pun">=</span><span class="pln"> hssfworkbook</span><span class="pun">.</span><span class="typ">GetSheet</span><span class="pun">(</span><span class="pln">sheetName</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFSheet</span><span class="pun">;</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">sheet </span><span class="pun">==</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span></code></li><li class="L3"><code><span class="pln"> dt </span><span class="pun">=</span><span class="pln"> </span><span class="typ">ImportDt</span><span class="pun">(</span><span class="pln">sheet</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pun">,</span><span class="pln"> includeTitle</span><span class="pun">);</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> dt</span><span class="pun">;</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code></code></li><li class="L7"><code><span class="pln"> </span><span class="com">/// <summary></span></code></li><li class="L8"><code><span class="pln"> </span><span class="com">/// 读取excel</span></code></li><li class="L9"><code><span class="pln"> </span><span class="com">/// </summary></span></code></li><li class="L0"><code><span class="pln"> </span><span class="com">/// <param name="strFileName">excel文件路径</param></span></code></li><li class="L1"><code><span class="pln"> </span><span class="com">/// <param name="sheet">需要导出的sheet</param></span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">/// <param name="HeaderRowIndex">列头所在行号,-1表示没有列头</param></span></code></li><li class="L3"><code><span class="pln"> </span><span class="com">/// <returns></returns></span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> </span><span class="typ">ImportExceltoDt</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> strFileName</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> </span><span class="typ">SheetName</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">HeaderRowIndex</span><span class="pun">)</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L6"><code><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pln"> workbook</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">FileStream</span><span class="pln"> file </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">strFileName</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Read</span><span class="pun">))</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> workbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pun">(</span><span class="pln">file</span><span class="pun">);</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L1"><code><span class="pln"> </span><span class="typ">HSSFSheet</span><span class="pln"> sheet </span><span class="pun">=</span><span class="pln"> workbook</span><span class="pun">.</span><span class="typ">GetSheet</span><span class="pun">(</span><span class="typ">SheetName</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFSheet</span><span class="pun">;</span></code></li><li class="L2"><code><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> table </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">DataTable</span><span class="pun">();</span></code></li><li class="L3"><code><span class="pln"> table </span><span class="pun">=</span><span class="pln"> </span><span class="typ">ImportDt</span><span class="pun">(</span><span class="pln">sheet</span><span class="pun">,</span><span class="pln"> </span><span class="typ">HeaderRowIndex</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">true</span><span class="pun">);</span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">//ExcelFileStream.Close();</span></code></li><li class="L5"><code><span class="pln"> workbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span></code></li><li class="L6"><code><span class="pln"> sheet </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> table</span><span class="pun">;</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L9"><code></code></li><li class="L0"><code><span class="pln"> </span><span class="com">/// <summary></span></code></li><li class="L1"><code><span class="pln"> </span><span class="com">/// 读取excel</span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">/// </summary></span></code></li><li class="L3"><code><span class="pln"> </span><span class="com">/// <param name="strFileName">excel文件路径</param></span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">/// <param name="sheet">需要导出的sheet序号</param></span></code></li><li class="L5"><code><span class="pln"> </span><span class="com">/// <param name="HeaderRowIndex">列头所在行号,-1表示没有列头</param></span></code></li><li class="L6"><code><span class="pln"> </span><span class="com">/// <returns></returns></span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> </span><span class="typ">ImportExceltoDt</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> strFileName</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">SheetIndex</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">HeaderRowIndex</span><span class="pun">)</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pln"> workbook</span><span class="pun">;</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">FileStream</span><span class="pln"> file </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">strFileName</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Read</span><span class="pun">))</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L2"><code><span class="pln"> workbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pun">(</span><span class="pln">file</span><span class="pun">);</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L4"><code><span class="pln"> </span><span class="typ">HSSFSheet</span><span class="pln"> sheet </span><span class="pun">=</span><span class="pln"> workbook</span><span class="pun">.</span><span class="typ">GetSheetAt</span><span class="pun">(</span><span class="typ">SheetIndex</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFSheet</span><span class="pun">;</span></code></li><li class="L5"><code><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> table </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">DataTable</span><span class="pun">();</span></code></li><li class="L6"><code><span class="pln"> table </span><span class="pun">=</span><span class="pln"> </span><span class="typ">ImportDt</span><span class="pun">(</span><span class="pln">sheet</span><span class="pun">,</span><span class="pln"> </span><span class="typ">HeaderRowIndex</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">true</span><span class="pun">);</span></code></li><li class="L7"><code><span class="pln"> </span><span class="com">//ExcelFileStream.Close();</span></code></li><li class="L8"><code><span class="pln"> workbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span></code></li><li class="L9"><code><span class="pln"> sheet </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> table</span><span class="pun">;</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code></code></li><li class="L3"><code><span class="pln"> </span><span class="com">/// <summary></span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">/// 读取excel</span></code></li><li class="L5"><code><span class="pln"> </span><span class="com">/// </summary></span></code></li><li class="L6"><code><span class="pln"> </span><span class="com">/// <param name="strFileName">excel文件路径</param></span></code></li><li class="L7"><code><span class="pln"> </span><span class="com">/// <param name="sheet">需要导出的sheet</param></span></code></li><li class="L8"><code><span class="pln"> </span><span class="com">/// <param name="HeaderRowIndex">列头所在行号,-1表示没有列头</param></span></code></li><li class="L9"><code><span class="pln"> </span><span class="com">/// <returns></returns></span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> </span><span class="typ">ImportExceltoDt</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> strFileName</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> </span><span class="typ">SheetName</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">HeaderRowIndex</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">bool</span><span class="pln"> needHeader</span><span class="pun">)</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L2"><code><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pln"> workbook</span><span class="pun">;</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">FileStream</span><span class="pln"> file </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">strFileName</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Read</span><span class="pun">))</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> workbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pun">(</span><span class="pln">file</span><span class="pun">);</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code><span class="pln"> </span><span class="typ">HSSFSheet</span><span class="pln"> sheet </span><span class="pun">=</span><span class="pln"> workbook</span><span class="pun">.</span><span class="typ">GetSheet</span><span class="pun">(</span><span class="typ">SheetName</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFSheet</span><span class="pun">;</span></code></li><li class="L8"><code><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> table </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">DataTable</span><span class="pun">();</span></code></li><li class="L9"><code><span class="pln"> table </span><span class="pun">=</span><span class="pln"> </span><span class="typ">ImportDt</span><span class="pun">(</span><span class="pln">sheet</span><span class="pun">,</span><span class="pln"> </span><span class="typ">HeaderRowIndex</span><span class="pun">,</span><span class="pln"> needHeader</span><span class="pun">);</span></code></li><li class="L0"><code><span class="pln"> </span><span class="com">//ExcelFileStream.Close();</span></code></li><li class="L1"><code><span class="pln"> workbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span></code></li><li class="L2"><code><span class="pln"> sheet </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> table</span><span class="pun">;</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code></code></li><li class="L6"><code><span class="pln"> </span><span class="com">/// <summary></span></code></li><li class="L7"><code><span class="pln"> </span><span class="com">/// 读取excel</span></code></li><li class="L8"><code><span class="pln"> </span><span class="com">/// </summary></span></code></li><li class="L9"><code><span class="pln"> </span><span class="com">/// <param name="strFileName">excel文件路径</param></span></code></li><li class="L0"><code><span class="pln"> </span><span class="com">/// <param name="sheet">需要导出的sheet序号</param></span></code></li><li class="L1"><code><span class="pln"> </span><span class="com">/// <param name="HeaderRowIndex">列头所在行号,-1表示没有列头</param></span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">/// <returns></returns></span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> </span><span class="typ">ImportExceltoDt</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> strFileName</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">SheetIndex</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">HeaderRowIndex</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">bool</span><span class="pln"> needHeader</span><span class="pun">)</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pln"> workbook</span><span class="pun">;</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">FileStream</span><span class="pln"> file </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">strFileName</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Read</span><span class="pun">))</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L8"><code><span class="pln"> workbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pun">(</span><span class="pln">file</span><span class="pun">);</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code><span class="pln"> </span><span class="typ">HSSFSheet</span><span class="pln"> sheet </span><span class="pun">=</span><span class="pln"> workbook</span><span class="pun">.</span><span class="typ">GetSheetAt</span><span class="pun">(</span><span class="typ">SheetIndex</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFSheet</span><span class="pun">;</span></code></li><li class="L1"><code><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> table </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">DataTable</span><span class="pun">();</span></code></li><li class="L2"><code><span class="pln"> table </span><span class="pun">=</span><span class="pln"> </span><span class="typ">ImportDt</span><span class="pun">(</span><span class="pln">sheet</span><span class="pun">,</span><span class="pln"> </span><span class="typ">HeaderRowIndex</span><span class="pun">,</span><span class="pln"> needHeader</span><span class="pun">);</span></code></li><li class="L3"><code><span class="pln"> </span><span class="com">//ExcelFileStream.Close();</span></code></li><li class="L4"><code><span class="pln"> workbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span></code></li><li class="L5"><code><span class="pln"> sheet </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> table</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L8"><code></code></li><li class="L9"><code><span class="pln"> </span><span class="com">/// <summary></span></code></li><li class="L0"><code><span class="pln"> </span><span class="com">/// 将制定sheet中的数据导出到datatable中</span></code></li><li class="L1"><code><span class="pln"> </span><span class="com">/// </summary></span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">/// <param name="sheet">需要导出的sheet</param></span></code></li><li class="L3"><code><span class="pln"> </span><span class="com">/// <param name="HeaderRowIndex">列头所在行号,-1表示没有列头</param></span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">/// <returns></returns></span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> </span><span class="typ">ImportDt</span><span class="pun">(</span><span class="typ">HSSFSheet</span><span class="pln"> sheet</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">HeaderRowIndex</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">bool</span><span class="pln"> needHeader</span><span class="pun">)</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L7"><code><span class="pln"> </span><span class="typ">DataTable</span><span class="pln"> table </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">DataTable</span><span class="pun">();</span></code></li><li class="L8"><code><span class="pln"> </span><span class="typ">HSSFRow</span><span class="pln"> headerRow</span><span class="pun">;</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">int</span><span class="pln"> cellCount</span><span class="pun">;</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="typ">HeaderRowIndex</span><span class="pln"> </span><span class="pun"><</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> </span><span class="pun">||</span><span class="pln"> </span><span class="pun">!</span><span class="pln">needHeader</span><span class="pun">)</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L4"><code><span class="pln"> headerRow </span><span class="pun">=</span><span class="pln"> sheet</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="lit">0</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFRow</span><span class="pun">;</span></code></li><li class="L5"><code><span class="pln"> cellCount </span><span class="pun">=</span><span class="pln"> headerRow</span><span class="pun">.</span><span class="typ">LastCellNum</span><span class="pun">;</span></code></li><li class="L6"><code></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> i </span><span class="pun">=</span><span class="pln"> headerRow</span><span class="pun">.</span><span class="typ">FirstCellNum</span><span class="pun">;</span><span class="pln"> i </span><span class="pun"><</span><span class="pln"> cellCount</span><span class="pun">;</span><span class="pln"> i</span><span class="pun">++)</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="typ">DataColumn</span><span class="pln"> column </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">DataColumn</span><span class="pun">(</span><span class="typ">Convert</span><span class="pun">.</span><span class="typ">ToString</span><span class="pun">(</span><span class="pln">i</span><span class="pun">));</span></code></li><li class="L0"><code><span class="pln"> table</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">.</span><span class="typ">Add</span><span class="pun">(</span><span class="pln">column</span><span class="pun">);</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">else</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> headerRow </span><span class="pun">=</span><span class="pln"> sheet</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="typ">HeaderRowIndex</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFRow</span><span class="pun">;</span></code></li><li class="L6"><code><span class="pln"> cellCount </span><span class="pun">=</span><span class="pln"> headerRow</span><span class="pun">.</span><span class="typ">LastCellNum</span><span class="pun">;</span></code></li><li class="L7"><code></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> i </span><span class="pun">=</span><span class="pln"> headerRow</span><span class="pun">.</span><span class="typ">FirstCellNum</span><span class="pun">;</span><span class="pln"> i </span><span class="pun"><</span><span class="pln"> cellCount</span><span class="pun">;</span><span class="pln"> i</span><span class="pun">++)</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">headerRow</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">i</span><span class="pun">)</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">table</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">.</span><span class="typ">IndexOf</span><span class="pun">(</span><span class="typ">Convert</span><span class="pun">.</span><span class="typ">ToString</span><span class="pun">(</span><span class="pln">i</span><span class="pun">))</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="lit">0</span><span class="pun">)</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L4"><code><span class="pln"> </span><span class="typ">DataColumn</span><span class="pln"> column </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">DataColumn</span><span class="pun">(</span><span class="typ">Convert</span><span class="pun">.</span><span class="typ">ToString</span><span class="pun">(</span><span class="str">"重复列名"</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> i</span><span class="pun">));</span></code></li><li class="L5"><code><span class="pln"> table</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">.</span><span class="typ">Add</span><span class="pun">(</span><span class="pln">column</span><span class="pun">);</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">else</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="typ">DataColumn</span><span class="pln"> column </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">DataColumn</span><span class="pun">(</span><span class="typ">Convert</span><span class="pun">.</span><span class="typ">ToString</span><span class="pun">(</span><span class="pln">i</span><span class="pun">));</span></code></li><li class="L0"><code><span class="pln"> table</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">.</span><span class="typ">Add</span><span class="pun">(</span><span class="pln">column</span><span class="pun">);</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">else</span><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">table</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">.</span><span class="typ">IndexOf</span><span class="pun">(</span><span class="pln">headerRow</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">i</span><span class="pun">).</span><span class="typ">ToString</span><span class="pun">())</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="lit">0</span><span class="pun">)</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L6"><code><span class="pln"> </span><span class="typ">DataColumn</span><span class="pln"> column </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">DataColumn</span><span class="pun">(</span><span class="typ">Convert</span><span class="pun">.</span><span class="typ">ToString</span><span class="pun">(</span><span class="str">"重复列名"</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> i</span><span class="pun">));</span></code></li><li class="L7"><code><span class="pln"> table</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">.</span><span class="typ">Add</span><span class="pun">(</span><span class="pln">column</span><span class="pun">);</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">else</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="typ">DataColumn</span><span class="pln"> column </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">DataColumn</span><span class="pun">(</span><span class="pln">headerRow</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">i</span><span class="pun">).</span><span class="typ">ToString</span><span class="pun">());</span></code></li><li class="L2"><code><span class="pln"> table</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">.</span><span class="typ">Add</span><span class="pun">(</span><span class="pln">column</span><span class="pun">);</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">int</span><span class="pln"> rowCount </span><span class="pun">=</span><span class="pln"> sheet</span><span class="pun">.</span><span class="typ">LastRowNum</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> i </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="typ">HeaderRowIndex</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="lit">1</span><span class="pun">);</span><span class="pln"> i </span><span class="pun"><=</span><span class="pln"> rowCount</span><span class="pun">;</span><span class="pln"> i</span><span class="pun">++)</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="typ">HSSFRow</span><span class="pln"> row</span><span class="pun">;</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">sheet</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i</span><span class="pun">)</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L4"><code><span class="pln"> row </span><span class="pun">=</span><span class="pln"> sheet</span><span class="pun">.</span><span class="typ">CreateRow</span><span class="pun">(</span><span class="pln">i</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFRow</span><span class="pun">;</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">else</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L8"><code><span class="pln"> row </span><span class="pun">=</span><span class="pln"> sheet</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">HSSFRow</span><span class="pun">;</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code></code></li><li class="L1"><code><span class="pln"> </span><span class="typ">DataRow</span><span class="pln"> dataRow </span><span class="pun">=</span><span class="pln"> table</span><span class="pun">.</span><span class="typ">NewRow</span><span class="pun">();</span></code></li><li class="L2"><code></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> j </span><span class="pun">=</span><span class="pln"> row</span><span class="pun">.</span><span class="typ">FirstCellNum</span><span class="pun">;</span><span class="pln"> j </span><span class="pun"><</span><span class="pln"> cellCount</span><span class="pun">;</span><span class="pln"> j</span><span class="pun">++)</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">row</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">)</span><span class="pln"> </span><span class="pun">!=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">switch</span><span class="pln"> </span><span class="pun">(</span><span class="pln">row</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">).</span><span class="typ">CellType</span><span class="pun">)</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="typ">CellType</span><span class="pun">.</span><span class="typ">String</span><span class="pun">:</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">string</span><span class="pln"> str </span><span class="pun">=</span><span class="pln"> row</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">).</span><span class="typ">StringCellValue</span><span class="pun">;</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(!</span><span class="kwd">string</span><span class="pun">.</span><span class="typ">IsNullOrEmpty</span><span class="pun">(</span><span class="pln">str</span><span class="pun">))</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> dataRow</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> str</span><span class="pun">.</span><span class="typ">ToString</span><span class="pun">();</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">else</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> dataRow</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="typ">CellType</span><span class="pun">.</span><span class="typ">Numeric</span><span class="pun">:</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="typ">DateUtil</span><span class="pun">.</span><span class="typ">IsCellDateFormatted</span><span class="pun">(</span><span class="pln">row</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">)))</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> dataRow</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">DateTime</span><span class="pun">.</span><span class="typ">FromOADate</span><span class="pun">(</span><span class="pln">row</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">).</span><span class="typ">NumericCellValue</span><span class="pun">);</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">else</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> dataRow</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Convert</span><span class="pun">.</span><span class="typ">ToDouble</span><span class="pun">(</span><span class="pln">row</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">).</span><span class="typ">NumericCellValue</span><span class="pun">);</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="typ">CellType</span><span class="pun">.</span><span class="typ">Boolean</span><span class="pun">:</span></code></li><li class="L3"><code><span class="pln"> dataRow</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Convert</span><span class="pun">.</span><span class="typ">ToString</span><span class="pun">(</span><span class="pln">row</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">).</span><span class="typ">BooleanCellValue</span><span class="pun">);</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="typ">CellType</span><span class="pun">.</span><span class="typ">Error</span><span class="pun">:</span></code></li><li class="L6"><code><span class="pln"> dataRow</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">ErrorEval</span><span class="pun">.</span><span class="typ">GetText</span><span class="pun">(</span><span class="pln">row</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">).</span><span class="typ">ErrorCellValue</span><span class="pun">);</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="typ">CellType</span><span class="pun">.</span><span class="typ">Formula</span><span class="pun">:</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">switch</span><span class="pln"> </span><span class="pun">(</span><span class="pln">row</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">).</span><span class="typ">CachedFormulaResultType</span><span class="pun">)</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="typ">CellType</span><span class="pun">.</span><span class="typ">String</span><span class="pun">:</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">string</span><span class="pln"> strFORMULA </span><span class="pun">=</span><span class="pln"> row</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">).</span><span class="typ">StringCellValue</span><span class="pun">;</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(!</span><span class="kwd">string</span><span class="pun">.</span><span class="typ">IsNullOrEmpty</span><span class="pun">(</span><span class="pln">strFORMULA</span><span class="pun">))</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> dataRow</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> strFORMULA</span><span class="pun">.</span><span class="typ">ToString</span><span class="pun">();</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">else</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> dataRow</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="typ">CellType</span><span class="pun">.</span><span class="typ">Numeric</span><span class="pun">:</span></code></li><li class="L3"><code><span class="pln"> dataRow</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Convert</span><span class="pun">.</span><span class="typ">ToString</span><span class="pun">(</span><span class="pln">row</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">).</span><span class="typ">NumericCellValue</span><span class="pun">);</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="typ">CellType</span><span class="pun">.</span><span class="typ">Boolean</span><span class="pun">:</span></code></li><li class="L6"><code><span class="pln"> dataRow</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Convert</span><span class="pun">.</span><span class="typ">ToString</span><span class="pun">(</span><span class="pln">row</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">).</span><span class="typ">BooleanCellValue</span><span class="pun">);</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="typ">CellType</span><span class="pun">.</span><span class="typ">Error</span><span class="pun">:</span></code></li><li class="L9"><code><span class="pln"> dataRow</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">ErrorEval</span><span class="pun">.</span><span class="typ">GetText</span><span class="pun">(</span><span class="pln">row</span><span class="pun">.</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">).</span><span class="typ">ErrorCellValue</span><span class="pun">);</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">default</span><span class="pun">:</span></code></li><li class="L2"><code><span class="pln"> dataRow</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">""</span><span class="pun">;</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">default</span><span class="pun">:</span></code></li><li class="L7"><code><span class="pln"> dataRow</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">""</span><span class="pun">;</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> exception</span><span class="pun">)</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">throw</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Exception</span><span class="pun">(</span><span class="pln">exception</span><span class="pun">.</span><span class="typ">Message</span><span class="pun">);</span></code></li><li class="L5"><code><span class="pln"> </span><span class="com">//wl.WriteLogs(exception.ToString());</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L8"><code><span class="pln"> table</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">.</span><span class="typ">Add</span><span class="pun">(</span><span class="pln">dataRow</span><span class="pun">);</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> exception</span><span class="pun">)</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">throw</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Exception</span><span class="pun">(</span><span class="pln">exception</span><span class="pun">.</span><span class="typ">Message</span><span class="pun">);</span></code></li><li class="L3"><code><span class="pln"> </span><span class="com">//wl.WriteLogs(exception.ToString());</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> exception</span><span class="pun">)</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">throw</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Exception</span><span class="pun">(</span><span class="pln">exception</span><span class="pun">.</span><span class="typ">Message</span><span class="pun">);</span></code></li><li class="L0"><code><span class="pln"> </span><span class="com">//wl.WriteLogs(exception.ToString());</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> table</span><span class="pun">;</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">#endregion</span></code></li><li class="L5"><code></code></li><li class="L6"><code><span class="pln"> </span><span class="com">#region 更新excel中的数据</span></code></li><li class="L7"><code></code></li><li class="L8"><code><span class="pln"> </span><span class="com">/// <summary></span></code></li><li class="L9"><code><span class="pln"> </span><span class="com">/// 更新Excel表格</span></code></li><li class="L0"><code><span class="pln"> </span><span class="com">/// </summary></span></code></li><li class="L1"><code><span class="pln"> </span><span class="com">/// <param name="outputFile">需更新的excel表格路径</param></span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">/// <param name="sheetname">sheet名</param></span></code></li><li class="L3"><code><span class="pln"> </span><span class="com">/// <param name="data"></param></span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">/// <param name="saveTitle"></param></span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">bool</span><span class="pln"> </span><span class="typ">UpdateExcel</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> oldFile</span><span class="pun">,</span><span class="kwd">string</span><span class="pln"> newFile</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> sheetname</span><span class="pun">,</span><span class="typ">DataTable</span><span class="pln"> data</span><span class="pun">,</span><span class="kwd">bool</span><span class="pln"> saveTitle </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">true</span><span class="pun">)</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pln"> hssfworkbook</span><span class="pun">;</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">FileStream</span><span class="pln"> readfile </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">oldFile</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Read</span><span class="pun">))</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L2"><code></code></li><li class="L3"><code><span class="pln"> hssfworkbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pun">(</span><span class="pln">readfile</span><span class="pun">);</span></code></li><li class="L4"><code><span class="pln"> </span><span class="typ">ISheet</span><span class="pln"> sheet1 </span><span class="pun">=</span><span class="pln"> hssfworkbook</span><span class="pun">.</span><span class="typ">GetSheet</span><span class="pun">(</span><span class="pln">sheetname</span><span class="pun">);</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">int</span><span class="pln"> currentSheetRowIndex </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">saveTitle</span><span class="pun">)</span><span class="pln"> currentSheetRowIndex </span><span class="pun">=</span><span class="pln"> </span><span class="lit">1</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> i </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> i </span><span class="pun"><</span><span class="pln"> data</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">;</span><span class="pln"> i</span><span class="pun">++,</span><span class="pln"> currentSheetRowIndex</span><span class="pun">++)</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> j </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> j </span><span class="pun"><</span><span class="pln"> data</span><span class="pun">.</span><span class="typ">Columns</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">;</span><span class="pln"> j</span><span class="pun">++)</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">currentSheetRowIndex</span><span class="pun">)</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span></code></li><li class="L2"><code><span class="pln"> sheet1</span><span class="pun">.</span><span class="typ">CreateRow</span><span class="pun">(</span><span class="pln">currentSheetRowIndex</span><span class="pun">);</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">currentSheetRowIndex</span><span class="pun">).</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">)</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span></code></li><li class="L4"><code><span class="pln"> sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">currentSheetRowIndex</span><span class="pun">).</span><span class="typ">CreateCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">);</span></code></li><li class="L5"><code><span class="pln"> sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">currentSheetRowIndex</span><span class="pun">).</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">j</span><span class="pun">).</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="pln">data</span><span class="pun">.</span><span class="typ">Rows</span><span class="pun">[</span><span class="pln">i</span><span class="pun">][</span><span class="pln">j</span><span class="pun">].</span><span class="typ">ToString</span><span class="pun">());</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="typ">FileStream</span><span class="pln"> writefile </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">newFile</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Create</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Write</span><span class="pun">))</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L2"><code><span class="pln"> hssfworkbook</span><span class="pun">.</span><span class="typ">Write</span><span class="pun">(</span><span class="pln">writefile</span><span class="pun">);</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">IOException</span><span class="pln"> ioException</span><span class="pun">)</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">throw</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">IOException</span><span class="pun">(</span><span class="pln">ioException</span><span class="pun">.</span><span class="typ">Message</span><span class="pun">);</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">UnauthorizedAccessException</span><span class="pln"> unauthorizedAccessException</span><span class="pun">)</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">throw</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">UnauthorizedAccessException</span><span class="pun">(</span><span class="pln">unauthorizedAccessException</span><span class="pun">.</span><span class="typ">Message</span><span class="pun">);</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> ex</span><span class="pun">)</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">throw</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Exception</span><span class="pun">(</span><span class="pln">ex</span><span class="pun">.</span><span class="typ">Message</span><span class="pun">);</span></code></li><li class="L6"><code><span class="pln"> </span><span class="com">// wl.WriteLogs(ex.ToString());</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">true</span><span class="pun">;</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code></code></li><li class="L1"><code><span class="pln"> </span><span class="com">/// <summary></span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">/// 更新Excel表格</span></code></li><li class="L3"><code><span class="pln"> </span><span class="com">/// </summary></span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">/// <param name="outputFile">需更新的excel表格路径</param></span></code></li><li class="L5"><code><span class="pln"> </span><span class="com">/// <param name="sheetname">sheet名</param></span></code></li><li class="L6"><code><span class="pln"> </span><span class="com">/// <param name="updateData">需更新的数据</param></span></code></li><li class="L7"><code><span class="pln"> </span><span class="com">/// <param name="coluids">需更新的列号</param></span></code></li><li class="L8"><code><span class="pln"> </span><span class="com">/// <param name="rowid">需更新的开始行号</param></span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">void</span><span class="pln"> </span><span class="typ">UpdateExcel</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> outputFile</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> sheetname</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pun">[][]</span><span class="pln"> updateData</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">[]</span><span class="pln"> coluids</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> rowid</span><span class="pun">)</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="typ">FileStream</span><span class="pln"> readfile </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">outputFile</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Read</span><span class="pun">);</span></code></li><li class="L2"><code></code></li><li class="L3"><code><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pln"> hssfworkbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pun">(</span><span class="pln">readfile</span><span class="pun">);</span></code></li><li class="L4"><code><span class="pln"> readfile</span><span class="pun">.</span><span class="typ">Close</span><span class="pun">();</span></code></li><li class="L5"><code><span class="pln"> </span><span class="typ">ISheet</span><span class="pln"> sheet1 </span><span class="pun">=</span><span class="pln"> hssfworkbook</span><span class="pun">.</span><span class="typ">GetSheet</span><span class="pun">(</span><span class="pln">sheetname</span><span class="pun">);</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> j </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> j </span><span class="pun"><</span><span class="pln"> coluids</span><span class="pun">.</span><span class="typ">Length</span><span class="pun">;</span><span class="pln"> j</span><span class="pun">++)</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> i </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> i </span><span class="pun"><</span><span class="pln"> updateData</span><span class="pun">[</span><span class="pln">j</span><span class="pun">].</span><span class="typ">Length</span><span class="pun">;</span><span class="pln"> i</span><span class="pun">++)</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L0"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">)</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L4"><code><span class="pln"> sheet1</span><span class="pun">.</span><span class="typ">CreateRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">);</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">).</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">coluids</span><span class="pun">[</span><span class="pln">j</span><span class="pun">])</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L8"><code><span class="pln"> sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">).</span><span class="typ">CreateCell</span><span class="pun">(</span><span class="pln">coluids</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]);</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L0"><code><span class="pln"> sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">).</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">coluids</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]).</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="pln">updateData</span><span class="pun">[</span><span class="pln">j</span><span class="pun">][</span><span class="pln">i</span><span class="pun">]);</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> ex</span><span class="pun">)</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">// wl.WriteLogs(ex.ToString());</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L0"><code><span class="pln"> </span><span class="typ">FileStream</span><span class="pln"> writefile </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">outputFile</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Create</span><span class="pun">);</span></code></li><li class="L1"><code><span class="pln"> hssfworkbook</span><span class="pun">.</span><span class="typ">Write</span><span class="pun">(</span><span class="pln">writefile</span><span class="pun">);</span></code></li><li class="L2"><code><span class="pln"> writefile</span><span class="pun">.</span><span class="typ">Close</span><span class="pun">();</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> ex</span><span class="pun">)</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L6"><code><span class="pln"> </span><span class="com">//wl.WriteLogs(ex.ToString());</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L9"><code></code></li><li class="L0"><code><span class="pln"> </span><span class="com">/// <summary></span></code></li><li class="L1"><code><span class="pln"> </span><span class="com">/// 更新Excel表格</span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">/// </summary></span></code></li><li class="L3"><code><span class="pln"> </span><span class="com">/// <param name="outputFile">需更新的excel表格路径</param></span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">/// <param name="sheetname">sheet名</param></span></code></li><li class="L5"><code><span class="pln"> </span><span class="com">/// <param name="updateData">需更新的数据</param></span></code></li><li class="L6"><code><span class="pln"> </span><span class="com">/// <param name="coluid">需更新的列号</param></span></code></li><li class="L7"><code><span class="pln"> </span><span class="com">/// <param name="rowid">需更新的开始行号</param></span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">void</span><span class="pln"> </span><span class="typ">UpdateExcel</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> outputFile</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> sheetname</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">double</span><span class="pun">[]</span><span class="pln"> updateData</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> coluid</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> rowid</span><span class="pun">)</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L0"><code><span class="pln"> </span><span class="typ">FileStream</span><span class="pln"> readfile </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">outputFile</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Read</span><span class="pun">);</span></code></li><li class="L1"><code></code></li><li class="L2"><code><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pln"> hssfworkbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pun">(</span><span class="pln">readfile</span><span class="pun">);</span></code></li><li class="L3"><code><span class="pln"> </span><span class="typ">ISheet</span><span class="pln"> sheet1 </span><span class="pun">=</span><span class="pln"> hssfworkbook</span><span class="pun">.</span><span class="typ">GetSheet</span><span class="pun">(</span><span class="pln">sheetname</span><span class="pun">);</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> i </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> i </span><span class="pun"><</span><span class="pln"> updateData</span><span class="pun">.</span><span class="typ">Length</span><span class="pun">;</span><span class="pln"> i</span><span class="pun">++)</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">)</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L0"><code><span class="pln"> sheet1</span><span class="pun">.</span><span class="typ">CreateRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">);</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">).</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">coluid</span><span class="pun">)</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L4"><code><span class="pln"> sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">).</span><span class="typ">CreateCell</span><span class="pun">(</span><span class="pln">coluid</span><span class="pun">);</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code></code></li><li class="L7"><code><span class="pln"> sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">).</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">coluid</span><span class="pun">).</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="pln">updateData</span><span class="pun">[</span><span class="pln">i</span><span class="pun">]);</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> ex</span><span class="pun">)</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">throw</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Exception</span><span class="pun">(</span><span class="pln">ex</span><span class="pun">.</span><span class="typ">Message</span><span class="pun">);</span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">//wl.WriteLogs(ex.ToString());</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">throw</span><span class="pun">;</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L8"><code><span class="pln"> readfile</span><span class="pun">.</span><span class="typ">Close</span><span class="pun">();</span></code></li><li class="L9"><code><span class="pln"> </span><span class="typ">FileStream</span><span class="pln"> writefile </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">outputFile</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Create</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Write</span><span class="pun">);</span></code></li><li class="L0"><code><span class="pln"> hssfworkbook</span><span class="pun">.</span><span class="typ">Write</span><span class="pun">(</span><span class="pln">writefile</span><span class="pun">);</span></code></li><li class="L1"><code><span class="pln"> writefile</span><span class="pun">.</span><span class="typ">Close</span><span class="pun">();</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> ex</span><span class="pun">)</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> </span><span class="com">//wl.WriteLogs(ex.ToString());</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L9"><code></code></li><li class="L0"><code><span class="pln"> </span><span class="com">/// <summary></span></code></li><li class="L1"><code><span class="pln"> </span><span class="com">/// 更新Excel表格</span></code></li><li class="L2"><code><span class="pln"> </span><span class="com">/// </summary></span></code></li><li class="L3"><code><span class="pln"> </span><span class="com">/// <param name="outputFile">需更新的excel表格路径</param></span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">/// <param name="sheetname">sheet名</param></span></code></li><li class="L5"><code><span class="pln"> </span><span class="com">/// <param name="updateData">需更新的数据</param></span></code></li><li class="L6"><code><span class="pln"> </span><span class="com">/// <param name="coluids">需更新的列号</param></span></code></li><li class="L7"><code><span class="pln"> </span><span class="com">/// <param name="rowid">需更新的开始行号</param></span></code></li><li class="L8"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">void</span><span class="pln"> </span><span class="typ">UpdateExcel</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> outputFile</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> sheetname</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">double</span><span class="pun">[][]</span><span class="pln"> updateData</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">[]</span><span class="pln"> coluids</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> rowid</span><span class="pun">)</span></code></li><li class="L9"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L0"><code><span class="pln"> </span><span class="typ">FileStream</span><span class="pln"> readfile </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">outputFile</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Read</span><span class="pun">);</span></code></li><li class="L1"><code></code></li><li class="L2"><code><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pln"> hssfworkbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pun">(</span><span class="pln">readfile</span><span class="pun">);</span></code></li><li class="L3"><code><span class="pln"> readfile</span><span class="pun">.</span><span class="typ">Close</span><span class="pun">();</span></code></li><li class="L4"><code><span class="pln"> </span><span class="typ">ISheet</span><span class="pln"> sheet1 </span><span class="pun">=</span><span class="pln"> hssfworkbook</span><span class="pun">.</span><span class="typ">GetSheet</span><span class="pun">(</span><span class="pln">sheetname</span><span class="pun">);</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> j </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> j </span><span class="pun"><</span><span class="pln"> coluids</span><span class="pun">.</span><span class="typ">Length</span><span class="pun">;</span><span class="pln"> j</span><span class="pun">++)</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> i </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> i </span><span class="pun"><</span><span class="pln"> updateData</span><span class="pun">[</span><span class="pln">j</span><span class="pun">].</span><span class="typ">Length</span><span class="pun">;</span><span class="pln"> i</span><span class="pun">++)</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">)</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L3"><code><span class="pln"> sheet1</span><span class="pun">.</span><span class="typ">CreateRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">);</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">).</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">coluids</span><span class="pun">[</span><span class="pln">j</span><span class="pun">])</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L7"><code><span class="pln"> sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">).</span><span class="typ">CreateCell</span><span class="pun">(</span><span class="pln">coluids</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]);</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L9"><code><span class="pln"> sheet1</span><span class="pun">.</span><span class="typ">GetRow</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> rowid</span><span class="pun">).</span><span class="typ">GetCell</span><span class="pun">(</span><span class="pln">coluids</span><span class="pun">[</span><span class="pln">j</span><span class="pun">]).</span><span class="typ">SetCellValue</span><span class="pun">(</span><span class="pln">updateData</span><span class="pun">[</span><span class="pln">j</span><span class="pun">][</span><span class="pln">i</span><span class="pun">]);</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> ex</span><span class="pun">)</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L3"><code><span class="pln"> </span><span class="com">//wl.WriteLogs(ex.ToString());</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> </span><span class="typ">FileStream</span><span class="pln"> writefile </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">outputFile</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Create</span><span class="pun">);</span></code></li><li class="L0"><code><span class="pln"> hssfworkbook</span><span class="pun">.</span><span class="typ">Write</span><span class="pun">(</span><span class="pln">writefile</span><span class="pun">);</span></code></li><li class="L1"><code><span class="pln"> writefile</span><span class="pun">.</span><span class="typ">Close</span><span class="pun">();</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> ex</span><span class="pun">)</span></code></li><li class="L4"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L5"><code><span class="pln"> </span><span class="com">//wl.WriteLogs(ex.ToString());</span></code></li><li class="L6"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L8"><code></code></li><li class="L9"><code><span class="pln"> </span><span class="com">#endregion</span></code></li><li class="L0"><code></code></li><li class="L1"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">GetSheetNumber</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> outputFile</span><span class="pun">)</span></code></li><li class="L2"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L3"><code><span class="pln"> </span><span class="kwd">int</span><span class="pln"> number </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span></code></li><li class="L4"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L6"><code><span class="pln"> </span><span class="typ">FileStream</span><span class="pln"> readfile </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">outputFile</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Read</span><span class="pun">);</span></code></li><li class="L7"><code></code></li><li class="L8"><code><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pln"> hssfworkbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pun">(</span><span class="pln">readfile</span><span class="pun">);</span></code></li><li class="L9"><code><span class="pln"> number </span><span class="pun">=</span><span class="pln"> hssfworkbook</span><span class="pun">.</span><span class="typ">NumberOfSheets</span><span class="pun">;</span></code></li><li class="L0"><code></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> exception</span><span class="pun">)</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">//wl.WriteLogs(exception.ToString());</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> number</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L8"><code></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="typ">ArrayList</span><span class="pln"> </span><span class="typ">GetSheetName</span><span class="pun">(</span><span class="kwd">string</span><span class="pln"> outputFile</span><span class="pun">)</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="typ">ArrayList</span><span class="pln"> arrayList </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">ArrayList</span><span class="pun">();</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">try</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L4"><code><span class="pln"> </span><span class="typ">FileStream</span><span class="pln"> readfile </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">FileStream</span><span class="pun">(</span><span class="pln">outputFile</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileMode</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FileAccess</span><span class="pun">.</span><span class="typ">Read</span><span class="pun">);</span></code></li><li class="L5"><code></code></li><li class="L6"><code><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pln"> hssfworkbook </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">HSSFWorkbook</span><span class="pun">(</span><span class="pln">readfile</span><span class="pun">);</span></code></li><li class="L7"><code><span class="pln"> </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> i </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> i </span><span class="pun"><</span><span class="pln"> hssfworkbook</span><span class="pun">.</span><span class="typ">NumberOfSheets</span><span class="pun">;</span><span class="pln"> i</span><span class="pun">++)</span></code></li><li class="L8"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L9"><code><span class="pln"> arrayList</span><span class="pun">.</span><span class="typ">Add</span><span class="pun">(</span><span class="pln">hssfworkbook</span><span class="pun">.</span><span class="typ">GetSheetName</span><span class="pun">(</span><span class="pln">i</span><span class="pun">));</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L1"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L2"><code><span class="pln"> </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> exception</span><span class="pun">)</span></code></li><li class="L3"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L4"><code><span class="pln"> </span><span class="com">//wl.WriteLogs(exception.ToString());</span></code></li><li class="L5"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L6"><code><span class="pln"> </span><span class="kwd">return</span><span class="pln"> arrayList</span><span class="pun">;</span></code></li><li class="L7"><code><span class="pln"> </span><span class="pun">}</span></code></li><li class="L8"><code></code></li><li class="L9"><code><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">bool</span><span class="pln"> isNumeric</span><span class="pun">(</span><span class="typ">String</span><span class="pln"> message</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">out</span><span class="pln"> </span><span class="kwd">double</span><span class="pln"> result</span><span class="pun">)</span></code></li><li class="L0"><code><span class="pln"> </span><span class="pun">{</span></code></li><li class="L1"><code><span class="pln"> </span><span class="typ">Regex</span><span class="pln"> rex </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Regex</span><span class="pun">(@</span><span class="str">"^[-]?d+[.]?d*\)");
result = -1;
if (rex.IsMatch(message))
{
result = double.Parse(message);
return true;
}
else return false;
}
}
}
DataTable table = ExcelHelper.ImportExceltoDt(name,"Detail");//name是excel的路径txtSheetName是sheet名称
success = ExcelHelper.UpdateExcel(name, newFileName, "Detail", table);//name是原excel名称,这里我需要把处理好的文件另存而不去修改源文件所以我加入了一个新的路径,table是需要保存的数据返回值为bool表示成功或失败
通过测试NPOI速度为OLEDB的数十倍,