//导出Execl明细按钮点击事件
private void button1_Click(object sender, EventArgs e)
{
if (tower_list.Rows.Count <= 0)
{
MessageBox.Show("该表中没有需要导出的数据！", "提示");
return;
}
string fileName = "楼盘信息表";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
for (int i = 0; i < tower_list.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = tower_list.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < tower_list.Rows.Count; r++)
{
for (int i = 0; i < tower_list.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = tower_list.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)//异常处理
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
}
public static void DataGridViewToExcel(DataGridView dataGridView) //点击导出到Excel表按钮
{
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook =
workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
//写入标题
for (int i = 0; i < dataGridView.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dataGridView.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < dataGridView.Rows.Count; r++)
{
for (int i = 0; i < dataGridView.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dataGridView.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
}
private void ExportExcels(string fileName, DataGridView gridview)
{
try
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
// saveDialog.Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*","xls","Excel");
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Name = "吊运实绩";
Save2ExcelSheet(gridview, worksheet);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("文件导出保存成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// datagridview导出Excel
/// </summary>
/// <param name="fileName">文件夹名称</param>
/// <param name="myDGV">表格名称</param>
private void ExportExcels(string fileName, DataGridView myDGV)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
//写入标题
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < myDGV.Rows.Count; r++)
{
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("文件： " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public static void DataGridExport(DataGrid dataGrid, string fileName, string sheetName = "查询结果")
{
string tempPath = Environment.CurrentDirectory + "/temp";
if (!Directory.Exists(tempPath))
{
Directory.CreateDirectory(tempPath);
}
fileName = tempPath + "/" + fileName;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
worksheet.Name = sheetName;
//写入行
//+++++++++为提高效率，采用复制粘贴的方式写入数据+++++++++++++
for (int i = 0; i < dataGrid.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dataGrid.Columns[i].Header;
}
for (int r = 0; r < dataGrid.Items.Count; r++)
{
for (int i = 0; i < dataGrid.Columns.Count; i++)
{
string value = (dataGrid.Columns[i].GetCellContent(dataGrid.Items[r]) as TextBlock)?.Text ?? (r + 1).ToString(); //读取DataGrid某一行某一列的信息内容，与DataGridView不同的地方
worksheet.Cells[r + 2, i + 1] = "'" + value;
}
}
worksheet.Columns.EntireColumn.AutoFit();
workbook.Saved = true;
workbook.SaveCopyAs(fileName);
xlApp.Visible = true;
}
public void Export(IDictionary <string, string> ProjectDetails, IDictionary <string, string> Metrics, IDictionary <string, Dictionary <string, string> > ProjectMetricValues, string outpath)
{
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
Microsoft.Office.Interop.Excel.Workbook wb = oXL.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet ws = (Worksheet)oXL.ActiveSheet;
Range tRange = ws.get_Range("H5");
tRange.Interior.Color = XlRgbColor.rgbGreen;
int row = 4;
int col = 5;
foreach (var a in ProjectDetails)
{
ws.Cells[row, col] = a.Key;
ws.Cells[row, col].Interior.Color = XlRgbColor.rgbGrey;
ws.Cells[row, col].Borders.Weight = Excel.XlBorderWeight.xlMedium;
ws.Cells[row, col].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
col++;
}
ws.Rows[6].Orientation = Excel.XlOrientation.xlUpward;
row++;
col = 5;
foreach (var b in ProjectDetails)
{
ws.Cells[row, col] = b.Value;
ws.Cells[row, col].Interior.Color = XlRgbColor.rgbGrey;
ws.Cells[row, col].Borders.Weight = Excel.XlBorderWeight.xlMedium;
ws.Cells[row, col].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
col++;
}
row++;
col = 2;
foreach (var a in Metrics)
{
ws.Cells[row, col] = a.Key;
ws.Cells[row, col].Interior.Color = XlRgbColor.rgbDarkGrey;
ws.Cells[row, col].Borders.Weight = Excel.XlBorderWeight.xlMedium;
ws.Cells[row, col].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[row, col].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
if (col == 6 || col == 11 || col == 12)
{
ws.Columns[col].ColumnWidth = 20;
}
else
{
ws.Columns[col].ColumnWidth = 15;
}
col++;
}
row++;
col = 2;
foreach (var b in Metrics)
{
ws.Cells[row, col] = b.Value;
ws.Cells[row, col].Interior.Color = XlRgbColor.rgbLightGrey;
ws.Cells[row, col].Borders.Weight = Excel.XlBorderWeight.xlMedium;
ws.Cells[row, col].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
col++;
}
row = 8;
col = 1;
foreach (var a in ProjectMetricValues)
{
ws.Cells[row, col] = a.Key;
ws.Cells[row, col].Interior.Color = XlRgbColor.rgbDarkGrey;
ws.Cells[row, col].Borders.Weight = Excel.XlBorderWeight.xlMedium;
ws.Cells[row, col].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
row++;
}
row = 8;
col = 2;
foreach (var b in ProjectMetricValues.Keys)
{
var val = ProjectMetricValues[b];
foreach (var c in val.Keys)
{
ws.Cells[row, col] = val[c];
ws.Cells[row, col].Borders.Weight = Excel.XlBorderWeight.xlMedium;
ws.Cells[row, col].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
col++;
}
row++;
col = 2;
}
ws.Cells[7, 1] = "Goal";
ws.Cells[7, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[7, 1].Borders.Weight = Excel.XlBorderWeight.xlMedium;
ws.Cells[6, 1].Borders.Weight = Excel.XlBorderWeight.xlMedium;
ws.Cells[7, 1].Interior.Color = XlRgbColor.rgbDarkGrey;
ws.Cells[6, 1].Interior.Color = XlRgbColor.rgbDarkGrey;
ws.Cells[5, 8].Interior.Color = XlRgbColor.rgbGreen;
ws.Columns[1].ColumnWidth = 15;
Microsoft.Office.Interop.Excel.Range oRange = (Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1];
float Left = (float)((double)oRange.Left);
float Top = (float)((double)oRange.Top);
const float ImageSize = 140;
const float ImageSize1 = 40;
ws.Cells[1, 6] = "DEWS Report";
ws.Cells[1, 6].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[1, 6].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
ws.Cells[1, 6].Font.Size = 20;
ws.Range["F1", "F3"].Merge();
ws.Shapes.AddPicture("D:\\siemens.jpg", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize, ImageSize1);
ws.Cells[1, 11] = DateTime.Now.ToOADate().ToString();
ws.Cells[1, 11].NumberFormat = "mm/dd/yyyy HH:mm:ss";
//ws.UsedRange.Columns.AutoFit();
oXL.Visible = true;
wb.SaveCopyAs(outpath);
}
/// <summary>
/// 把DataTable导出到EXCEL
/// </summary>
/// <param name="reportName">报表名称</param>
/// <param name="dt">数据源表</param>
/// <param name="saveFileName">Excel全路径文件名</param>
/// <returns>导出是否成功</returns>
public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)
{
if (dt == null)
{
_ReturnStatus = -1;
_ReturnMessage = "数据集为空！";
return(false);
}
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
return(false);
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Cells.Font.Size = 10;
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
worksheet.Cells[1, 1] = reportName;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Size = 12;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Bold = true;
//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
range = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]];
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (dt.Rows.Count > 0)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
//保存文件
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
_ReturnStatus = -1;
_ReturnMessage = "导出文件时出错,文件可能正被打开！\n" + ex.Message;
}
}
else
{
fileSaved = false;
}
//释放Excel对应的对象
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
xlApp.Application.Workbooks.Close();
xlApp.Quit();
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
return(fileSaved);
}
private void picLoadOut_Click(object sender, EventArgs e)
{
#region 导出信息保存为Excel表
DialogResult ret = MessageBox.Show("导出信息为敏感操作，确定要继续导出吗？(此步操作将写入操作日志)", "信息提醒", MessageBoxButtons.YesNo);
if (ret == DialogResult.Yes)
{
//Response.ContentEncoding = System.Text.Encoding.UTF8;
string fileName = "";
string saveFileName = "";
//fileName.Charset = "GB2312";
SaveFileDialog saveDialog = new SaveFileDialog();
//saveDialog.DefaultExt = "xls";
saveDialog.FileName = fileName;
saveDialog.Filter = "2003~2007工作表*.xls|*.xls|2010及以上版本工作表*.xlsx|*.xlsx";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象,您的电脑可能未安装Excel！", "来自T仔的提醒");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
for (int i = 0; i < this.dgvCustomerList.Columns.Count; i++)
{
xlApp.Cells[1, i + 1] = dgvCustomerList.Columns[i].HeaderText;
}
for (int i = 0; i < dgvCustomerList.Rows.Count; i++)//添加每一项
{
for (int j = 0; j < dgvCustomerList.Columns.Count; j++)
{
xlApp.Cells[i + 2, j + 1] = dgvCustomerList.Rows[i].Cells[j].Value.ToString();
}
}
System.Windows.Forms.Application.DoEvents();
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "信息导出成功", "来自T仔提示", MessageBoxButtons.OK);
#region 获取添加操作日志所需的信息
Operation o = new Operation();
o.OperationTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd,HH:mm:ss"));
o.Operationlog = LoginInfo.WorkerClub + LoginInfo.WorkerName + LoginInfo.WorkerPosition + LoginInfo.WorkerName + "于" + DateTime.Now + "导出了" + "用户信息!";
o.OperationAccount = LoginInfo.WorkerClub + LoginInfo.WorkerName + LoginInfo.WorkerPosition;
#endregion
OperationManager.InsertOperationLog(o);
System.Diagnostics.Process.Start("Explorer.exe", saveFileName);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();
#endregion
}
}
private void ExportExcels(string fileName, DataGridView gridview)
{
try
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Worksheet objSheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
Excel.Worksheet objSheet2 = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
worksheet.Name = "自动率";
objSheet.Name = "行车自动率";
objSheet2.Name = "行车结果自动率";
Save2ExcelSheet(gridview, worksheet);
Save2ExcelSheet(this.dataGridView3, objSheet);
Save2ExcelSheet(this.dataGridView3, objSheet2);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("文件： " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public string ExportExcel(DataSet ds, string saveFileName)
{
try
{
if (ds == null)
{
return("数据库为空");
}
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
return("无法创建Excel对象，可能您的机子未安装Excel");
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入字段
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
}
//写入数值
for (int r = 0; r < ds.Tables[0].Rows.Count; r++)
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = ds.Tables[0].Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
else
{
fileSaved = false;
}
xlApp.Quit();
GC.Collect();//强行销毁
if (fileSaved && System.IO.File.Exists(saveFileName))
{
System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
}
return("成功保存到Excel");
}
catch (Exception ex)
{
return(ex.ToString());
}
}
public void SaveCopyAs(string fileName)
{
xWorkBook.SaveCopyAs(fileName);
}
private void button2_Click(object sender, EventArgs e)
{
try
{
this.Cursor = Cursors.WaitCursor;
if (!Directory.Exists(@"D:\AOI\ImageFile"))
{
Directory.CreateDirectory(@"D:\AOI\ImageFile");
}
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.InitialDirectory = @"D:\AOI\ImageFile";
saveDialog.Filter = "Excel文件|*.xlsx";
// saveDialog.FileName = fileName;
saveDialog.FileName = "ImageFile_" + DateTime.Now.ToLongDateString().ToString();
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
this.Cursor = Cursors.Default;
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Range range = worksheet.Range[worksheet.Cells[4, 1], worksheet.Cells[8, 1]];
//写入标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < dataGridView1.Rows.Count; r++)
{
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
if (this.dataGridView1.Rows[r].Cells[i].Style.BackColor == Color.Red)
{
range = worksheet.Range[worksheet.Cells[r + 2, i + 1], worksheet.Cells[r + 2, i + 1]];
range.Interior.ColorIndex = 10;
}
/* if (r == 0 & i == 1)
* {
* worksheet.Cells[r + 2, i + 1] = DateTime.Now.ToLocalTime().ToString();
* }*/
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
this.Cursor = Cursors.Default;
}
catch
{
this.Cursor = Cursors.Default;
MessageBox.Show("处理异常3");
}
}
private void button9_Click(object sender, EventArgs e)
{
string connStr = "Data Source=WHM;Initial Catalog=A;Integrated Security=True";
string _sql = "SELECT dbo.xiangxidingdan.商品名称, dbo.xiangxidingdan.商品数量, "
+ " dbo.xiangxidingdan.商品价格," + "dbo.xiangxidingdan.商品数量*dbo.xiangxidingdan.商品价格 as '小计' FROM dbo.dingdan RIGHT OUTER JOIN dbo.xiangxidingdan ON dbo.dingdan.订单编号 = dbo.xiangxidingdan.订单编号"
+ " where 是否结账='是'";
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sda = new SqlDataAdapter(_sql, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < dataGridView1.Rows.Count; r++)
{
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//双击查看原图行销毁
}
/// <summary>
/// 导出Excel 的方法
/// </summary>
private void tslExport_Excel(string fileName, DataGridView myDGV)
{
string saveFileName = "";
//bool fileSaved = false;
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
for (int i = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < myDGV.Rows.Count; r++)
{
for (int i = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[r + 2, i] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[myDGV.Rows.Count + 2, 2]);
rang.NumberFormat = "000000000000";
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//fileSaved = true;
}
catch (Exception ex)
{
//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
// if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
MessageBox.Show(fileName + ",保存成功", "提示", MessageBoxButtons.OK);
}
private bool ExportToExcel(DataTable dt, string path)
{
bool succeed = false;
if (dt != null)
{
Microsoft.Office.Interop.Excel.Application xlApp = null;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Worksheet xlSheet = null;
xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
//xlSheet.Name = dt.TableName;
int rowIndex = 1;
int colIndex = 1;
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
for (int i = 0; i < colCount; i++)
{
string str = dt.Columns[i].ColumnName;
switch (str)
{
case "PIPELINETYPE":
str = "管线类型";
break;
case "FIELDNAME":
str = "属性";
break;
case "PVALUE":
str = "分类";
break;
case "LENGTH":
str = "长度(米)";
break;
case "TOTALLENGTH":
str = "总长度(米)";
break;
}
xlSheet.Cells[rowIndex, colIndex] = str;
colIndex++;
}
//xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowIndex, colCount]).Font.Bold = true;
//xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
//xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowCount + 1, colCount]).Font.Size = "10";
rowIndex++;
for (int i = 0; i < rowCount; i++)
{
colIndex = 1;
for (int j = 0; j < colCount; j++)
{
xlSheet.Cells[rowIndex, colIndex] = dt.Rows[i][j].ToString();
colIndex++;
}
rowIndex++;
}
MergeCell_Second(ref xlSheet, 2, dt.Rows.Count, "A");
//MergeCell_Second(ref xlSheet, 2, dt.Rows.Count, "B");
//MergeCell_Second(ref xlSheet, 2, dt.Rows.Count, "E");
xlSheet.Cells.EntireColumn.AutoFit();
xlApp.DisplayAlerts = false;
xlBook.SaveCopyAs(path);
xlBook.Close(false, null, null);
xlApp.Workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
xlBook = null;
succeed = true;
}
catch (System.Exception ex)
{
succeed = false;
}
finally
{
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
}
return(succeed);
}
