正在从数据库创建Excel

本文关键字：创建 Excel 数据库 | 更新日期: 2023-09-27 18:30:13

我想在单击某个按钮时生成Excel报告。Excel中的数据显示交易编号（接收ID）和项目（材料名称）。一个交易编号可以包含一个或多个项目。我已经有了制作Excel报表的代码。但这总是错误的。这是代码：

SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "Save Report";
sfd.FileName = ("Transaction History Report From").Replace("/","-"); // ganti slah jadi strip
sfd.Filter = "Excel FIle| *.xlsx";
/*
 * open dialog
 * -misValue pakai System.Reflection.Missing
 * -Excel.Range
 * -Excel : app,workbook,worksheet
 * -tarik data
 * -isi ke excel
 * 
 */
int row;
if (sfd.ShowDialog() == DialogResult.OK)
{
    //show dialog berhasil
    object misValue = System.Reflection.Missing.Value;
    Excel.Range rng;
    Excel.Application app = new Excel.Application();
    Excel.Workbook wb = app.Workbooks.Add(misValue); // bikin workbook
    Excel.Worksheet ws = wb.Worksheets.get_Item(1); // nikin worksheet
    rng = ws.get_Range("A1:I1");
    rng.Merge();
    rng.Value = "Transaction History Report From" ;
    rng.Font.Bold = true;
    rng.Font.Size = 21;
    rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
    row = 3;
    ws.Cells[row, 1] = "TransactionID";
    ws.Cells[row, 2] = "Material";
    DataTable dttrID = con.executeSelect("SELECT receivedID FROM TrInventoryReceived WHERE receivedDate BETWEEN '" + dateTimePicker1.Value + "' AND '" + dateTimePicker2.Value + "'");
    row++;
    for (int i = 0; i < dttrID.Rows.Count; i++) //looping sebanyak transaksi yang ada
    {
        String transactionID = dttrID.Rows[i][0].ToString();
        DataTable material = con.executeSelect("SELECT materialID FROM TrStock WHERE receivedID ='" + transactionID + "'");
        ws.Cells[row + 4, 1] = transactionID;
        row++;
        for (int j = 0; j < material.Rows.Count; j++) //mengulang sebanyak data yang ada di detail transaction
        {
            DataTable Materianame = con.executeSelect("SELECT materialName FROM MsMaterial WHERE materialID ='" + material.Rows[j][0].ToString() + "'");
                ws.Cells[row + 4 + j, 2] = Materianame.Rows[0][0].ToString();
        }
    }
    ws.Columns.AutoFit();//buat nyamain uk7ran cellnyq sama kontenya
    wb.SaveAs(sfd.FileName, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    wb.Close(true, misValue, misValue);
    app.Quit();
    releaseObject(ws);
    releaseObject(wb);
    releaseObject(app);    
    MessageBox.Show("Flie created");
}

错误信息显示"位置0没有行"

此外，我有Class Connection连接到我的数据库。这是代码：

class Connect
{
    SqlConnection con;
    public Connect()
    {
        String connectionString = @"Data Source=.'SQLEXPRESS;AttachDbFilename=" + Application.StartupPath + @"'Database1.mdf;Integrated Security=True;User Instance=True";
        con = new SqlConnection(connectionString);
    }
    public DataTable executeSelect(String query)
    {
        con.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(query, con);
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        con.Close();
        return dt;
    }
    public void execute(String query)
    {
        con.Open();
        SqlCommand cmd = new SqlCommand(query, con);
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

我的FOrm顶部DatagGridVIew为TrInventoryReceived，底部为TrStock Table

Excel中的结果示例在此处输入图像描述

有人能帮助我的代码中错误的位置吗？我该如何修复？

正在从数据库创建Excel

也许这会对您有所帮助。这是一种从数据库中导出表并将其保存为excel文件的方法。它使用SqlConnection连接到数据库。当它读取数据时，它会同时将数据写入excel文件。

此方法将数据导出到excel文件：

public static void DataBaseToExcel(string connectionString, string table, string saveAs)
    {
        Excel.Application app = new Excel.Application();
        Excel.Workbook workbook = app.Workbooks.Add(System.Reflection.Missing.Value);
        app.ActiveWindow.DisplayGridlines = false;
        Excel.Worksheet worksheet = workbook.Worksheets.Item[1];
        List<string> ColumnsNames = GetColumnsNames(connectionString, table);
        int Row = 2;
        for (int i = 0; i != ColumnsNames.Count; i++)
        {
            worksheet.Cells[1, i + 1].Value = ColumnsNames[i];
        }
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string commandString = "SELECT * FROM " + table;
            connection.Open();
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = commandString;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        for (int Column = 0; Column != ColumnsNames.Count; Column++)
                        {
                            worksheet.Cells[Row, Column + 1] = reader.GetValue(Column);
                        }
                        Row++;
                    }
                }
            }
            connection.Close();
        }
        workbook.SaveAs(saveAs);
        app.Quit();
    }

它还包含列的标题，我用以下方法检索：

public static List<string> GetColumnsNames(string connectionString, string table)
    {
        List<string> columnNames = new List<string>();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = "select c.name from sys.columns c inner join sys.tables t on t.object_id = c.object_id and t.name = '" + table + "' and t.type = 'U'";
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        columnNames.Add(reader.GetString(0));
                    }
                }
                connection.Close();
            }
        }
        return columnNames;
    }

也许为了你的目的，你必须修改这些方法。

相关文章：