110,577
社区成员
发帖
与我相关
我的任务
分享
#region 第一个子表的子表一:
sbd.Append("<table x:str border=0 cellpadding=0 cellspacing=0 width=518 style='border-collapse:");
sbd.Append("collapse;table-layout:fixed;width:389pt'>");
#region 第一个DataTable的数据
sbd.Append("<tr height=22 style='height:16.3pt'>");
sbd.Append("<td height=19 class=xl25 width=76 style='height:14.25pt;width:57pt;color:Red;'>办事处编码</td>");
sbd.Append("<td height=19 class=xl25 width=76 style='height:14.25pt;width:57pt;color:Red;'>办事处名称</td>");
sbd.Append("<td height=19 class=xl25 width=76 style='height:14.25pt;width:57pt;color:Red;'>联系电话</td>");
sbd.Append("<td height=19 class=xl25 width=100 style='height:14.25pt;width:57pt;color:Red;'>所在省</td>");
sbd.Append("<td height=19 class=xl25 width=76 style='height:14.25pt;width:57pt;color:Red;'>所在市</td>");
sbd.Append("<td height=19 class=xl25 width=76 style='height:14.25pt;width:57pt;color:Red;'>所在区</td>");
sbd.Append("</tr>");
for (int i = 0; i < dt.Rows.Count; i++)
{
sbd.Append("<tr height=22 style='height:16.3pt'>");
sbd.Append("<td class=xl25 style='border-top:none;border-left:none'>" + dt.Rows[i]["OffId"].ToString().Trim() + "</td>");
sbd.Append("<td class=xl25 style='border-top:none;border-left:none'>" + dt.Rows[i]["OffName"].ToString().Trim() + "</td>");
sbd.Append("<td class=xl25 style='border-top:none;border-left:none'>" + dt.Rows[i]["Phone"].ToString().Trim() + "</td>");
sbd.Append("<td class=xl25 style='border-top:none;border-left:none'>" + dt.Rows[i]["Province"].ToString().Trim() + "</td>");
sbd.Append("<td class=xl25 style='border-top:none;border-left:none'>" + dt.Rows[i]["City"].ToString().Trim() + "</td>");
sbd.Append("<td class=xl25 style='border-top:none;border-left:none'>" + dt.Rows[i]["County"].ToString().Trim() + "</td>");
sbd.Append("</tr>");
}
#endregion
sbd.Append("</table>");
public void ExportExcel()
{
// DataTable xslTable=(DataTable)this.dgrd_Show.DataSource;//取得dataGrid绑定的DataSet
// if(xslTable==null) return;
if (this.listViewQuery_IM.Items.Count == 0) return;
string saveFileName = "";
bool fileSaved = false;
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = "Sheet1";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象,可能您的电脑未安装Excel.","提示",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Range range;
// string oldCaption=this.listViewQuery_IM.ca.CaptionText;
long totalCount = this.listViewQuery_IM.Items.Count;
long rowRead = 0;
float percent = 0;
//写入表头
for (int i = 0; i < this.listViewQuery_IM.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = this.listViewQuery_IM.Columns[i].Text.ToString();
}
//设置表头显示样式
Excel.Range productTitle = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, this.listViewQuery_IM.Columns.Count]);
productTitle.Font.ColorIndex = 5;
productTitle.Font.Bold = false;
productTitle.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
productTitle.VerticalAlignment = Excel.XlVAlign.xlVAlignBottom;
//写入数值
this.lblpro.Visible = true;
for (int j = 0; j < this.listViewQuery_IM.Items.Count; j++)
{
worksheet.Cells[j + 2, 1] = this.listViewQuery_IM.Items[j].SubItems[0].Text;
worksheet.Cells[j + 2, 2] = this.listViewQuery_IM.Items[j].SubItems[1].Text;
worksheet.Cells[j + 2, 3] = this.listViewQuery_IM.Items[j].SubItems[2].Text;
worksheet.Cells[j + 2, 4] = this.listViewQuery_IM.Items[j].SubItems[3].Text;
worksheet.Cells[j + 2, 5] = this.listViewQuery_IM.Items[j].SubItems[4].Text;
worksheet.Cells[j + 2, 6] = this.listViewQuery_IM.Items[j].SubItems[5].Text;
worksheet.Cells[j + 2, 7] = this.listViewQuery_IM.Items[j].SubItems[6].Text;
worksheet.Cells[j + 2, 8] = this.listViewQuery_IM.Items[j].SubItems[7].Text;
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
//显示填写进度
this.lblpro.Text = "正在导出数据[" + percent.ToString("0.00") + "%]...";
Application.DoEvents();
}
this.lblpro.Visible = false;
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.listViewQuery_IM.Items.Count + 2, this.listViewQuery_IM.Columns.Count]);
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
if (this.listViewQuery_IM.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出Excel文件时出错,文件可能正被打开!\n" + ex.Message);
}
}
else
{
fileSaved = false;
}
xlApp.Quit();
GC.Collect();//强行销毁
//导出成功,是否打开文件提示
if (fileSaved && File.Exists(saveFileName))
{
if (MessageBox.Show("文件导出成功,要打开文件吗?", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == DialogResult.OK)
{
System.Diagnostics.Process.Start(saveFileName);
}
else
{
return;
}
}
}