透過陣列轉欄位型態
陣列function可儲存不同的cell
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
private string saveExcel(string processID, string fileName, string sqlDate)
{
AbstractEngine engine = NJPublicFunc.GetEngine();
try
{
if (!File.Exists(Server.MapPath("~\\tempFolder\\" + processID))) //建立資料夾
{
System.IO.Directory.CreateDirectory(Server.MapPath("~\\tempFolder\\" + processID));
}
string dt = DateTime.Now.ToString("yyyy_MM_dd_hhmmss");
string saveName = fileName + dt + ".xlsx"; // 暫存檔名
string saveFilePath = Server.MapPath("~\\tempFolder\\" + processID + "\\" + saveName);
XSSFWorkbook oWorkbook = new XSSFWorkbook(); // 建立.xlsx
setISheet oSheet = new setISheet();
oSheet._ISheet = oWorkbook.CreateSheet("sheet1"); // 建立資料表
#region 寫入excel
string doubleIndex = ",2,6,7,"; // 需轉為數值的位置
DataSet ds = ;
if (ds.Tables[0].Rows.Count > 0)
{
int i=0;
// 標題
foreach (DataColumn column in ds.Tables[0].Columns)
{
oSheet.Cells[0, i] = column.ColumnName;
i++;
}
// 內容
for (i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
if (doubleIndex.IndexOf("," + j.ToString() + ",") > -1) // 數值
{
oSheet.Cells[i + 1, j] = parseDouble(ds.Tables[0].Rows[i][j]);
}
else // 文字
{
oSheet.Cells[i + 1, j] = ds.Tables[0].Rows[i][j].ToString();
}
}
}
}
#endregion
// 儲存excel
FileStream sw = File.Create(saveFilePath);
oWorkbook.Write(sw);
sw.Close();
}
catch (Exception ex)
{
MessageBox(ex.ToString());
}
finally
{
if (engine != null)
{
engine.close();
}
}
return "";
}
private string saveCsv(string processID, string fileName, string sqlDate)
{
AbstractEngine engine = NJPublicFunc.GetEngine();
try
{
if (!File.Exists(Server.MapPath("~\\tempFolder\\" + processID))) //建立資料夾
{
System.IO.Directory.CreateDirectory(Server.MapPath("~\\tempFolder\\" + processID));
}
string dt = DateTime.Now.ToString("yyyy_MM_dd_hhmmss");
string saveName = fileName + dt + ".csv"; // 暫存檔名
string saveFilePath = Server.MapPath("~\\tempFolder\\" + processID + "\\" + saveName);
StreamWriter sw = new StreamWriter(saveFilePath); // 建立.csv
#region 寫入Csv
DataSet ds = ;
if (ds.Tables[0].Rows.Count > 0)
{
int i = 0;
// 標題
for (i = 0; i < ds.Tables[0].Columns.Count; i++)
{
if (i != 0)
{
sw.Write(",");
}
sw.Write(ds.Tables[0].Columns[i].ColumnName);
}
// 內容
for (i = 0; i < ds.Tables[0].Rows.Count; i++)
{
sw.WriteLine("");
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
if (j != 0)
{
sw.Write(",");
}
sw.Write(ds.Tables[0].Rows[i][j].ToString());
}
}
}
#endregion
sw.Close(); // 儲存CSV
return saveFilePath;
}
catch (Exception ex)
{
MessageBox(ex.ToString());
}
finally
{
if (engine != null)
{
engine.close();
}
}
return "";
}
/// <summary>
/// 下載檔案
/// </summary>
/// <param name="downloadName">檔案名稱</param>
/// <param name="filePath">檔案位置</param>
private void downloadFile(string downloadName,string filePath)
{
string fileExt = System.IO.Path.GetExtension(filePath); // 副檔名
Response.Clear();
Response.ClearHeaders();
Response.Buffer = true;
Response.AppendHeader("Content-Disposition", "Attachment;Filename=" + Utility.UrlEncode(downloadName + fileExt));
Response.ContentType = "application/x-download";
Response.ContentEncoding = System.Text.Encoding.UTF8;
FileInfo fileInfo = new FileInfo(filePath);
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.WriteFile(filePath);
Response.Flush();
Response.End();
Response.Close();
}
/// <summary>
/// 轉型double型態
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
private double parseDouble(object obj)
{
string str = NJPublicFunc.objToString(obj);
double result;
if (double.TryParse(str, out result) == false)
{
result = 0;
}
return result;
}
#region Excel的欄位建立存取方法
/// <summary>
/// 存取資料表的Cells
/// </summary>
private class setISheet
{
public ISheet _ISheet;
public setCell Cells
{
get
{
setCell cell = new setCell();
cell._ISheet = _ISheet;
return cell;
}
}
}
/// <summary>
/// 將Cell建立並取值
/// </summary>
private class setCell
{
public ISheet _ISheet;
public object this[object rowIndex, object columnIndex]
{
get
{
int row = (int)rowIndex;
int col = (int)columnIndex;
if (_ISheet.GetRow(row) == null || _ISheet.GetRow(row).GetCell(col) == null)
{
return "";
}
return _ISheet.GetRow(row).GetCell(col);
}
set
{
int row = (int)rowIndex;
int col = (int)columnIndex;
if (_ISheet.GetRow(row) == null)
{
_ISheet.CreateRow(row);
}
if (_ISheet.GetRow(row).GetCell(col) == null)
{
_ISheet.GetRow(row).CreateCell(col);
_ISheet.GetRow(row).GetCell(col).SetCellType(NPOI.SS.UserModel.CellType.Blank);
}
if (value.GetType() == typeof(double)) // 數值
{
_ISheet.GetRow(row).GetCell(col).SetCellValue((double)value);
}
else // 文字
{
_ISheet.GetRow(row).GetCell(col).SetCellValue(value.ToString());
}
}
}
}
#endregion
