簡單的將資料塞進Excel , 下載至User 電腦
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
NEW Workbook
XSSFWorkbook singlexssfwk = new XSSFWorkbook();
XSSFWorkbook singlexssfwk = new XSSFWorkbook();
Create Sheet ,Row ,column
singlexssfwk.CreateSheet("DataSource");
XSSFSheet xssfsheet = (XSSFSheet)singlexssfwk.GetSheetAt( index );
//建立行
XSSFRow xssfrow1 = (XSSFRow)xssfsheet.CreateRow( rowIndex );
//設定單元格內容
xssfrow1.CreateCell( columnIndex1 ).SetCellValue("Name");
xssfrow1.CreateCell( columnIndex2 ).SetCellValue("Sex");
xssfrow1.CreateCell( columnIndex3 ).SetCellValue("Phone");
singlexssfwk.CreateSheet("DataSource");
XSSFSheet xssfsheet = (XSSFSheet)singlexssfwk.GetSheetAt( index );
//建立行
XSSFRow xssfrow1 = (XSSFRow)xssfsheet.CreateRow( rowIndex );
//設定單元格內容
xssfrow1.CreateCell( columnIndex1 ).SetCellValue("Name");
xssfrow1.CreateCell( columnIndex2 ).SetCellValue("Sex");
xssfrow1.CreateCell( columnIndex3 ).SetCellValue("Phone");
SQL server : SqlDataReader
SqlConnection conn = new SqlConnection();
conn.ConnectionString = strConn;
conn.Open();
string sSQL = $@"SELECT * FROM Student";
SqlCommand mySqlCmd = new SqlCommand(sSQL, conn);
SqlDataReader reader =mySqlCmd.ExecuteReader()
SqlConnection conn = new SqlConnection();
conn.ConnectionString = strConn;
conn.Open();
string sSQL = $@"SELECT * FROM Student";
SqlCommand mySqlCmd = new SqlCommand(sSQL, conn);
SqlDataReader reader =mySqlCmd.ExecuteReader()
while (reader.Read())
{
//建立行
xssfrow1 = (XSSFRow)xssfsheet.CreateRow(i++);//.GetRow(1);
//設定單元格內容
xssfrow1.CreateCell(0).SetCellValue(reader["Name"].ToString());
xssfrow1.CreateCell(1).SetCellValue(reader["Sex"].ToString());
xssfrow1.CreateCell(2).SetCellValue(reader["Phone"].ToString());
}
如需設定欄位樣式
using NPOI.SS.UserModel;
ICellStyle style = singlexssfwk.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
style.FillPattern = FillPattern.SolidForeground;
//xssfrow1.Cells.ForEach(x => x.CellStyle = style); 一整行都要設定
xssfrow1.GetCell(0).CellStyle = style
using NPOI.SS.UserModel;
ICellStyle style = singlexssfwk.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
style.FillPattern = FillPattern.SolidForeground;
//xssfrow1.Cells.ForEach(x => x.CellStyle = style); 一整行都要設定
xssfrow1.GetCell(0).CellStyle = style
將檔案寫入指定路徑
SaveFileDialog savedialog = new SaveFileDialog(); //彈出讓使用者選擇excel儲存路徑的視窗
savedialog.Filter = " excel files(*.xlsx)|*.xlsx|All files(*.*)|*.*";
savedialog.RestoreDirectory = true;
savedialog.FileName = string.Format("Student List");
if (savedialog.ShowDialog() == DialogResult.OK)
{
//newsavefilepath是excel的儲存路徑
newsavefilepath = savedialog.FileName.ToString().Trim();
using (FileStream newfs = new FileStream(newsavefilepath, FileMode.Create, FileAccess.ReadWrite))
{
singlexssfwk.Write(newfs); //將生成的excel寫入使用者選擇儲存的檔案路徑中
newfs.Close();
success = true;
}
}
SaveFileDialog savedialog = new SaveFileDialog(); //彈出讓使用者選擇excel儲存路徑的視窗
savedialog.Filter = " excel files(*.xlsx)|*.xlsx|All files(*.*)|*.*";
savedialog.RestoreDirectory = true;
savedialog.FileName = string.Format("Student List");
if (savedialog.ShowDialog() == DialogResult.OK)
{
//newsavefilepath是excel的儲存路徑
newsavefilepath = savedialog.FileName.ToString().Trim();
using (FileStream newfs = new FileStream(newsavefilepath, FileMode.Create, FileAccess.ReadWrite))
{
singlexssfwk.Write(newfs); //將生成的excel寫入使用者選擇儲存的檔案路徑中
newfs.Close();
success = true;
}
}