C# Browser匯出Excel 及 建立第2個sheet

建立SHEET方法

範例 :

ISheet sheet1 = book.CreateSheet("SHEET_NAME1");  //開第一個SHEET

  shee1......

ISheet sheet2 = book.CreateSheet("SHEET_NAME2");  //開第二個SHEET

  sheet2......

---程式碼案例-------------------------------------------------------------------------------------------------------

using NPOI.HSSF.UserModel;

using NPOI.SS.UserModel;

HSSFWorkbook book = new HSSFWorkbook();

ISheet sheet = book.CreateSheet("sheet1");  //sheet 1

IRow row;

IFont boldfont = book.CreateFont();

boldfont.Boldweight = (short)FontBoldWeight.BOLD; //設定字型

ICellStyle cellstyle = book.CreateCellStyle();

cellstyle.SetFont(boldfont);

int row_index, col_index;  //sheet1的row&col參數宣告

dt = db.ExecuteFillToDataTable(txtSQL);                       

 /////sheet1內容

sheet.CreateRow(0).CreateCell(0).SetCellValue(dt.Rows[0]["theme"].ToString() + "  轉檔時間: " + DateTime.Now);   //第一列

sheet.GetRow(0).GetCell(0).CellStyle = cellstyle;

//Header:匯出題目欄位

row = sheet.CreateRow(1);   //第二列

row.CreateCell(0).SetCellValue("項次");

row.CreateCell(1).SetCellValue("編號");

row.CreateCell(2).SetCellValue("廠商");

ow.CreateCell(3).SetCellValue("聯絡人");

row.CreateCell(i + 4).SetCellValue("填寫時間"); 

 for (int i = 0; i <= dt.Rows.Count; i++)

{

    if (i == dt.Rows.Count) { row.CreateCell(i + 4).SetCellValue("填寫時間");  break; }

    id_list.Add(dt.Rows[i]["no."].ToString());

    row.CreateCell(i + 4).SetCellValue(dt.Rows[i]["no."].ToString() + "." + dt.Rows[i]["question"].ToString());

}

                          

row.RowStyle = cellstyle;  

//回答內容

row_index = 2;

DataTable dt_user= db.ExecuteFillToDataTable("SELECT From TABLE_A);

for (int i = 0; i < dt_user.Rows.Count; i++)

{

   //最後輸出所填答案

   col_index = 0;

   row = sheet.CreateRow(row_index++);

   row.CreateCell(col_index).SetCellValue(i+1);

   row.CreateCell(++col_index).SetCellValue(dt_user.Rows[i]["aaa"].ToString());

   row.CreateCell(++col_index).SetCellValue(dt_user.Rows[i]["bbb"].ToString());

   row.CreateCell(++col_index).SetCellValue(dt_user.Rows[i]["ccc"].ToString());

   row.CreateCell(++col_index).SetCellValue(dt_user.Rows[i]["ddd"].ToString()); 

}

//////SHEET2

ISheet sheet2 = book.CreateSheet("sheet2");   //create sheet 2

sheet2.CreateRow(0).CreateCell(0).SetCellValue("  轉檔時間: " + DateTime.Now);  //第一列

sheet2.GetRow(0).GetCell(0).CellStyle = cellstyle;

int row_index2, col_index2;    //宣告參數

//Header:匯出題目欄位

row = sheet2.CreateRow(1);    //第二列

row.CreateCell(0).SetCellValue("項次");

row.CreateCell(1).SetCellValue("編號");

row.CreateCell(2).SetCellValue("廠商");

row.CreateCell(3).SetCellValue("聯絡人");

row.CreateCell(4).SetCellValue("E-MAIL");

row.RowStyle = cellstyle;

row_index2 = 2;    //第三列開始

                        

col_index2 = 0;

DataTable dt_noans = db.ExecuteFillToDataTable("SELECT From TABLE_B);

for (int i = 0; i < dt_noans.Rows.Count; i++)

{

  row = sheet2.CreateRow(row_index2++);

  row.CreateCell(col_index2).SetCellValue(i + 1);

  row.CreateCell(++col_index2).SetCellValue(dt_noans.Rows[i]["aaa"].ToString());

  row.CreateCell(++col_index2).SetCellValue(dt_noans.Rows[i]["bbb"].ToString());

  row.CreateCell(++col_index2).SetCellValue(dt_noans.Rows[i]["ccc"].ToString());

  row.CreateCell(++col_index2).SetCellValue(dt_noans.Rows[i]["ddd"].ToString());

 }

string outputFile = "excel.xls";

Response.ContentType = "application/excel";

Response.Clear();

Response.AppendHeader("Content-Disposition", "attachment; " + "filename=\"" + outputFile + "\"; ");

MemoryStream out_file = new MemoryStream();

book.Write(out_file);

Response.BinaryWrite(out_file.ToArray());

out_file.Close();

Response.End();