110,536
社区成员
发帖
与我相关
我的任务
分享数据库数据导入Excel中
我现在要做这个功能，统计下季度的利润，之前没做过，应该用什么类？
用asp.net做 大家给说说，给个案例也可以
用asp.net做 大家给说说，给个案例也可以
...全文
请发表友善的回复…
发表回复
haohaojiazu 2011-04-17
- 打赏
- 举报
先把数据显示到datagridview中，然后点调用ecxel com组建就基本ok了！代码很简单的！！
vanguards 2011-04-16
- 打赏
- 举报
好啊，我也借光来学习学习
xinshouruhang 2011-04-16
- 打赏
- 举报
谢谢，贴这么多代码，我参考下~~
心灵彩虹 2011-04-16
- 打赏
- 举报
/// <summary>
/// 创建Excel
/// </summary>
private void SaveExcel(DataTable dt)
{
if (this.rdoDayList.Checked == true)
{
billName = "销售日报";
}
if (this.rdoWeekList.Checked == true)
{
return;
}
if (this.rdoYearList.Checked == true)
{
return;
}
if (this.rdoMonthList.Checked == true)
{
return;
}
if (this.rdoOtherList.Checked == true)
{
billName = "其他销售报表";
}
//创建一个excel application
Microsoft.Office.Interop.Excel.Application xls_exp = null;
int rowindex = 1;
int colindex = 0;
string path = "";
//创建一个workbook,一个worksheet
Microsoft.Office.Interop.Excel.Workbook xls_book = null;
Microsoft.Office.Interop.Excel.Worksheet xls_sheet = null;
try
{
this.Cursor = Cursors.WaitCursor;
xls_exp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xls_book = xls_exp.Workbooks.Add(true);
//同样方法处理数据
//clsSale cs = new clsSale();
//DataSet ds = new DataSet();
//cs.startdate = ComLibrary.ToInt(dtpStartDate.txtDate.Text);
//cs.enddate = ComLibrary.ToInt(dtpEndDate.txtDate.Text);
//cs.trademark = cmbTradeMark.Value;//add by ge 2011-03-09
//ds = cs.GetDataList(19);
int rowidx = 0;
foreach (DataRow row in dt.Rows)
{
//首行
if (rowidx == 0)
{
colindex = 1;
xls_sheet = (Microsoft.Office.Interop.Excel.Worksheet)xls_book.ActiveSheet;
//xls_sheet.Name = "测试";
if (row["TRADEMARK"].ToString() == "")
{
xls_sheet.Name = "无供应商";
}
else
{
xls_sheet.Name = row["TRADEMARK"].ToString();
}
xls_exp.Cells[1, colindex] = "商品编号";
xls_exp.Cells[1, colindex + 1] = "商品名称";
xls_exp.Cells[1, colindex + 2] = "销售数量";
xls_exp.Cells[1, colindex + 3] = "剩余库存";
xls_exp.Cells[1, colindex + 4] = "金额合计";
count = ComLibrary.ToDecimal(row["SUMAMT"]);
}
else
{
if (row["TRADEMARK"].ToString() != dt.Rows[rowidx - 1]["TRADEMARK"].ToString())
{
count = 0;
rowindex = 1;
xls_exp.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xls_sheet = (Microsoft.Office.Interop.Excel.Worksheet)xls_book.Worksheets[1];
//xls_sheet.Name = row["TRADEMARK"].ToString();
if (row["TRADEMARK"].ToString() == "")
{
xls_sheet.Name = "无供应商";
}
else
{
xls_sheet.Name = row["TRADEMARK"].ToString();
}
colindex = 1;
xls_exp.Cells[1, colindex] = "商品编号";
xls_exp.Cells[1, colindex + 1] = "商品名称";
xls_exp.Cells[1, colindex + 2] = "销售数量";
xls_exp.Cells[1, colindex + 3] = "剩余库存";
xls_exp.Cells[1, colindex + 4] = "金额合计";
count = count + ComLibrary.ToDecimal(row["SUMAMT"]);
}
else
{
count = count + ComLibrary.ToDecimal(row["SUMAMT"]);
}
}
colindex = 1;
////数字格式设置为文本
//xls_sheet.get_Range(
//xls_exp.Cells[rowindex-1, colindex],
//xls_exp.Cells[rowindex-1, colindex + 3]).NumberFormatLocal = "@";
rowindex++;
//C#创建Excel文件之给cell赋值
//数字格式设置为文本
xls_sheet.get_Range(xls_exp.Cells[rowindex, colindex], xls_exp.Cells[rowindex, colindex + 1]).NumberFormatLocal = "@";
xls_exp.Cells[rowindex, colindex] = row["ITEMCD"];
xls_exp.Cells[rowindex, colindex + 1] = row["ITEMNAME"];
xls_exp.Cells[rowindex, colindex + 2] = row["SALEQTY"];
xls_exp.Cells[rowindex, colindex + 3] = row["QTY"];
xls_exp.Cells[rowindex, colindex + 4] = row["SUMAMT"];
rowidx++;
/************************and by gefangliang 2011-03-07增加日销售数量合计，金额合计*********************/
xls_exp.Cells[rowindex + 1, 1] = "销售金额合计:";
xls_sheet.Cells[rowindex + 1, 2] = count;
}
this.Cursor = Cursors.Default;
string filename;
if (ComLibrary.GetRadioButton(pnlList) == "4")//其他报表
{
if (ComLibrary.ToInt(dtpStartDate.txtDate.Text) != ComLibrary.ToInt(dtpEndDate.txtDate.Text))
{
filename = (ComLibrary.ToInt(dtpStartDate.txtDate.Text)).ToString("####年##月##日")
+ (ComLibrary.ToInt(dtpEndDate.txtDate.Text)).ToString(" 至 ####年##月##日销售报表") + ".xls";
}
else
{//考虑到可以打印过去某日的报表
//filename = (ComLibrary.ToInt(dtpEndDate.txtDate.Text)).ToString("####年##月##日销售报表") + ".xls";
filename = billName;
}
}
else
{
//filename = DateTime.Now.ToString("yyyy年MM月dd日") + "销售报表.xls";
filename = billName;
}
saveFileDialog1.FileName = filename;
//saveFileDialog1.Filter = "Excel文档|.xls";
saveFileDialog1.Title = "销售报表存放位置";
saveFileDialog1.Filter = "excel files(*.xls)|*.xls";//excel files(*.xls)|*.xls|All files(*.*)|*.*
saveFileDialog1.FilterIndex = 0;
saveFileDialog1.RestoreDirectory = true;
if (saveFileDialog1.ShowDialog() == DialogResult.Cancel)
{
path = saveFileDialog1.FileName;
//放弃保存
this.Cursor = Cursors.Default;
//不替换时关闭
xls_book.Close(false, path, Missing.Value);//关闭不保存修改
xls_exp.Application.Quit();
xls_exp.Quit();
GC.Collect();
return;
}
path = saveFileDialog1.FileName;
xls_exp.Cells.EntireColumn.AutoFit();
xls_book.Saved = true;
xls_book.SaveCopyAs(path);
DialogResult dr = MessageBox.Show("导出成功！是否打开报表所在文件夹？", "系统信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dr == DialogResult.OK)
{
System.Diagnostics.Process.Start("explorer.exe", "/select," + path);
}
this.Cursor = Cursors.Default;
xls_book.Close(false, path, Missing.Value);//关闭不保存修改
xls_exp.Application.Quit();
xls_exp.Quit();
GC.Collect();
}
catch (Exception err)
{
//异常时关闭
xls_book.Close(false, path, Missing.Value);//关闭不保存修改
xls_exp.Application.Quit();
xls_exp.Quit();
GC.Collect();
MessageBox.Show("销售报表保存失败！(" + err.ToString() + ")", "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
this.Cursor = Cursors.Default;
}
}
心灵彩虹 2011-04-16
- 打赏
- 举报
第一种方法例子
private void btnCSV_Click(object sender, EventArgs e)
{
//检查是否安装excel程序
if (!codeboolisExcelInstalled())
{
MessageBox.Show("当前系统没有发现可执行的Excel文件, 如需使用Excel功能请先安装office", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//C#创建Excel文件之取得数据
DataTable dt = GetData();
ComLibrary com = new ComLibrary();
if (dt != null)
{
SaveExcel(dt);
}
}
/// <summary>
/// 获取数据
/// </summary>
/// <returns></returns>
private DataTable GetData()
{
if (!ControlCheck(dtpDate.txtDate, "处理日", true, true)) return null;
if (ComLibrary.GetRadioButton(pnlList) != "3")
if (!ControlCheck(dtpStartDate.txtDate, "日期", true, true)) return null;
if (ComLibrary.GetRadioButton(pnlList) == "4")
{
if (!ControlCheck(dtpEndDate.txtDate, "终了日", true, true)) return null;
if (!DateCheck()) return null;
}
if (!StaffCheck()) return null;
if (ComLibrary.GetRadioButton(pnlType) == "0")
{
if (!ItemCheck()) return null;
}
if (ComLibrary.GetRadioButton(pnlType) == "1")
if (!ScombCheck()) return null;
clsSale cs = new clsSale();
DataSet ds = new DataSet();
cs.staffcd = txtStaffCD.Value;
cs.itemcd = txtItemCD.Value;
cs.trademark = cmbTradeMark.Value;
switch (ComLibrary.GetRadioButton(pnlList))
{
case "0": //销售日报打印
cs.startdate = ComLibrary.ToInt(dtpStartDate.txtDate.Text);
cs.enddate = ComLibrary.ToInt(dtpStartDate.txtDate.Text);
break;
case "1": //销售周报
cs.startdate = ComLibrary.ToInt(txtWeekStart.Text);
cs.enddate = ComLibrary.ToInt(txtWeekEnd.Text);
break;
case "2": //销售月报
cs.startdate = ComLibrary.ToInt(dtpStartDate.txtDate.Text + "01");
string date = ComLibrary.ToDateFormat(ComLibrary.ToInt(dtpStartDate.txtDate.Text + "01"), "YYYY/MM/DD");
cs.enddate = ComLibrary.ToInt((Convert.ToDateTime(date).AddMonths(1).AddDays(-1)).ToString("yyyyMMdd"));
break;
case "3": //销售年报
cs.startdate = ComLibrary.ToInt(dtpStartDate.txtDate.Text + "0101");
cs.enddate = ComLibrary.ToInt(dtpStartDate.txtDate.Text + "1231");
break;
case "4": //其他销售报表
cs.startdate = ComLibrary.ToInt(dtpStartDate.txtDate.Text);
cs.enddate = ComLibrary.ToInt(dtpEndDate.txtDate.Text);
break;
}
if (ComLibrary.GetRadioButton(pnlType) == "0") //单品打印
{
clsSale csExcel = new clsSale();
ds = csExcel.GetDataList(18);
try
{
if (ds == null)
{
this.Cursor = Cursors.Default;
lblMsg.Text = cm.GetMessage("E00009");
lblMsg.ForeColor = Color.Red;
return null;
}
if (ds.Tables[0].Rows.Count == 0)
{
this.Cursor = Cursors.Default;
lblMsg.Text = cm.GetMessage("E00009");
lblMsg.ForeColor = Color.Red;
return null;
}
this.Cursor = Cursors.Default;
return ds.Tables[0];
}
catch (Exception ew)
{
this.Cursor = Cursors.Default;
ComLibrary.getMsg(ew.Message, "E", "错误");
return null;
}
}
return null;
}
心灵彩虹 2011-04-16
- 打赏
- 举报
可以给你两种思路，1、直接生成excel的行和列，然后将数据放入datatable，导入，2、做好excel模板，直接导入，这个方法要注意模板的列和行的变动和删减。功能要多一些。
