EPPLUS 折り返して全体を表示 セルに合わせて縮小

折り返して全体を表示
sheet.Cells["A1"].Value = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
sheet.Cells["A1"].Style.WrapText = true;

セルに合わせて縮小
sheet.Cells["A3"].Value = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
sheet.Cells["A3"].Style.ShrinkToFit = true;

FireFox しばらく未接続語、タブもしくはurlを変更しても新規ページが表示されない

FireFox しばらく未接続語、タブもしくはurlを変更しても新規ページが表示されない

1. アドレスバー に about:config と入力し、Enter キーを押します。
   
   • about:config の "動作保証対象外になります！" という警告ページが表示されます。危険性を承知の上で使用する をクリックし、about:config ページを開いてください。
2. 設定リスト内で右クリック して 新規作成 を選択し、真偽値 を選択してください。
3. 新しい 真偽値の設定名 フィールドに network.dns.disablePrefetch と入力し、OK ボタンをクリックしてください。
4. 次に、true を選択して値を設定し、OK ボタンをクリックしてください。 

EPPLUS 数字フォーマット マイナスなら赤字で編集を行う

EPPLUS 数字フォーマット　マイナスなら赤字で編集を行う

sheet.Column(10).Style.Numberformat.Format = "#,##0; [red]-#,##0";

 通常は #,##0        色指定無し
[red]-#,##0           マイナスなら赤で編集 数字3桁でカンマ編集

C# font 複数の書式を設定する場合

C# font 複数の書式を設定する場合

// 上の例に加え、斜体のスタイルを設定する
 this.textBox1.Font = new Font("Arial", 12, FontStyle.Bold | FontStyle.Italic);

FontStyle を　|　で結合する

Oracle Sqlplusで余計な出力をしない方法

set echo off
set linesize 1000
set pagesize 0
set trimspool on
set feedback off
set colsep ','
spool c:\temp\tab.csv
select * from tab;
spool off

EPPlusのグルーピング方法

EPPlusのグルーピング方法

//明細グルーピング編集
sheet.Row(vY).OutlineLevel = 2;
sheet.Row(vY).Collapsed = true;

//合計グルーピング編集
sheet.Row(vY).OutlineLevel = 1;

Level = 1を総合計
Level = 2を担当者合計に割当

C# key break処理 キーブレイク処理

