Excel文件出力

Excel文件出力
  1         #region-----エラーデータをEXCELに出力-----
  2         /// <summary>
  3         /// エラーデータをEXCELに出力
  4         /// </summary>
  5         /// <param name="dt"></param>
  6         /// <param name="fileName"></param>
  7         private void ErrorDataExcelOut(DataTable dt, string fileName)
  8         {
  9             //Excel対象
 10             myExcel.Application excel = new Excel.Application();
 11             excel.Application.EnableEvents = false;
 12 
 13             //Workbook対象
 14             myExcel.Workbook workbook = excel.Application.Workbooks.Add(true);
 15             workbook = excel.Workbooks._Open(fileName, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 16 
 17             //Worksheet対象
 18             myExcel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
 19             //Worksheet最大化
 20             excel.ActiveWindow.WindowState = myExcel.XlWindowState.xlMaximized;
 21             //sheet名前
 22             worksheet.Name = "エラーリストサンプル";
 23 
 24             myExcel.Range xlRange;
 25             //見出し出力
 26             xlRange = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]);
 27             xlRange.Value2 = "従業員NO";
 28 
 29             xlRange = worksheet.get_Range(worksheet.Cells[1, 2], worksheet.Cells[1, 2]);
 30             xlRange.Value2 = "氏名";
 31 
 32             xlRange = worksheet.get_Range(worksheet.Cells[1, 3], worksheet.Cells[1, 3]);
 33             xlRange.Value2 = "所属コード";
 34 
 35             xlRange = worksheet.get_Range(worksheet.Cells[1, 4], worksheet.Cells[1, 4]);
 36             xlRange.Value2 = "所属名";
 37 
 38             xlRange = worksheet.get_Range(worksheet.Cells[1, 5], worksheet.Cells[1, 5]);
 39             xlRange.Value2 = "生年月日";
 40 
 41             xlRange = worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[1, 6]);
 42             xlRange.Value2 = "退社年月日";
 43 
 44             xlRange = worksheet.get_Range(worksheet.Cells[1, 7], worksheet.Cells[1, 7]);
 45             xlRange.Value2 = "エラー";
 46 
 47             //線
 48             xlRange = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]);
 49             xlRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
 50 
 51             //並べる方法
 52             //見出し
 53             xlRange = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]);
 54             xlRange.HorizontalAlignment = myExcel.XlHAlign.xlHAlignLeft;
 55 
 56             //データ
 57             //従業員NO
 58             xlRange = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 1, 1]);
 59             xlRange.HorizontalAlignment = myExcel.XlHAlign.xlHAlignRight;
 60 
 61             //氏名
 62             xlRange = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[dt.Rows.Count + 1, 2]);
 63             xlRange.HorizontalAlignment = myExcel.XlHAlign.xlHAlignLeft;
 64 
 65             //所属コード
 66             xlRange = worksheet.get_Range(worksheet.Cells[2, 3], worksheet.Cells[dt.Rows.Count + 1, 3]);
 67             xlRange.HorizontalAlignment = myExcel.XlHAlign.xlHAlignRight;
 68 
 69             //所属名
 70             xlRange = worksheet.get_Range(worksheet.Cells[2, 4], worksheet.Cells[dt.Rows.Count + 1, 4]);
 71             xlRange.HorizontalAlignment = myExcel.XlHAlign.xlHAlignLeft;
 72 
 73             //生年月日,退社年月日
 74             xlRange = worksheet.get_Range(worksheet.Cells[2, 5], worksheet.Cells[dt.Rows.Count + 1, 6]);
 75             xlRange.HorizontalAlignment = myExcel.XlHAlign.xlHAlignRight;
 76 
 77             //エラー
 78             xlRange = worksheet.get_Range(worksheet.Cells[2, 7], worksheet.Cells[dt.Rows.Count + 1, 7]);
 79             xlRange.HorizontalAlignment = myExcel.XlHAlign.xlHAlignLeft;
 80 
 81             //明細の設定
 82             Object[,] DetailData = new Object[dt.Rows.Count, dt.Columns.Count];
 83 
 84             for (int i = 0; i < dt.Rows.Count; i++)
 85             {
 86                 for (int j = 0; j < dt.Columns.Count; j++)
 87                 {
 88                     DetailData[i, j] = dt.Rows[i][j].ToString();
 89                 }
 90             }
 91 
 92             xlRange = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]);
 93             xlRange.Font.Size = 11;
 94             xlRange.RowHeight = 11.25;
 95             xlRange.Font.Name = "MS Pゴシック";
 96             xlRange.EntireColumn.AutoFit();
 97 
 98             if (dt.Rows.Count > 0)
 99             {
100                 xlRange = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 1, dt.Columns.Count]);
101                 xlRange.Value2 = DetailData;
102                 xlRange.Font.Size = 11;
103                 xlRange.RowHeight = 11.25;
104                 xlRange.Font.Name = "MS Pゴシック";
105                 xlRange.EntireColumn.AutoFit();
106             }
107 
108             //ファイルの保存
109             int FormatNum;
110             string Version;
111             Version = excel.Version;
112             //Excel 97-2003
113             if (Convert.ToDouble(Version) < 12)
114             {
115                 FormatNum = -4143;
116             }
117             //Excel 2007以上
118             else
119             {
120                 FormatNum = 56;
121             }
122 
123             excel.DisplayAlerts = false;
124 
125             workbook.SaveAs(fileName, FormatNum, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
126 
127             workbook.Close(true, fileName, false);
128 
129             System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
130             worksheet = null;
131             System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
132             workbook = null;
133             excel.Quit();
134             System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
135             excel = null;
136             System.GC.Collect();
137         }
138         #endregion
View Code

 参考:https://blog.csdn.net/beyondqd/article/details/51580254(XlLineStyle 枚举 (Excel))

上一篇:makefile编译报错之missing separator解决


下一篇:leetcode 268. 缺失数字(Missing Number)