操作SqlServer+后台数据写到Excel

 1、数据库操作类的调用方法，其中sql为数据库操作的字符串内容，当n大于0则操作数据库成功

int n = ConnectSQL("127.0.0.1", "512", "Lib_W", "sa", "Sa123", sql);
if (n != 0)
{
    //操作成功
}

2、数据库操作类

/// <summary>
/// 数据库操作类
/// </summary>
/// <param name="server"></param>
/// <param name="size"></param>
/// <param name="database"></param>
/// <param name="uid"></param>
/// <param name="pwd"></param>
/// <param name="sqlCommand"></param>
/// <returns></returns>
private int ConnectSQL(string server, string size, string database, string uid, string pwd, string sqlCommand)
{
    try
    {
        string constr = string.Format("Server = {0}; Pooling=true;Max Pool Size = {1};Database={2}; Uid={3};Pwd={4}", server, size, database, uid, pwd);
        SqlConnection con = new SqlConnection(constr);
        con.Open();
        SqlCommand comm = new SqlCommand(sqlCommand, con);
        comm.CommandText = sqlCommand;
        //返回受影响行数
        int n = comm.ExecuteNonQuery();
        con.Dispose();
        con.Close();
        return n;
    }
    catch { return 0; }
}

3、在数据库以DataView形式输出后，将DataView内容显示在dataGridVIew1的表单中

string strSql = "select * from WMS_Loc_Mast";
DataView dv = sqlDataGridView(strSql, "127.0.0.1", "sa", "Sa123", "LXWZ_ASRS");
dataGridView1.DataSource = dv;

 也可以用如下形式通过foreach方法用DataRowView获取到DataView中的指定内容

4、获取数据库table，以DataView形式输出

/// <summary>
/// 获取数据库table
/// </summary>
/// <param name="sql"></param>
/// <param name="dataSource"></param>
/// <param name="userId"></param>
/// <param name="passWord"></param>
/// <param name="initialCatalog"></param>
/// <returns></returns>
private DataView sqlDataGridView(string sql, string dataSource, string userId, string passWord, string initialCatalog)
{
    SqlConnection selectConnection = (SqlConnection)null;
    SqlDataAdapter sqlDataAdapter = (SqlDataAdapter)null;
    try
    {
        SqlConnectionStringBuilder sq = new SqlConnectionStringBuilder();
        sq.DataSource = dataSource;
        sq.UserID = userId;
        sq.Password = passWord;
        sq.InitialCatalog = initialCatalog;

        selectConnection = new SqlConnection(sq.ToString());
        if (selectConnection.State == ConnectionState.Closed)
            selectConnection.Open();
        sqlDataAdapter = new SqlDataAdapter(sql, selectConnection);
        DataSet dataSet = new DataSet();
        sqlDataAdapter.Fill(dataSet);
        DataView dataView = new DataView(dataSet.Tables[0]);
        sqlDataAdapter.Dispose();
        selectConnection.Dispose();
        selectConnection.Close();
        return dataView;
    }
    catch (Exception ex)
    {
        return (DataView)null;
    }
    finally
    {
        sqlDataAdapter.Dispose();
        selectConnection.Dispose();
        selectConnection.Close();
    }
}

5、这是一个以多态List为形参的数据生成Excel方法

/// <summary>
/// 盘点数据生成EXCEL
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
private bool WriteDataToExcel(List<ItemInfo> data)
{
    try
    {
        // 创建一个新的Excel文件
        IWorkbook workbook = new HSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("盘点报告" + DateTime.Now.ToString("yyyy年MM月dd日HH时mm分"));

        // 创建表头
        IRow headerRow = sheet.CreateRow(0);
        headerRow.CreateCell(0).SetCellValue("库位号");
        headerRow.CreateCell(1).SetCellValue("物料类型");
        headerRow.CreateCell(2).SetCellValue("物料编码");
        headerRow.CreateCell(3).SetCellValue("批次号");
        headerRow.CreateCell(4).SetCellValue("物料号");
        headerRow.CreateCell(5).SetCellValue("物料名称");
        headerRow.CreateCell(6).SetCellValue("材质");
        headerRow.CreateCell(7).SetCellValue("规格");
        headerRow.CreateCell(8).SetCellValue("数量");
        headerRow.CreateCell(9).SetCellValue("单位");
        headerRow.CreateCell(10).SetCellValue("备注");


        for (int i = 0; i < data.Count; i++)
        {
            IRow row = sheet.CreateRow(i + 1);
            if (data[i].ItemType == "piece")//工件
            {
                row.CreateCell(0).SetCellValue("NULL");//盘点报告无库位号
                row.CreateCell(1).SetCellValue(data[i].ItemType);//物料类型
                row.CreateCell(2).SetCellValue(data[i].ItemId);//物料编码
                row.CreateCell(3).SetCellValue(data[i].BatchNum);//。。
                row.CreateCell(4).SetCellValue(data[i].ItemNo);
                row.CreateCell(5).SetCellValue(data[i].ItemName);
                row.CreateCell(6).SetCellValue(data[i].Datum);
                row.CreateCell(7).SetCellValue(data[i].Spec);
                row.CreateCell(8).SetCellValue(data[i].Qty);
                row.CreateCell(9).SetCellValue(data[i].Unit);
                row.CreateCell(10).SetCellValue(data[i].Memo);
            }
            else if (data[i].ItemType == "Box")//盒子
            {
                row.CreateCell(0).SetCellValue("NULL");//盘点报告无库位号
                row.CreateCell(1).SetCellValue(data[i].ItemType);
                row.CreateCell(2).SetCellValue(data[i].ItemId);
                row.CreateCell(3).SetCellValue(data[i].BatchNum);
                row.CreateCell(4).SetCellValue(data[i].ItemNo);
                row.CreateCell(5).SetCellValue(data[i].ItemName);
                row.CreateCell(6).SetCellValue(data[i].Datum);
                row.CreateCell(7).SetCellValue(data[i].Spec);
                row.CreateCell(8).SetCellValue(data[i].Qty);
                row.CreateCell(9).SetCellValue(data[i].Unit);
                row.CreateCell(10).SetCellValue(data[i].Memo);
            }
        }
        IRow rowEmpty = sheet.CreateRow(data.Count + 2);//增加一行，往后递增
        rowEmpty.CreateCell(0).SetCellValue(" -------------------------------------- ");
        IRow rowEnd = sheet.CreateRow(data.Count + 3);//增加一行，往后递增
        rowEnd.CreateCell(0).SetCellValue("总库位数量：" + listKU.Count);
        rowEnd.CreateCell(3).SetCellValue("总工件数量：" + listGJ.Count);

        // 保存Excel文件
        using (FileStream fs = new FileStream(("D:\\BoomyCode\\C#\\812零星库\\SporadicStorageWMS\\WMS\\" + "盘点报告" + DateTime.Now.ToString("yyyy年MM月dd日HH时mm分") + ".xls"), FileMode.Create))
        {
            workbook.Write(fs);
        }
        return true;
    }
    catch
    {
        return false;
    }
}

6、Excel模板：