C#でキーブレイク処理をすることになった。
COBOLの時は１件読込み後、キーブレイク変数をセットとして
perform untilで回していたけど、C#の場合は、LINQを使った方がきれいに書ける。

            //最初は月度でブレイク
            var yearMonths = items.GroupBy(x => x.月度);

            foreach (var ym in yearMonths) {

                //シート追加＆ヘッダ処理
                if (ym.Key == pic売上年月_開始.SelectedDate.Value.ToString("yyyyMM")) {   //最初の月度ならシート名を編集
                    sheet.Name = ym.Key.Substring(0, 4) + "年" + ym.Key.Substring(4, 2) + "月";
                }
                else {
                    package.Workbook.Worksheets.Add(ym.Key.Substring(0, 4) + "年" + ym.Key.Substring(4, 2) + "月");
                    vSt++;
                    sheet = package.Workbook.Worksheets[vSt];
                }

                sheet.Cells["F1"].Value = ym.Key.Substring(0,4) + " 年 " + ym.Key.Substring(4,2) + " + 月現在";
                sheet.Cells["P1"].Value = DateTime.Now.ToString("　　　　yyyy/MM/dd HH:mm:ss");

                //列Aから列Eまで非表示
                sheet.Column(1).Hidden = true;
                sheet.Column(2).Hidden = true;
                sheet.Column(3).Hidden = true;
                sheet.Column(4).Hidden = true;
                sheet.Column(5).Hidden = true;

                sheet.Column(7).Hidden = true;
                sheet.Column(8).Hidden = true;
                sheet.Column(9).Hidden = true;

                //ヘッダー項目編集
                sheet.Cells["F1"].Value = DateTime.Today.ToString("yyyy 年 MM月現在");
                sheet.Cells["K1"].Value = "　　＊＊＊　担当者別売上統計表　＊＊＊";
                sheet.Cells["P1"].Value = DateTime.Now.ToString("　　　　yyyy/MM/dd HH:mm:ss");

                sheet.Cells["A3"].Value = "年月";
                sheet.Cells["B3"].Value = "部門";
                sheet.Cells["C3"].Value = "部門名";
                sheet.Cells["D3"].Value = "課";
                sheet.Cells["E3"].Value = "課名";
                sheet.Cells["F3"].Value = " ｺｰﾄﾞ担当者名";
                sheet.Cells["G3"].Value = "担当者名";
                sheet.Cells["H3"].Value = "得意先";
                sheet.Cells["I3"].Value = "得意先名";
                sheet.Cells["J3"].Value = "純売上";
                sheet.Cells["K3"].Value = "粗利";
                sheet.Cells["L3"].Value = "粗利率";
                sheet.Cells["M3"].Value = "売上";
                sheet.Cells["N3"].Value = "返品";
                sheet.Cells["O3"].Value = "返品率";
                sheet.Cells["P3"].Value = "値引";
                sheet.Cells["Q3"].Value = "値引率";
                sheet.Cells["R3"].Value = "売上目標";
                sheet.Cells["S3"].Value = "達成率";

                sheet.Cells["K1"].Style.Font.SetFromFont(new Font("MS Mincho", 16, FontStyle.Bold));

                sheet.Cells["A3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["B3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["C3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["D3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["E3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["F3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["G3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["H3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["I3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["J3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["K3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["L3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["M3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["N3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["O3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["P3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["Q3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["R3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                sheet.Cells["S3"].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));

                sheet.Column(06).Width = 30;    //F
                sheet.Column(11).Width = 16;    //J
                sheet.Column(12).Width = 16;    //K
                sheet.Column(13).Width = 7.5;   //L
                sheet.Column(14).Width = 16;    //M
                sheet.Column(15).Width = 16;    //N
                sheet.Column(16).Width = 7.5;   //O
                sheet.Column(17).Width = 16;    //P
                sheet.Column(18).Width = 7.5;   //Q
                sheet.Column(19).Width = 7.5;   //R

                //合計初期化
                tl1純売上 = 0;
                tl1粗利 = 0;
                tl1売上 = 0;
                tl1返品 = 0;
                tl1値引 = 0;
                tl1売上目標 = 0;

                //自社他社区分のヘッダ編集
                vY = 4;

                //自社他社区分でブレイク
                var v自社他社s = ym.GroupBy(x => x.自社他社区分);

                foreach (var v自社他社 in v自社他社s) {

                    //ヘッダ編集
                    if (v自社他社.Key == "2") {
                        sheet.Cells[vY, 06].Value = "【区分 一般売上】";
                    }
                    else {
                        sheet.Cells[vY, 06].Value = "【区分 社内売上】";
                    }
                    sheet.Cells[vY, 06].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                    vY++;

                    //初期化
                    tl2純売上 = 0;
                    tl2粗利 = 0;
                    tl2売上 = 0;
                    tl2返品 = 0;
                    tl2値引 = 0;
                    tl2売上目標 = 0;

                    //部門でブレイク
                    var v部門s = v自社他社.GroupBy(x => x.部門コード);

                    foreach (var v部門 in v部門s) {

                        var s1 = items.Where(x => x.月度 == ym.Key
                                               && x.自社他社区分 == v自社他社.Key
                                               && x.部門コード == v部門.Key).FirstOrDefault();

                        //ヘッダ編集
                        sheet.Cells[vY, 06].Value = s1.部門コード + " " + s1.部門名;
                        sheet.Cells[vY, 06].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));
                        vY++;

                        //初期化
                        tl3純売上 = 0;
                        tl3粗利 = 0;
                        tl3売上 = 0;
                        tl3返品 = 0;
                        tl3値引 = 0;
                        tl3売上目標 = 0;

                        //課でブレイク
                        var v課s = v部門.GroupBy(x => x.課コード);

                        foreach (var v課 in v課s) {

                            var s2 = items.Where(x => x.月度 == ym.Key
                                                   && x.自社他社区分 == v自社他社.Key
                                                   && x.部門コード == v部門.Key
                                                   && x.課コード == v課.Key).FirstOrDefault();

                            //初期化
                            tl4純売上 = 0;
                            tl4粗利   = 0;
                            tl4売上   = 0;
                            tl4返品   = 0;
                            tl4値引   = 0;
                            tl4売上目標 = 0;

                            //担当者でブレイク
                            var v担当者s = v課.GroupBy(x => x.担当者コード);

                            foreach (var v担当者 in v担当者s) {

                                var s3 = items.Where(x => x.月度 == ym.Key
                                                       && x.自社他社区分 == v自社他社.Key
                                                       && x.部門コード == v部門.Key
                                                       && x.課コード == v課.Key).FirstOrDefault();

                                //初期化
                                tl5純売上 = 0;
                                tl5粗利 = 0;
                                tl5売上 = 0;
                                tl5返品 = 0;
                                tl5値引 = 0;

                                //明細出力
                                var recs = items.Where(x => x.月度 == ym.Key
                                                        && x.自社他社区分 == v自社他社.Key
                                                        && x.部門コード == v部門.Key
                                                        && x.課コード == v課.Key
                                                        && x.担当者コード == v担当者.Key);

                                foreach (var rec in recs) {
                                    //明細編集
                                    v純売上 = (rec.売上金額計 + rec.返品金額計);
                                    v粗利率 = func率計算(rec.粗利金額計, v純売上);
                                    v返品率 = func率計算(rec.返品金額計, rec.売上金額計);
                                    v値引率 = func率計算(rec.値引金額計, v純売上);

                                    sheet.Cells[vY, 01].Value = rec.月度;
                                    sheet.Cells[vY, 02].Value = rec.部門コード;
                                    sheet.Cells[vY, 03].Value = rec.部門名;
                                    sheet.Cells[vY, 04].Value = rec.課コード;
                                    sheet.Cells[vY, 05].Value = rec.課名;
                                    sheet.Cells[vY, 06].Value = "    " + rec.得意先コード + " " + rec.得意先名;
                                    sheet.Cells[vY, 07].Value = rec.担当者名;
                                    sheet.Cells[vY, 08].Value = rec.得意先コード;
                                    sheet.Cells[vY, 09].Value = rec.得意先名;

                                    sheet.Cells[vY, 10].Value = v純売上;
                                    sheet.Cells[vY, 11].Value = rec.粗利金額計;
                                    sheet.Cells[vY, 12].Value = v粗利率;
                                    sheet.Cells[vY, 13].Value = rec.売上金額計;
                                    sheet.Cells[vY, 14].Value = rec.返品金額計;
                                    sheet.Cells[vY, 15].Value = v返品率;
                                    sheet.Cells[vY, 16].Value = rec.値引金額計;
                                    sheet.Cells[vY, 17].Value = v値引率;
                                    sheet.Cells[vY, 18].Value = "";
                                    sheet.Cells[vY, 19].Value = "";

                                    //集計計算
                                    tl5純売上 += v純売上;
                                    tl5粗利 += rec.粗利金額計;
                                    tl5売上 += rec.売上金額計;
                                    tl5返品 += rec.返品金額計;
                                    tl5値引 += rec.値引金額計;

                                    //セーブ項目
                                    sv売上目標 = rec.目標金額計;
                                    sv担当者Cd = rec.担当者コード;
                                    sv担当者名 = rec.担当者名;

                                    vY++;
                                }

                                //集計計算
                                tl4純売上 += tl5純売上;
                                tl4粗利 += tl5粗利;
                                tl4売上 += tl5売上;
                                tl4返品 += tl5返品;
                                tl4値引 += tl5値引;
                                tl4売上目標 += sv売上目標;

                                //担当者合計出力
                                set合計Row(ref sheet, vY, "     * " + sv担当者Cd + " " + sv担当者名,
                                        tl5純売上,
                                        tl5粗利,
                                        tl5売上,
                                        tl5返品,
                                        tl5値引,
                                        sv売上目標);
                                vY++;
                            }

                            //集計計算
                            tl3純売上 += tl4純売上;
                            tl3粗利   += tl4粗利;
                            tl3売上   += tl4売上;
                            tl3返品   += tl4返品;
                            tl3値引   += tl4値引;
                            tl3売上目標 += tl4売上目標;

                            //課合計出力
                            set合計Row(ref sheet, vY, "     * " + s2.課コード + " " + s2.課名,
                                    tl4純売上,
                                    tl4粗利,
                                    tl4売上,
                                    tl4返品,
                                    tl4値引,
                                    tl4売上目標);
                            vY++;
                        }

                        //集計計算
                        tl2純売上 += tl3純売上;
                        tl2粗利   += tl3粗利;
                        tl2売上   += tl3売上;
                        tl2返品   += tl3返品;
                        tl2値引   += tl3値引;
                        tl2売上目標 += tl3売上目標;

                        //部門合計出力
                        set合計Row(ref sheet, vY, "    ** " + s1.部門コード + " " + s1.部門名,
                                tl3純売上,
                                tl3粗利,
                                tl3売上,
                                tl3返品,
                                tl3値引,
                                tl3売上目標);
                        vY++;
                    }

                    //集計計算
                    tl1純売上 += tl2純売上;
                    tl1粗利   += tl2粗利;
                    tl1売上   += tl2売上;
                    tl1返品   += tl2返品;
                    tl1値引   += tl2値引;
                    tl1売上目標 += tl2売上目標;

                    //自社他社区分合計出力
                    set合計Row(ref sheet, vY, "   *** 区分計",
                            tl2純売上,
                            tl2粗利,
                            tl2売上,
                            tl2返品,
                            tl2値引,
                            tl2売上目標);
                    vY++;
                }

                //月度合計出力
                set合計Row(ref sheet, vY, "  **** 総合計",
                        tl1純売上,
                        tl1粗利,
                        tl1売上,
                        tl1返品,
                        tl1値引,
                        tl1売上目標);
            }

            package.Save();
        }
    }

    private void set合計Row(ref ExcelWorksheet isheet,
                            int iY,
                            string i計名,
                            decimal i純売上,
                            decimal i粗利,
                            decimal i売上,
                            decimal i返品,
                            decimal i値引,
                            decimal i売上目標) {

        isheet.Cells[iY, 06].Value = i計名;
        isheet.Cells[iY, 06].Style.Font.SetFromFont(new Font("MS Mincho", 11, FontStyle.Bold));

        isheet.Cells[iY, 10].Value = i純売上;
        isheet.Cells[iY, 11].Value = i粗利;
        isheet.Cells[iY, 12].Value = func率計算(i粗利, i純売上);
        isheet.Cells[iY, 13].Value = i売上;
        isheet.Cells[iY, 14].Value = i返品;
        isheet.Cells[iY, 15].Value = func率計算(i返品, i売上);
        isheet.Cells[iY, 16].Value = i値引;
        isheet.Cells[iY, 17].Value = func率計算(i値引, i純売上);
        isheet.Cells[iY, 18].Value = i売上目標;
        isheet.Cells[iY, 19].Value = func率計算(i純売上, i売上目標);
    }
    private decimal func率計算(decimal i1, decimal i2) {
        decimal vCal = 0;
        try {
            vCal = (i1 / i2) * 100;
        }
        catch { vCal = 0; }

        return vCal;
    }

Excel列(お借りしました)

列番号	列名
1	A
2	B
3	C
4	D
5	E
6	F
7	G
8	H
9	I
10	J
11	K
12	L
13	M
14	N
15	O
16	P
17	Q
18	R
19	S
20	T
21	U
22	V
23	W
24	X
25	Y
26	Z
27	AA
28	AB
29	AC
30	AD
31	AE
32	AF
33	AG
34	AH
35	AI
36	AJ
37	AK
38	AL
39	AM
40	AN
41	AO
42	AP
43	AQ
44	AR
45	AS
46	AT
47	AU
48	AV
49	AW
50	AX
51	AY
52	AZ
53	BA
54	BB
55	BC
56	BD
57	BE
58	BF
59	BG
60	BH
61	BI
62	BJ
63	BK
64	BL
65	BM
66	BN
67	BO
68	BP
69	BQ
70	BR
71	BS
72	BT
73	BU
74	BV
75	BW
76	BX
77	BY
78	BZ
79	CA
80	CB
81	CC
82	CD
83	CE
84	CF
85	CG
86	CH
87	CI
88	CJ
89	CK
90	CL
91	CM
92	CN
93	CO
94	CP
95	CQ
96	CR
97	CS
98	CT
99	CU
100	CV
101	CW
102	CX
103	CY
104	CZ
105	DA
106	DB
107	DC
108	DD
109	DE
110	DF
111	DG
112	DH
113	DI
114	DJ
115	DK
116	DL
117	DM
118	DN
119	DO
120	DP
121	DQ
122	DR
123	DS
124	DT
125	DU
126	DV
127	DW
128	DX
129	DY
130	DZ
131	EA
132	EB
133	EC
134	ED
135	EE
136	EF
137	EG
138	EH
139	EI
140	EJ
141	EK
142	EL
143	EM
144	EN
145	EO
146	EP
147	EQ
148	ER
149	ES
150	ET
151	EU
152	EV
153	EW
154	EX
155	EY
156	EZ
157	FA
158	FB
159	FC
160	FD
161	FE
162	FF
163	FG
164	FH
165	FI
166	FJ
167	FK
168	FL
169	FM
170	FN
171	FO
172	FP
173	FQ
174	FR
175	FS
176	FT
177	FU
178	FV
179	FW
180	FX
181	FY
182	FZ
183	GA
184	GB
185	GC
186	GD
187	GE
188	GF
189	GG
190	GH
191	GI
192	GJ
193	GK
194	GL
195	GM
196	GN
197	GO
198	GP
199	GQ
200	GR
201	GS
202	GT
203	GU
204	GV
205	GW
206	GX
207	GY
208	GZ
209	HA
210	HB
211	HC
212	HD
213	HE
214	HF
215	HG
216	HH
217	HI
218	HJ
219	HK
220	HL
221	HM
222	HN
223	HO
224	HP
225	HQ
226	HR
227	HS
228	HT
229	HU
230	HV
231	HW
232	HX
233	HY
234	HZ
235	IA
236	IB
237	IC
238	ID
239	IE
240	IF
241	IG
242	IH
243	II
244	IJ
245	IK
246	IL
247	IM
248	IN
249	IO
250	IP
251	IQ
252	IR
253	IS
254	IT
255	IU
256	IV
257	IW
258	IX
259	IY
260	IZ
261	JA
262	JB
263	JC
264	JD
265	JE
266	JF
267	JG
268	JH
269	JI
270	JJ
271	JK
272	JL
273	JM
274	JN
275	JO
276	JP
277	JQ
278	JR
279	JS
280	JT
281	JU
282	JV
283	JW
284	JX
285	JY
286	JZ
287	KA
288	KB
289	KC
290	KD
291	KE
292	KF
293	KG
294	KH
295	KI
296	KJ
297	KK
298	KL
299	KM
300	KN
301	KO
302	KP
303	KQ
304	KR
305	KS
306	KT
307	KU
308	KV
309	KW
310	KX
311	KY
312	KZ
313	LA
314	LB
315	LC
316	LD
317	LE
318	LF
319	LG
320	LH
321	LI
322	LJ
323	LK
324	LL
325	LM
326	LN
327	LO
328	LP
329	LQ
330	LR
331	LS
332	LT
333	LU
334	LV
335	LW
336	LX
337	LY
338	LZ
339	MA
340	MB
341	MC
342	MD
343	ME
344	MF
345	MG
346	MH
347	MI
348	MJ
349	MK
350	MM
351	MM
352	MN
353	MO
354	MP
355	MQ
356	MR
357	MS
358	MT
359	MU
360	MV
361	MW
362	MX
363	MY
364	MZ

元ネタ
http://d.hatena.ne.jp/dentrok/20100810/1281472075