Question

如何用NPOI建立EXCEL的多个SHEET

Answer 1

　　借用NPOI来实现，要在同一Excel文件中创建多个sheet，只需要在同一个workbook中创建多个sheet即可。要注意的是，sheet的名字一定不能重复。下面是实现的代码：
　　private void buttonTest_Click(object sender, EventArgs e)
{
HSSFWorkbook workBook = new HSSFWorkbook();
//ISheet sheetA = workBook.CreateSheet("sheetA");
//ISheet sheetB = workBook.CreateSheet("sheetB");

createSheet(workBook,"SheetA");
createSheet(workBook,"SheetB");
createSheet(workBook,"SheetC");

string path = Application.StartupPath + @"\test.xls";
if (File.Exists(path))
{
File.Delete(path);
}
using (FileStream file = new FileStream(path, FileMode.Create))
{
workBook.Write(file);　　//创建Excel文件。
file.Close();
}
MessageBox.Show("OK");
}

private ISheet createSheet(HSSFWorkbook workBook, string sheetName)
{
ISheet sheet = workBook.CreateSheet(sheetName);
IRow RowHead = sheet.CreateRow(0);

for (int iColumnIndex = 0; iColumnIndex < 10; iColumnIndex++)
{
RowHead.CreateCell(iColumnIndex).SetCellValue(Guid.NewGuid().ToString());
}

for (int iRowIndex = 0; iRowIndex < 20; iRowIndex++)
{
IRow RowBody = sheet.CreateRow(iRowIndex + 1);
for (int iColumnIndex = 0; iColumnIndex < 10; iColumnIndex++)
{
RowBody.CreateCell(iColumnIndex).SetCellValue(DateTime.Now.Millisecond);
sheet.AutoSizeColumn(iColumnIndex);
}
}
return sheet;
}

Answer 2

0

登录进行投票
我要用网页做一个把资料库档案输出成EXCEL的网页
使用的是NPOI的模组
而NPOI里面的模组有一个新增SHEET的 "HSSFSheet"
如果要新增一个Excel的Sheet
就要打 "HSSFSheet pdsheet = workbook.CreateSheet("My Sheet");"
例如这样的程式码
现在如果我想使用回圈
让他跑出很多个Sheet来
可是他都会说 "The workbook already contains a sheet of this name"
应该是说已经有用过pdsheet这个的名称了
现在我想问一下
就是如果要用回圈跑
要怎麼让她每次跑
都可以跑出不一样的sheet名称
例如：
第一次跑：HSSFSheet pdsheet1 = workbook.CreateSheet("My Sheet");
第二次跑：HSSFSheet pdsheet2 = workbook.CreateSheet("My Sheet");
第三次跑...
不晓得要怎麼假设耶
谢谢大家。

下面是回圈内的程式码:

HSSFSheet pdsheet = workbook.CreateSheet("My Sheet");

string content = "", pdName = "";
int x = 1, y, z;
string[] pdarray;
string[] pdtable = new string[] { "\t\n" };

SqlConnection pdobjCon = new SqlConnection(WebConfigurationManager.ConnectionStrings["loginConnectionString"].ConnectionString);
pdobjCon.Open();

string pdqs = "SELECT * FROM dbo.Purchasedetail where Purchase_id = '" + abc + "'";
SqlDataAdapter pdobjDataAdapter = new SqlDataAdapter(pdqs, pdobjCon);

SqlConnection pdSql_Conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["loginConnectionString"].ConnectionString);
string pdSqlString = "Select * From dbo.Purchasedetail where Purchase_id = '" + abc + "'";
pdSql_Conn.Open();

using (SqlCommand pdSql_Command = new SqlCommand())
{
SqlDataReader pdSql_Reader;
pdSql_Command.Connection = pdSql_Conn;
pdSql_Command.CommandText = pdSqlString;

pdSql_Reader = pdSql_Command.ExecuteReader();

DataTable pdschemaTable = pdSql_Reader.GetSchemaTable();
DataRow pddataRow;

z = (int)pdschemaTable.Rows.Count;

for (y = 0; y <= z - 1; y++)
{
pddataRow = pdschemaTable.Rows[y];
pdName += pddataRow["ColumnName"].ToString();
pdName += "\t\n";
}
pdSql_Command.Dispose();
pdSql_Reader.Close();
pdSql_Reader.Dispose();
}
pdSql_Conn.Close();

pdarray = pdName.Split(pdtable, StringSplitOptions.None);

for (y = 0; y <= z - 1; y++)
{
pdsheet.CreateRow(0).CreateCell(y).SetCellValue(pdarray[y]);
}

DataSet pdobjDataSet = new DataSet();
pdobjDataAdapter.Fill(pdobjDataSet, "pd");

DataTable pddt = pdobjDataSet.Tables[0];

foreach (DataRow pdrow in pddt.Rows)
{
for (y = 0; y <= z - 1; y++)
{
pdsheet.CreateRow(x).CreateCell(y).SetCellValue(pdrow[pdarray[y]].ToString());
}
x++;
}

HSSFSheet pdsheet = workbook.CreateSheet("My Sheet"); <-- 这句也在回圈内吗？

改成这样看看
HSSFSheet pdsheet = workbook.CreateSheet("My Sheet" + 回圈次数);

Answer 3

实现代码如下：
private void buttonTest_Click(object sender, EventArgs e)
{
HSSFWorkbook workBook = new HSSFWorkbook();

createSheet(workBook,"SheetA");
createSheet(workBook,"SheetB");
createSheet(workBook,"SheetC");

string path = Application.StartupPath + @"\test.xls";
if (File.Exists(path))
{
File.Delete(path);
}
using (FileStream file = new FileStream(path, FileMode.Create))
{
workBook.Write(file);
file.Close();
}
MessageBox.Show("OK");
}

private ISheet createSheet(HSSFWorkbook workBook, string sheetName)
{
ISheet sheet = workBook.CreateSheet(sheetName);
IRow RowHead = sheet.CreateRow(0);

for (int iColumnIndex = 0; iColumnIndex < 10; iColumnIndex++)
{
RowHead.CreateCell(iColumnIndex).SetCellValue(Guid.NewGuid().ToString());
}

for (int iRowIndex = 0; iRowIndex < 20; iRowIndex++)
{
IRow RowBody = sheet.CreateRow(iRowIndex + 1);
for (int iColumnIndex = 0; iColumnIndex < 10; iColumnIndex++)
{
RowBody.CreateCell(iColumnIndex).SetCellValue(DateTime.Now.Millisecond);
sheet.AutoSizeColumn(iColumnIndex);
}
}
return sheet;
}