Example #1
0
        //string idBill, string idUser, string idCustomer, DateTime time, long subtractMoney, long totalMoney)
        public static bool ExportListViews2Excel(string sSheetName, string sPath, List<ListView> lv)
        {
            try
            {
                // Khởi động chtr Excell
                exApp = new COMExcel.Application();

                // Thêm file temp xls
                exBook = exApp.Workbooks.Add(
                          COMExcel.XlWBATemplate.xlWBATWorksheet);

                // Lấy sheet 1.
                COMExcel.Worksheet exSheet = (COMExcel.Worksheet)exBook.Worksheets[1];

                exSheet.Activate();
                exSheet.Name = sSheetName;

                List<int> list_iMaxLength = new List<int>(); //Gia tri max de so sanh AutoFit column

                int iRowFit = 1;
                int iColumnFit = 1;
                bool isNewMaxLength = true;

                int rowIndex = 0;

                foreach (ListView listView in lv)
                {
                    int[] listOldMaxLength = new int[list_iMaxLength.Count];
                    list_iMaxLength.CopyTo(listOldMaxLength);
                    list_iMaxLength.Clear();

                    for (int iColumn = 0; iColumn < listView.Columns.Count; iColumn++)
                    {
                        COMExcel.Range r = (COMExcel.Range)exSheet.Cells[rowIndex + 1, iColumn + 1];

                        r.Font.Bold = true;
                        r.Value2 = listView.Columns[iColumn].Text.ToString();
                        //r.BorderAround(COMExcel.XlLineStyle.xlContinuous, COMExcel.XlBorderWeight.xlThin, COMExcel.XlColorIndex.xlColorIndexAutomatic, 1);

                        if (iColumn < listOldMaxLength.Length && listView.Columns[iColumn].Text.Length < listOldMaxLength[iColumn])
                        {
                            list_iMaxLength.Add(listOldMaxLength[iColumn]);
                        }
                        else
                        {
                            list_iMaxLength.Add(listView.Columns[iColumn].Text.Length);

                            COMExcel.Range rFit = (COMExcel.Range)exSheet.Cells[rowIndex + 1, iColumn + 1];
                            rFit.Columns.AutoFit();
                        }
                    }

                    //rowIndex += 1;

                    for (int iColumn = 0; iColumn < listView.Columns.Count; iColumn++)
                    {
                        iRowFit = rowIndex + 1;
                        iColumnFit = iColumn + 1;

                        for (int iRow = rowIndex; iRow < listView.Items.Count + rowIndex; iRow++)
                        {
                            COMExcel.Range r = (COMExcel.Range)exSheet.Cells[iRow + 2, iColumn + 1];

                            r.Value2 = listView.Items[iRow - rowIndex].SubItems[iColumn].Text.ToString();
                            //r.BorderAround(COMExcel.XlLineStyle.xlContinuous, COMExcel.XlBorderWeight.xlThin, COMExcel.XlColorIndex.xlColorIndexAutomatic, 1);

                            if (listView.Items[iRow - rowIndex].SubItems[iColumn].Text.Length > list_iMaxLength[iColumn])
                            {
                                list_iMaxLength[iColumn] = listView.Items[iRow - rowIndex].SubItems[iColumn].Text.Length;

                                iRowFit = iRow + 2;
                                iColumnFit = iColumn + 1;

                                isNewMaxLength = true;
                            }
                        }

                        if (isNewMaxLength)
                        {
                            COMExcel.Range rFit = (COMExcel.Range)exSheet.Cells[iRowFit, iColumnFit];
                            rFit.Columns.AutoFit();

                            isNewMaxLength = false;
                        }
                    }

                    rowIndex += listView.Items.Count;
                    rowIndex += 1;
                }

                exApp.Visible = false;

                exBook.SaveAs(sPath, COMExcel.XlFileFormat.xlWorkbookNormal,
                                null, null, false, false,
                                COMExcel.XlSaveAsAccessMode.xlExclusive,
                                false, false, false, false, false);
                exBook.Close(false, false, false);
                exApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);

                return true;
            }
            catch
            {
                CloseExcel();

                return false;
            }
        }