前言:
最近收到任務是公司需要一份新報表,掛到公司內部的網頁上,供使用者查詢,然而這份報表所要呈現的資料並無法直接以Sql語法撈出,雖然這部分不排除是我Sql功力不足,但無論如何,問題來了還是得解決,只好另闢蹊徑,想把要用的資料撈出來後再自行整理成使用者需要的樣式。
問題:
這次的主要問題簡而言之就是從SQL獲取「庫存資料」及「異動資料」,從這兩個去整理出目前庫存的庫齡,最一開始我想到的解法便是:當使用者選完它要的搜尋條件後,按下查詢會在網頁上出現「庫存資料」及「異動資料」的表格,當使用者按下【下載成Excel】的按鈕後,再把他們整理成三個Sheet,依序為「庫存資料」「異動資料」及「庫齡報表」
解決方法:
這個問題並不難解決,只要把NPOI這個套件拿來使用就好(也順手介紹一下NPOI的一些基本使用方法) //NPOI要使用的工作簿,工作表,列,儲存格都需要先被「創造」後才能使用 HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //建立活頁簿 ISheet ws2 = hssfworkbook.CreateSheet("異動明細"); //建立sheet ISheet ws3 = hssfworkbook.CreateSheet("庫存資料"); ISheet ws1 = hssfworkbook.CreateSheet("庫存統計表");
創立好各個工作表(Sheet)後就可以開始把內容寫入工作表內,而相信眼尖的朋友也已經發現剛剛說要先創造列,卻沒提到欄,那欄要創造嗎?讓我們看一下以下的程式碼。ws1.CreateRow(0);//第一列被創造 ws1.GetRow(0).CreateCell(0).SetCellValue("廠別");//獲取第一列,並創造第一列的第一個儲存格 //(即第一欄,excel的A1儲存格),並賦值。 ws1.GetRow(0).GetCell(0).CellStyle = headerStyle;
因為報表要好看,所以多了ws1.GetRow(0).GetCell(0).CellStyle = headerStyle;
這行來設置儲存格的樣式
至於headerStyle要怎麼設置就要在前面先設好它的內容,方式個人覺得有點像Css的方式ICellStyle headerStyle = hssfworkbook.CreateCellStyle(); IFont headerfont = hssfworkbook.CreateFont(); headerStyle.Alignment = HorizontalAlignment.Center; //水平置中 headerStyle.VerticalAlignment = VerticalAlignment.Center; //垂直置中 headerfont.FontName = "微軟正黑體"; headerfont.FontHeightInPoints = 12; headerfont.Boldweight = (short)FontBoldWeight.Bold;//粗體 headerfont.Color = NPOI.HSSF.Util.HSSFColor.White.Index; headerStyle.FillPattern = FillPattern.SolidForeground; headerStyle.FillForegroundColor = IndexedColors.Black.Index; headerStyle.SetFont(headerfont);
這些應該就是NPOI的基本介紹了,至於怎麼把資料填入就是各種for迴圈跟if判斷就不再贅述了!
下載下來就可以得到想要的報表了~(雖然我碼了幾乎整張圖)
延伸問題
然而這樣的做法卻被嫌棄「不夠直觀」,「我要在網頁上直接看到結果,我不要你的過程」等等,相信未來需要使用類似功能的人也會遇到類似的問題,希望先在網頁端就看一下這次撈取出的資料長甚麼樣子,再決定要不要下載,然而C# asp.net卻沒有可以對資料進行計算整理然後再貼到網頁的方法(也可能是我不會),苦思許久後,我決定直接複製,然後貼上,把剛剛要下載成excel的方法再做一次,但這次不下載,而是做完之後,直接像是上傳了一個檔案然後讀取,在把它們一行一行填入table內,然後再利用Css美化我的表格。
解決方法
`
HSSFSheet worksheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
int rowCount = worksheet.LastRowNum;
int colCount = worksheet.GetRow(0).LastCellNum;
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
hw.Write("<style type='text/css'>");
hw.Write("table { border-collapse: collapse; width: 100%; }");
hw.Write("th ,td { text-align: left; padding: 12px; font-size:175%;}");
hw.Write("</style>");
hw.RenderBeginTag(HtmlTextWriterTag.Table);
hw.RenderBeginTag(HtmlTextWriterTag.Tr);
for (int j = 0; j < colCount; j++)
{
hw.RenderBeginTag(HtmlTextWriterTag.Th);
hw.Write(worksheet.GetRow(0).GetCell(j).ToString());
hw.RenderEndTag();
}
hw.RenderEndTag();
for (int i = 1; i <= rowCount; i++)
{
bool isSubtotal = i == clint_A + 1 || i == clint_H || i == clint_R;
bool isTotal = i == clint_R + 1;
string cssClass = "";
if (isSubtotal)
{
cssClass = "subtotal";
}
else if (isTotal)
{
cssClass = "total";
}
hw.RenderBeginTag(HtmlTextWriterTag.Tr);
for (int j = 0; j < colCount; j++)
{
if (worksheet.GetRow(i).GetCell(j) != null)
{
if (isSubtotal)
{
if (j != 0)
{
hw.AddAttribute("class", cssClass);
hw.RenderBeginTag(HtmlTextWriterTag.Th);
hw.Write(worksheet.GetRow(i).GetCell(j).ToString());
hw.RenderEndTag();
}
else
{
hw.AddAttribute("class", cssClass);
hw.AddStyleAttribute("font-size", "200%");
hw.AddAttribute("colspan", "2");
hw.RenderBeginTag(HtmlTextWriterTag.Th);
hw.Write(worksheet.GetRow(i).GetCell(j).ToString());
hw.RenderEndTag();
}
}else if (isTotal)
{
if (j != 0)
{
hw.AddAttribute("class", cssClass);
hw.RenderBeginTag(HtmlTextWriterTag.Th);
hw.Write(worksheet.GetRow(i).GetCell(j).ToString());
hw.RenderEndTag();
}
else
{
hw.AddAttribute("class", cssClass);
hw.AddStyleAttribute("font-size", "200%");
hw.AddAttribute("colspan", "2");
hw.RenderBeginTag(HtmlTextWriterTag.Th);
hw.Write(worksheet.GetRow(i).GetCell(j).ToString());
hw.RenderEndTag();
}
}
else
{
hw.RenderBeginTag(HtmlTextWriterTag.Td);
hw.Write(worksheet.GetRow(i).GetCell(j).ToString());
hw.RenderEndTag();
}
}
else
{
if (!isTotal && !isSubtotal)
{
hw.RenderBeginTag(HtmlTextWriterTag.Td);
worksheet.GetRow(i).CreateCell(j).SetCellValue("");
hw.Write(worksheet.GetRow(i).GetCell(j).ToString());
hw.RenderEndTag();
}
}
}
hw.RenderEndTag();
}
string total_num = worksheet.GetRow(clint_R + 1).GetCell(2).ToString();
lblTotal.Text = total_num;
hw.RenderEndTag();
hssfworkbook.Close();
Label1.Text = sw.ToString();
`
就像是這樣,把東西都做完後呢!再插入Label
這樣就可以完成我要的樣子了
如下圖,一樣打碼處理
因為前兩家工廠沒有被選到,所以只有一列,列出工廠名字,但完全沒有對應的客戶,故以灰色顯示
第三家工廠有被使用者選到,所以會有很多列,每一列都是C廠配上C1客戶,C2客戶等等已次類推
最下面的黑色則為所有有被選到的工廠的總計。
後記:
進公司三個多月從巨集要用的VB到SQL再到C# asp.net都是從0開始,邊做邊查邊學,英文奇差的我也因此看了很多英文的網站,但這次後來的延伸問題是真的沒查到,大部分都只查到怎麼轉成Excel(沒錯就是用NPOI),但這我已經會了呀~沒有辦法的情況下只能自己多繞幾條路用本文的方法來解決,特別發文,一來是為了避免自己忘記,二來也為了未來有跟我一樣的初心者,三來更是為了記錄自己的成長,所以就記錄了下來,我也明白這樣的方法感覺有點繞,但至少解決了目前的問題,也希望如果有大神路過此文,能指點我更棒的方法,先說聲感恩了!
NPOI這個套件
舊版的問題一堆
除非你能用新版的NPOI
不然關於報表
我還是比較推薦Crystal Reports或SSRS(SQL Server Reporting Services)
了解!感謝大大~馬上來google看看~
帳齡分析
庫齡分析
都可以用 Function Return Table 方式
先算出 balance (結存)
寫好 cusrsor loop 倒推最後進貨(庫齡)或出貨(帳齡)
寫好後::
直接下 SQL 去 SELECT * FROM TSMC_AR_Agging (@帳款基準日)
直接下 SQL 去 SELECT * FROM TSMC_Inv_Agging (@庫齡基準日)
需要細節請留言
有一點不明白大大的意思,目前SQL只會CTE/子查詢之類的,真的是去年11月入職,才邊做邊學,還非常的菜,不好意思。
但非常感謝大大建議,我會在假日google自我學習看看,真的很感謝您
公司內部人員最重要的公作
是釐清"管理需求"+"管理流程"
套用外部顧問提供技術
作出"公司管理系統"
SAP 做得到的事你也作得到
但事有件事很難
就是讓"帳款立沖/成本結算/會計報告"
不要為了方便就"彙總"
全部採明細立帳沖帳
這點
一般沒花大錢請顧問
是做不到
原因是沒人理你
花錢消災解噩夢
是一定要的