MFC Problems about write a List Control to an Excel file

Question

I am writing a function which aims to wirte the content of a List Control to an Excel file. But I find the the format of the output is not as I wish. My code is

{
    CString buff0, buff1, buff2;
    CString fileName = _T("d:\\test.xls");//
    CFile file(fileName, CFile::modeCreate | CFile::modeReadWrite | 
                   CFile::shareExclusive);
    file.Write("A\tB\tC\n", 56);
    int i = 0;
    int j = 0;
    j = m_list.GetItemCount();
    if (j > 0)
    {
        for (i = 0; i<j; i++)
        {
            buff0 = _T("0"); % only for test, should be m_list.GetItemText()
            buff1 = _T("1"); % for test
            buff2 = _T("2"); % for test
            CString msg;
            msg.Format(_T("%s\t%s\t%s\n"), buff0, buff1, buff2);%  output each line to Excel
            file.Write(msg, msg.GetLength());
            }
        }
    }

I find msg.Format(_T("%s\t%s\t%s\n"), buff0, buff1, buff2); is not executed as I wish. The output Excel file is like

But it should be 3 elements (0,1,2) in each line according to msg.Format(_T("%s\t%s\t%s\n"), buff0, buff1, buff2);

However file.Write("A\tB\tC\n", 56); is executed as wish.

Anyone knows what's the problem. Thanks very much!

Answer 1

You are writing to file in UTF-16. msg.GetLength() returns the number of wchar_t in the string, which is half of the total length of what's in the buffer (in this example). If you write L"12345\n" in this way, it may show as " 1 2 3" in ANSI, the rest of the string is lost.

In file.Write("A\tB\tC\n", 56) you assign an arbitrary number, 56, which is larger than the buffer, it happens to work.

You should write to file in ANSI, or change UTF-16 to UTF-8 to preserve Unicode. Example:

CStringA u8 = CW2A(_T("A\tB\tC\n"), CP_UTF8);
file.Write(u8, u8.GetLength());

for(...)
{
    buff0 = _T("0"); 
    buff1 = _T("1");
    buff2 = _T("2");
    CString msg;
    msg.Format(_T("%s\t%s\t%s\n"), 
            (LPCTSTR)buff0, (LPCTSTR)buff1, (LPCTSTR)buff2); 
    u8 = CW2A(msg, CP_UTF8);
    file.Write(u8, u8.GetLength());
}

Answer 2

There are excellent libraries that can write nice and feature-rich xlsx Excel files. Like: http://sourceforge.net/p/simplexlsx/wiki/Home/