//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;
}
}