ikcesRpt相关知识总结--excel

1.获取程序启动路径:

string strpa=Application.StartupPath;

2.浏览文件:

            OpenFileDialog openFile = new OpenFileDialog();
            openFile.Filter = "excel file(*.xls)|*.xls";
            if (openFile.ShowDialog() == DialogResult.OK)
            {
                txtFilename1.Text = openFile.FileName.ToString().Trim();
            }

3.生成保存文件名获得保存路径:

string strOutPath = "";
            SaveFileDialog saveFD = new SaveFileDialog();
            saveFD.FileName = strCustomer + " ikces Rpt " + DateTime.Now.ToString("yyyyMMdd") + ".XLS";

            if (saveFD.ShowDialog() == DialogResult.OK)
            {
                strOutPath = saveFD.FileName;
            }

4.Generate Rpt的方法:

a.            Excel.Application m_objExcel = null;
            Excel._Workbook m_objBook = null;
            Excel._Worksheet m_objSheet = null;
            string objectpath = strpa + "\\ikcesTemplate.xls";

　　　　string sourcepath = @"ExcessTemplate.xls";
            string strModelType = "";

            if (!File.Exists(objectpath))
            {
                File.Copy(sourcepath, objectpath);
            }

b.                m_objExcel = new Excel.Application();

                m_objBook = m_objExcel.Workbooks.Open(objectpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                m_objExcel.DisplayAlerts = false;

                m_objExcel.Visible = true;//excel生成过程可视;

c.由于要求按datatable中model名产生每个sheet,且每个sheet都会有一行模板,且同一个sheet中按Category不同模板行会重复,所以Excel中Sheet[1]被当作模板,定制其中的行;当取出datatable的model个数后:

　　　　　　　m_objSheet = (Excel.Worksheet)m_objBook.Worksheets.get_Item(1);
                    m_objSheet.Copy(Type.Missing, m_objBook.Sheets[1]);//在sheet[1]之后copy并生成新sheet;

                    m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet;//若不设此为ActiveSheet,则会发现写的时候又跑到之前写的sheet中;

　　　　　　　m_objSheet.Name="name";//为sheet命名;

　　　　　　　m_objSheet.Cells[m, n]="要写入的数据"//写单元格就不停的run for循环了...

　　　　　　/////附如下对Range的一些操作:

　　　　　　m_objSheet.get_Range("A" + 4, "A" + (k + 3)).MergeCells = true;
　　　　　　m_objSheet.get_Range("A" + 4, "A" + (k + 3)).Interior.ColorIndex = 19;
　　　　　　m_objSheet.get_Range("A3", "G3").Font.Size = 12;
　　　　　　m_objSheet.get_Range("A3", "G3").Font.Bold = true;
　　　　　　m_objSheet.get_Range("L4", "L" + m).Font.ColorIndex = 46;
　　　　　　m_objSheet.get_Range("L4", "L" + m).Font.Name ="Arial";
　　　　　　m_objSheet.get_Range("A3", "G3").Borders.LineStyle = 1;///单元格内网格线;
　　　　　　m_objSheet.get_Range("A3", "G3").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
　　　　　　m_objSheet.get_Range("A3", "G3").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
　　　　　　m_objSheet.get_Range("A3", "G3").Columns.AutoFit();

d.同sheet内模板行copy:

                    Excel.Range range = m_objSheet.get_Range("A3", "U3");
                    Excel.Range range1 = m_objSheet.get_Range("A" + n, "U" + n);///获取要写入目标范围;
                    range.Copy(range1);

e.删除sheet[1]的模板行,

                m_objSheet = (Excel.Worksheet)m_objBook.Worksheets.get_Item(1);
                m_objSheet.Activate();///用这中激活方法反而OK了......与上面的不同;
                m_objSheet.Name = "Summary";
                Excel.Range rangeSpe = m_objSheet.get_Range("A3","U3");
                //rangeSpe.Select();///据说要用这段,但用了提示出错:没这个方法,不用反而好了;
                rangeSpe.EntireRow.Delete(Type.Missing);

f.保存并返回路径:

 　　　　　m_objBook.SaveAs(strOutPath , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                return strOutPath ;

g. finally:

　　　　　  m_objExcel.Workbooks.Close();

                m_objExcel.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);

                System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);

                m_objBook = null;

                m_objExcel = null;

                GC.Collect();