现在在做一个工资的查询系统，有两个问题，请教大神
第一，能不能把图中的0.00替换成---,并且还能保持计算。
计算代码
public decimal ReturnTotal(int col)
{
decimal char_total = 0;
foreach (GridViewRow gvr in GridView1.Rows)
{
if (null != gvr.Cells[col].Text)
{
char_total += Convert.ToDecimal(gvr.Cells[col].Text);
}
}
return char_total;
}
protected void GridView1_RowDataBound1(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Footer)
{
e.Row.Cells[2].Text = "小计";
e.Row.Cells[3].Text = ReturnTotal(3).ToString();
e.Row.Cells[4].Text = ReturnTotal(4).ToString();
e.Row.Cells[5].Text = ReturnTotal(5).ToString();
e.Row.Cells[7].Text = "合计:"+ Convert.ToString(ReturnTotal(3) + ReturnTotal(4) + ReturnTotal(5));
}
}
gridview里面的数据是通过sqldatasources来绑定数据的。
第二，现在上传是通过SQL里面的导入功能，从EXCEL导入到数据库，问下大神，有没有办法可以在网页端导入。
谢谢。。
我是新手，请大神说的明白一点，谢谢。
[解决办法]
1.格式的校验自己补充完整
public decimal ReturnTotal(int col)
{
decimal char_total = 0;
foreach (GridViewRow gvr in GridView1.Rows)
{
if (null != gvr.Cells[col].Text && gvr.Cells[col].Text != "---")
{
char_total += Convert.ToDecimal(gvr.Cells[col].Text);
}
}
return char_total;
}
protected void GridView1_RowDataBound1(object sender, GridViewRowEventArgs e)
{
if(e.Row.RowType==DataControlRowType.DataRow)
{
e.Row.Cells[3].Text = e.Row.Cells[3].Text == "0.00" ? "---" : e.Row.Cells[3].Text;
e.Row.Cells[4].Text = e.Row.Cells[4].Text == "0.00" ? "---" : e.Row.Cells[4].Text;
e.Row.Cells[5].Text = e.Row.Cells[5].Text == "0.00" ? "---" : e.Row.Cells[5].Text;
}
if (e.Row.RowType == DataControlRowType.Footer)
{
e.Row.Cells[2].Text = "小计";
e.Row.Cells[3].Text = ReturnTotal(3).ToString();
e.Row.Cells[4].Text = ReturnTotal(4).ToString();
e.Row.Cells[5].Text = ReturnTotal(5).ToString();
e.Row.Cells[7].Text = "合计:"+ Convert.ToString(ReturnTotal(3) + ReturnTotal(4) + ReturnTotal(5));
}
}
2.导入excel
private static string connectionString = string.Empty;
private static ExcelExtention excelExtention = ExcelExtention.xls;
/// <summary>
/// 将数据源读成数据源
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static DataSet Read(string filename)
{
excelExtention = GetExcelExtention(filename);
return Read(filename, GetWorkSheets(GetConnectionString(filename)));
}
private static ExcelExtention GetExcelExtention(string filename)
{
string extention = filename.Split('.')[filename.Split('.').Length - 1];
if (extention.Trim() == ExcelExtention.xlsx.ToString())
return ExcelExtention.xlsx;
else
return ExcelExtention.xls;
}
/// <summary>
/// 返回连接excel的字符串
/// </summary>
/// <param name="filename"></param>
/// <returns></returns>
private static string GetConnectionString(string filename)
{
if (excelExtention == ExcelExtention.xls)
connectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;data source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", filename);
else if (excelExtention == ExcelExtention.xlsx)
connectionString = string.Format("Provider=Microsoft.ACE.OleDb.12.0; data source={0}; Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'", filename);
return connectionString;
}
public static DataSet Read(string filename, string[] workSheets)
{
var ds = new DataSet();
foreach (string workSheet in workSheets)
{
try
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", workSheet), GetConnectionString(filename)))
{
adapter.Fill(ds, workSheet);
}
}
catch (Exception ex)
{
//LogHelper.Log(string.Format("将Excel工作表【{0}】数据填充到数据集中失败，原因如下：{1}", workSheet, ex.ToString()));
throw new Exception(ex.Message, ex.InnerException);
}
}
return ds;
}
/// <summary>
///
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
private static string[] GetWorkSheets(string connectionString)
{
DataTable dataTable;
try
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
dataTable = connection.GetSchema("Tables");
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex.InnerException);
}
int lenght = dataTable.Rows.Count;
string[] worksheets = new string[lenght];
for (int i = 0; i < lenght; i++)
{
worksheets[i] = dataTable.Rows[i]["TABLE_NAME"].ToString();
}
return worksheets;
}
[解决办法]
你可以参考一下教程：http://reeezak.cnblogs.com/archive/2006/07/09/446444.html
你可以看到，在统计时方法
1// 类范围，累积合计的变量……
2decimal _totalUnitPrice = 0m;
3int _totalNonNullUnitPriceCount = 0;
4int _totalUnitsInStock = 0;
5int _totalUnitsOnOrder = 0;
6
7protected void ProductsInCategory_RowDataBound(object sender, GridViewRowEventArgs e)
8{
9 if (e.Row.RowType == DataControlRowType.DataRow)
10 {
11 // 通过e.Row.DataItem 属性引用ProductsRow
12 Northwind.ProductsRow product = (Northwind.ProductsRow)((System.Data.DataRowView)e.Row.DataItem).Row;
13
14 // 增加累积合计(如果它们不为NULL的话！)
15 if (!product.IsUnitPriceNull())
16 {
17 _totalUnitPrice += product.UnitPrice;
18 _totalNonNullUnitPriceCount++;
19 }
20
21 if (!product.IsUnitsInStockNull())
22 _totalUnitsInStock += product.UnitsInStock;
23
24 if (!product.IsUnitsOnOrderNull())
25 _totalUnitsOnOrder += product.UnitsOnOrder;
26 }
27}
这里人家是取得了绑定到 row 上的数据（Northwind.ProductsRow 类型的对象实例），然后取得原始数据。
这样，通过正确地架构处理概念，就彻底跟 GridView 的模板列的格式化（选择了Label还是数字化仪表显示，如何格式化，等等）相分离。免得纠缠在一起。