StringBuilder sb = new StringBuilder();
sb.Append("MAC地址,生产商,报备厂商,终端类型,硬件版本,软件版本kernel,软件版本APP,上线IP,开机时间\n");
for (int i = 0; i < itemCount; i++)
{
sb.Append(((Label)rptList.Items[i].FindControl("lblMac")).Text + ",");
sb.Append(((Label)rptList.Items[i].FindControl("lblVendor")).Text + ",");
sb.Append(((Label)rptList.Items[i].FindControl("lblCom")).Text + ",");
sb.Append(((Label)rptList.Items[i].FindControl("lblBoxType")).Text + ",");
sb.Append(((Label)rptList.Items[i].FindControl("lblHw")).Text + ",");
sb.Append(((Label)rptList.Items[i].FindControl("lblKernel")).Text + ",");
sb.Append(((Label)rptList.Items[i].FindControl("lblSw")).Text + ",");
sb.Append(((Label)rptList.Items[i].FindControl("lblIP")).Text + ",");
sb.Append(((Label)rptList.Items[i].FindControl("lblAddDate")).Text);
sb.Append("\n");
}
Response.ClearHeaders();
Response.AppendHeader("Content-disposition", "attachment;filename=开机认证记录.csv");
Response.Write(sb.ToString());
Response.End(); 以下还有4种方法，不过有点麻烦：
1.采用流方式
private void DataGridViewToExcel(DataGridView dgv)
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Execl files (*.xls)|*.xls";
dlg.FilterIndex = 0;
dlg.RestoreDirectory = true;
dlg.CreatePrompt = true;
dlg.Title = "保存为Excel文件";
if (dlg.ShowDialog() == DialogResult.OK)
{
Stream myStream;
myStream = dlg.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string columnTitle = "";
try
{
//写入列标题
for (int i = 0; i < dgv.ColumnCount; i )
{
if (i > 0)
{
columnTitle = "\t";
}
columnTitle = dgv.Columns[i].HeaderText;
}
sw.WriteLine(columnTitle);
//写入列内容
for (int j = 0; j < dgv.Rows.Count; j )
{
string columnValue = "";
for (int k = 0; k < dgv.Columns.Count; k )
{
if (k > 0)
{
columnValue = "\t";
}
if (dgv.Rows[j].Cells[k].Value == null)
columnValue = "";
else
columnValue = dgv.Rows[j].Cells[k].Value.ToString().Trim();
}
sw.WriteLine(columnValue);
}
sw.Close();
myStream.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
} 2.显示Excel界面
/// <remarks>
/// 从工具箱中添加"Microsoft Excel 11.0 Object Library"
/// 命名空间添加 using Excel = Microsoft.Office.Interop.Excel;
/// </remarks>
/// <returns> </returns>
public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
{
if (dgv.Rows.Count == 0)
return false;
//建立Excel对象
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcle;
//生成字段名称
for (int i = 0; i < dgv.ColumnCount; i )
{
excel.Cells[1, i 1] = dgv.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dgv.RowCount - 1; i )
{
for (int j = 0; j < dgv.ColumnCount; j )
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i 2, j 1] = "'" dgv[j, i].Value.ToString();
}
else
{
excel.Cells[i 2, j 1] = dgv[j, i].Value.ToString();
}
}
}
return true;
} 3.采用Excel控件方法
/// <summary>
///方法，导出DataGridView中的数据到Excel文件
/// </summary>
/// <remarks>
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel=Microsoft.Office.Interop.Excel;
/// using System.Reflection;
/// </remarks>
/// <param name="dgv" /> DataGridView
public static void DataGridViewToExcel(DataGridView dgv)
{
#region 验证可操作性
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.DefaultExt = "xls ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
//默然路径是系统当前路径
dlg.InitialDirectory = Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel) return;
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{ return; }
//定义表格内数据的行数和列数
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数必须大于0
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//行数不可以大于65536
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数不可以大于255
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//验证以fileNameString命名的文件是否存在，如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#endregion
Excel.Application objExcel = null;
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//设置EXCEL不可见
objExcel.Visible = false;
//向Excel中写入表格的表头
int displayColumnsCount = 1;
for (int i = 0; i <= dgv.ColumnCount - 1; i )
{
if (dgv.Columns[i].Visible == true)
{
objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
displayColumnsCount ;
}
}
//设置进度条
//tempProgressBar.Refresh();
//tempProgressBar.Visible = true;
//tempProgressBar.Minimum=1;
//tempProgressBar.Maximum=dgv.RowCount;
//tempProgressBar.Step=1;
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row <= dgv.RowCount - 1; row )
{
//tempProgressBar.PerformStep();
displayColumnsCount = 1;
for (int col = 0; col < colscount; col )
{
if (dgv.Columns[col].Visible == true)
{
try
{
objExcel.Cells[row 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount ;
}
catch (Exception)
{
}
}
}
}
//隐藏进度条
//tempProgressBar.Visible = false;
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
//关闭Excel应用
if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
if (objExcel != null) objExcel.Quit();
objsheet = null;
objWorkbook = null;
objExcel = null;
}
MessageBox.Show(fileNameString "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
} 4.用模版的方式导出
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="sender" />
/// <param name="e" />
private void 导出到ExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
string excelPath = null;
#region 获取Excel保存路径
//using (FolderBrowserDialog myDialog = new FolderBrowserDialog())
//{
// myDialog.ShowNewFolderButton = false;
// myDialog.Description = "请选择要保存文件夹或目录";
// if (myDialog.ShowDialog() == DialogResult.OK)
// {
// excelPath = myDialog.SelectedPath + "\\股东清册.xls";
// }
//}
#endregion
#region 选择Excel模板
using (OpenFileDialog openFile = new OpenFileDialog())
{
openFile.Title = "请选择Excel文件模板";
openFile.Filter = "Excel 97-2003 文件模板|*.xls|Excel 2007-2010 文件模板|*.xlsx";
openFile.CheckFileExists = true;
openFile.CheckPathExists = true;
if (openFile.ShowDialog(this) == DialogResult.OK)
{
excelPath = openFile.FileName;
//MessageBox.Show(openFile.FileName, "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("未选择Excel文件模板！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
#endregion
System.Data.DataTable dt = new System.Data.DataTable();
DataColumn column;
DataRow row;
#region
//column = new DataColumn();
//column.ColumnName = "序号";
//column.DataType = typeof(string);
//dt.Columns.Add(column);
//DataColumn column;
// row = dt.NewRow();
// row["序号"] = i + 1;
// row["股权托管证编号"] = dtStock.Rows[i]["trustee_id"].ToString();
// row["股东帐号"] = dtStock.Rows[i]["stock_account"].ToString();
// row["股东名称"] = dtStock.Rows[i]["customer_name"].ToString();
// row["股权类别"] = dtStock.Rows[i]["stock_bonus_type_name"].ToString();
// row["股权数量"] = dtStock.Rows[i]["begin_amount"].ToString();
// row["联系地址"] = dtStock.Rows[i]["address"].ToString();
// row["邮编"] = dtStock.Rows[i]["zip"].ToString();
// row["电话"] = dtStock.Rows[i]["phone"].ToString();
// dt.Rows.Add(row);
#endregion
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
if (dataGridView1.Columns[j].Visible == true)
{
column = new DataColumn();
column.ColumnName = dataGridView1.Columns[j].HeaderText;
column.DataType = typeof(string);
dt.Columns.Add(column);
}
}
for (int i = 0; i < dataGridView1.RowCount; i++)
{
row = dt.NewRow();
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
if (dataGridView1.Columns[j].Visible == true)
{
row[dataGridView1.Columns[j].HeaderText] = dataGridView1.Rows[i].Cells[j].Value;
}
}
dt.Rows.Add(row);
}
DataTableToExcel(dt, excelPath);
}
/// <summary>
/// 将Datatable导出Excel时新建Excel
/// </summary>
/// <param name="dt" />
/// <param name="excelPath" />
/// <returns></returns>
public static bool DataTableTonewExcel(System.Data.DataTable dt, string excelPath)
{
try
{
object missing = System.Reflection.Missing.Value;
Excel.Application app = new Excel.ApplicationClass();
app.Application.Workbooks.Add(true);
Excel.Workbook newExcel = (Excel.Workbook)app.ActiveWorkbook;
Excel.Worksheet newSheet = (Excel.Worksheet)newExcel.ActiveSheet;
int row = dt.Rows.Count;
int column = dt.Columns.Count;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < column; j++)
{
newSheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}
for (int i = 0; i < column; i++)//设置Sheet的列名
{
newSheet.Cells[1, 1 + i] = dt.Columns[i].ColumnName;
}
newExcel.Save();
app.Cells.Font.Name = "宋体"; //字体
app.Cells.Font.Size = 9; //字号
//保存excel文件
newExcel.SaveCopyAs(excelPath);
//关闭文件
newExcel.Close(false, missing, missing);
//退出excel
app.Quit();
return true;
}
catch
{
return false;
}
finally
{
KillExcel();
}
}
/// <summary>
/// 将DataTable导出到指定路径Excel模板
/// </summary>
/// <param name="dt" />
/// <param name="excelPath" />
/// <returns></returns>
public static bool DataTableToExcel(System.Data.DataTable dt, string excelPath)
{
try
{
Excel.Application app = new Excel.Application();
app.Visible = true;
Excel.Workbook myExcel = app.Workbooks.Open(excelPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
Excel.Worksheet mySheet = (Excel.Worksheet)myExcel.Worksheets[1];
int row = dt.Rows.Count;
int column = dt.Columns.Count;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < column; j++)
{
mySheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}
for (int i = 0; i < column; i++)//设置Sheet的列名
{
mySheet.Cells[1, 1 + i] = dt.Columns[i].ColumnName;
}
myExcel.Save();
app.Cells.Font.Name = "宋体"; //字体
app.Cells.Font.Size = 9; //字号
app.DisplayAlerts = false;
//覆盖时不提示
app.AlertBeforeOverwriting = false;
app.SaveWorkspace(excelPath);
app.Quit();
app = null;
return true;
}
catch
{
return false;
}
finally
{
KillExcel();
}
}
/// <summary>
/// 关闭Excel进程
/// </summary>
private static void KillExcel()
{
System.Diagnostics.Process[] pro = System.Diagnostics.Process.GetProcessesByName("Excel");
foreach (System.Diagnostics.Process killpro in pro)
{
killpro.Kill();
}
} 
549
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
