asp.net可以不需要建一个EXCEL文件直接导出到EXCEL(未保存状态)
Response.ContentType = "application/vnd.ms-excel ";
Response.OutputStream.Flush;请问winform要怎样实现这个方法?
Response.ContentType = "application/vnd.ms-excel ";
Response.OutputStream.Flush;请问winform要怎样实现这个方法?
调试欢乐多
{
MessageBox.Show("没有数据可供导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出文件保存路径"; saveFileDialog.ShowDialog();
progreesBar.Visible = true;
Stream myStream;
myStream = saveFileDialog.OpenFile();
//StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string str = "";
try
{
//写标题
for (int i = 0; i < pListView.Columns.Count; i++)
{
if (i > 0)
{
str += "\t";
}
str += pListView.Columns[i].Text.ToString();
}
sw.WriteLine(str);
//写内容
for (int j = 0; j < pListView.Items.Count; j++)
{
string tempStr = "";
for (int k = 0; k < pListView.Columns.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr += pListView.Items[j].SubItems[k].Text.ToString();
}
sw.WriteLine(tempStr);
progreesBar.Value += 100 / pListView .Items .Count ;
}
sw.Close();
myStream.Close();
progreesBar.Value = 100;
MessageBox.Show("数据已经成功导出到:" + saveFileDialog.FileName.ToString(), "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
progreesBar.Value = 0;
progreesBar.Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "友情提示", MessageBoxButtons.OK);
}
finally
{
sw.Close();
myStream.Close();
} }
private void btnCSV_Click(object sender, EventArgs e)
{
//C#创建Excel文件之取得数据
DataTable dt = GetData();//这个方法是将你的数据搞成datatable
ComLibrary com = new ComLibrary();
if (dt != null)
{
SaveExcel(dt);
}
}
2、
导出excel方法
private void SaveExcel(DataTable dt)
{
//创建一个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);
//同样方法处理数据
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] = "剩余库存";
}
else
{
if (row["TRADEMARK"].ToString() != dt.Rows[rowidx - 1]["TRADEMARK"].ToString())
{
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] = "剩余库存";
}
}
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"];
rowidx++;
}
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";
}
}
else
{
filename = DateTime.Now.ToString("yyyy年MM月dd日") + "销售日报.xls";
}
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;
}
}