如何在Excel中创建标题文本 [英] How to create header text in excel

问题描述

大家好



i希望在我的代码上创建标题文本我将我的访问表导出到excel中导出得很好但是标题文本没有到来，这里我不是使用任何datagridview组件



我尝试过：

Hi all

i want to create header text on my code im exporting my access table into excel its exporting very well but header text is not coming , here im not using any datagridview component

What I have tried:

string connectionString = null;
           string sql = null;
           string data = null;
           int i = 0;
           int j = 0;
           int iRowCnt = 0;

           Excel.Application xlApp;
           Excel.Workbook xlWorkBook;
           Excel.Worksheet xlWorkSheet;
           object misValue = System.Reflection.Missing.Value;

           xlApp = new Excel.Application();
           xlWorkBook = xlApp.Workbooks.Add(misValue);
           xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);


           connectionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
           cnn.ConnectionString = connectionString;
           cnn.Open();
           sql = "SELECT [FormNo] , [Date] as [Date],[TruckNo] as [TruckNo],[Office_Code] as [Office_Code],[Party_Code] as [Party_Code],[Party_Code1] as [Party_Code1],[Location] as [Location],[Supplier] as [Supplier],[Item] as [Item],[Invoice_no] as [Invoice_no],[Invoice_date] as [Invoice_date],[Package] as [Package], [Weight] as [Weight], [Invest_Amount] as [Invest_Amount],[Percentage] as [Percentage],[Amount]as [Amount],[Payment_Amount] as [Payment_Amount], [Payment_Type]as [Payment_Type], [Bank_Name]as [Bank_Name],[Cheque_No] as [Cheque_No],[Cheque_Date] as [Cheque_Date],[Bill_No]as [Bill_No],[Bill_Date] as [Bill_Date], [Code_Create] as [Code_Create]  FROM Billing where Bill_No is null order by FormNo desc";
           OleDbDataAdapter dscmd = new OleDbDataAdapter(sql, cnn);
           DataSet ds = new DataSet();
           dscmd.Fill(ds);
           cnn.Close();




           for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
           {
               for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
               {
                   data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                   xlWorkSheet.Cells[i + 1, j + 1] = data;


               }
           }





           System.Windows.Forms.SaveFileDialog saveDlg = new System.Windows.Forms.SaveFileDialog();
           saveDlg.InitialDirectory = @"C:\";
           saveDlg.Filter = "Excel files (*.xls)|*.xls";
           saveDlg.FilterIndex = 0;
           saveDlg.RestoreDirectory = true;
           saveDlg.Title = "Export Excel File To";
           if (saveDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
           {
               xlWorkBook.Close(true, misValue, misValue);
               xlApp.Quit();
           }
           cnn.Close();


           releaseObject(xlWorkSheet);
           releaseObject(xlWorkBook);
           releaseObject(xlApp);

推荐答案

添加循环以在写入数据的循环之前写入标头。您还需要将数据单元格向下移动一行以腾出空间。

Add a loop to write the headers before the loop that writes the data. You'll also need to shift the data cells down by one row to make room.

for (j = 0; j < ds.Tables[0].Columns.Count; j++)
{
    xlWorkSheet.Cells[1, j + 1] = ds.Tables[0].Columns[j].Caption;
}

for (i = 0; i < ds.Tables[0].Rows.Count; i++)
{
    for (j = 0; j < ds.Tables[0].Columns.Count; j++)
    {
        data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
        xlWorkSheet.Cells[i + 2, j + 1] = data;
    }
}

注意：而不是将循环条件指定为 x< = n - 1 ，更容易将其指定为 x< n 而不是。

NB: Rather than specifying your loop condition as x <= n - 1, it's easier to specify it as x < n instead.

这篇关于如何在Excel中创建标题文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！