using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using GeoScene.Data;
using Microsoft.Office.Interop.Excel;
using Application = Microsoft.Office.Interop.Excel.Application;
using DataTable = System.Data.DataTable;
namespace Cyberpipe
{
class ExpEXCEL
{
/// <summary>
/// 导出EXCEL单个SHEET
/// </summary>
/// <param name="dt"></param>
/// <param name="strSaveFile"></param>
public static void ExpToExcel(DataGridView dt, string strSaveFile,string str)
{
//创建一个EXCEL应用程序
Application excel = new Application();
//是否显示导出过程（显示创建后的EXCEL）
excel.Visible = false;
//定义缺省值
Missing miss = Missing.Value;
//创建一个新的工作簿
Workbooks workbooks = excel.Workbooks;
Workbook workbook = workbooks.Add(miss);
Worksheet sheet = workbook.Worksheets[1];
sheet.Name = str;
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.Cells[1, i + 1] = dt.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dt.Rows.Count; i++)//所要添加的行数
{
for (int j = 0; j < dt.Columns.Count; j++)//每行的列数
{
//将数据填充到对应的单元格中
sheet.Cells[i + 2, j + 1] = dt.Rows[i].Cells[j].Value.ToString();
}
}
//设置表格样式
//设置列标题的背景颜色
Range er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, dt.Columns.Count]];
//重新选择单元格范围
int rowscount = dt.Rows.Count;//this.dataGridView1.Rows.Count;
int columncount = dt.Columns.Count;//this.dataGridView1.ColumnCount;
//将范围重新确定为每一行的第一个单元格
er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount, 1]];
//选中EXCEL所有表格
er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount + 1, columncount + 1]];
//让EXCEL中的所有单元格的列宽碎文字的长短自动调整
er.EntireColumn.AutoFit();
// 让EXCEL的文本水平居中方式
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//保存文件
string strSaveFile2 = strSaveFile;
if (File.Exists(strSaveFile2))
{
int z = 1;
do
{
strSaveFile2 = Path.Combine(Path.GetDirectoryName(strSaveFile) + "\\" + Path.GetFileNameWithoutExtension(strSaveFile) + z + ".xlsx");
z++;
} while (File.Exists(strSaveFile2));
}
sheet.SaveAs(strSaveFile2, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
//关闭表格
workbook.Close(false, miss, miss);
workbooks.Close();
//释放资源
excel.Quit();
}
/// <summary>
/// 将DataTable保存为Excel文件,两个SHEET
/// </summary>
public static void ExpToExcel(DataGridView dt1, DataGridView dt, string strSaveFile)
{
//创建一个EXCEL应用程序
Application excel = new Application();
//是否显示导出过程（显示创建后的EXCEL）
excel.Visible = false;
excel.Workbooks.Add(true);
//定义缺省值
Missing miss = Missing.Value;
//创建一个新的工作簿
Workbooks workbooks = excel.Workbooks;
Workbook workbook = workbooks.Add(miss);
Worksheet sheet = workbook.Worksheets[1];
Worksheet sheet1;
if (workbook.Worksheets.Count == 1)
{
sheet1 = (Worksheet)workbook.Worksheets.Add(miss, workbook.ActiveSheet);
}
else {
sheet1 = workbook.Worksheets[2];
}
sheet.Name = "垂直距离";
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.Cells[1, i + 1] = dt.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dt.Rows.Count; i++)//所要添加的行数
{
for (int j = 0; j < dt.Columns.Count; j++)//每行的列数
{
//将数据填充到对应的单元格中
sheet.Cells[i + 2, j + 1] = dt.Rows[i].Cells[j].Value.ToString();
}
}
//设置表格样式
//设置列标题的背景颜色
Range er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, dt.Columns.Count]];
//重新选择单元格范围
int rowscount = dt.Rows.Count;//this.dataGridView1.Rows.Count;
int columncount = dt.Columns.Count;//this.dataGridView1.ColumnCount;
//将范围重新确定为每一行的第一个单元格
er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount, 1]];
//选中EXCEL所有表格
er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount + 1, columncount + 1]];
//让EXCEL中的所有单元格的列宽碎文字的长短自动调整
er.EntireColumn.AutoFit();
// 让EXCEL的文本水平居中方式
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet1.Name = "水平距离";
for (int i = 0; i < dt1.Columns.Count; i++)
{
sheet1.Cells[1, i + 1] = dt1.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dt1.Rows.Count; i++)//所要添加的行数
{
for (int j = 0; j < dt1.Columns.Count; j++)//每行的列数
{
//将数据填充到对应的单元格中
sheet1.Cells[i + 2, j + 1] = dt1.Rows[i].Cells[j].Value.ToString();
}
}
//设置表格样式
//设置列标题的背景颜色
Range er1 = sheet1.Range[sheet1.Cells[1, 1], sheet1.Cells[1, dt1.Columns.Count]];
//重新选择单元格范围
int rowscount1 = dt.Rows.Count;//this.dataGridView1.Rows.Count;
int columncount1 = dt.Columns.Count;//this.dataGridView1.ColumnCount;
//将范围重新确定为每一行的第一个单元格
er1 = sheet1.Range[sheet1.Cells[1, 1], sheet1.Cells[rowscount, 1]];
//选中EXCEL所有表格
er1 = sheet1.Range[sheet1.Cells[1, 1], sheet1.Cells[rowscount + 1, columncount + 1]];
//让EXCEL中的所有单元格的列宽碎文字的长短自动调整
er1.EntireColumn.AutoFit();
// 让EXCEL的文本水平居中方式
er1.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//保存文件
string strSaveFile2 = strSaveFile;
if (File.Exists(strSaveFile2))
{
int z = 1;
do
{
strSaveFile2 = Path.Combine(Path.GetDirectoryName(strSaveFile) + "\\" + Path.GetFileNameWithoutExtension(strSaveFile) + z + ".xlsx");
z++;
} while (File.Exists(strSaveFile2));
}
sheet.SaveAs(strSaveFile2, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
//关闭表格
workbook.Close(false, miss, miss);
workbooks.Close();
//释放资源
excel.Quit();
KillProcess(excel);
}
static List<String> sheetNameList = new List<string>();
/// <summary>
/// 导出EXCEL多个SHEET(红线审核导出用)
/// </summary>
/// <param name="dt"></param>
/// <param name="strSaveFile"></param>
public static void ExpToExcel(DataGridView dt, string strSaveFile, string str, List<MainFrm.LineStruct> lineStrcut, List<GSOFeatures> featsList)
{
sheetNameList.Clear();
string layer = "";
string hxName = "";
//创建一个EXCEL应用程序
Application excel = new Application();
//是否显示导出过程（显示创建后的EXCEL）
excel.Visible = false;
//定义缺省值
Missing miss = Missing.Value;
//创建一个新的工作簿
Workbooks workbooks = excel.Workbooks;
Workbook workbook = workbooks.Add(miss);
Worksheet sheet = null;
for (int sheetCount = 0; sheetCount <= dt.Rows.Count; sheetCount++)
{
if (workbook.Worksheets.Count > sheetCount)
{
sheet = workbook.Worksheets[sheetCount + 1];
}
else
{
sheet = (Worksheet)workbook.Worksheets.Add(miss, workbook.ActiveSheet);
}
if (sheetCount == 0)
{
sheet.Name = str;
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.Cells[1, i + 1] = dt.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dt.Rows.Count; i++)//所要添加的行数
{
for (int j = 0; j < dt.Columns.Count; j++)//每行的列数
{
//将数据填充到对应的单元格中
sheet.Cells[i + 2, j + 1] = dt.Rows[i].Cells[j].Value.ToString();
}
}
//设置表格样式
//设置列标题的背景颜色
Range er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, dt.Columns.Count]];
//重新选择单元格范围
int rowscount = dt.Rows.Count;//this.dataGridView1.Rows.Count;
int columncount = dt.Columns.Count;//this.dataGridView1.ColumnCount;
//将范围重新确定为每一行的第一个单元格
er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount, 1]];
//选中EXCEL所有表格
er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount + 1, columncount + 1]];
//让EXCEL中的所有单元格的列宽碎文字的长短自动调整
er.EntireColumn.AutoFit();
// 让EXCEL的文本水平居中方式
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;
}
else
{
DataTable dataTable = new DataTable();
dataTable.Columns.Add("图层类型");
dataTable.Columns.Add("编号");
layer = dt.Rows[sheetCount - 1].Cells["管线类型"].Value.ToString();
hxName = dt.Rows[sheetCount - 1].Cells["红线编号"].Value.ToString();
for (int i = 0; i < lineStrcut.Count; i++)
{
if (lineStrcut[i].layerName == layer && lineStrcut[i].hxName == hxName)
{
DataRow row = dataTable.NewRow();
row[0] = lineStrcut[i].layerName;
row[1] = lineStrcut[i].layerCode;
dataTable.Rows.Add(row);
}
}
if (featsList.Count > 0)
{
for (int j = 0; j < featsList.Count; j++)
{
GSOFeatures feats = featsList[j];
if (feats[0].Dataset.Caption.Contains(layer))
{
for (int n = 0; n < feats.Length; n++)
{
DataRow pointRow = dataTable.NewRow();
pointRow[0] = feats[n].Dataset.Caption;
pointRow[1] = feats[n].GetFieldAsString("编号");
dataTable.Rows.Add(pointRow);
}
}
}
}
if (!sheetNameList.Contains(layer))
{
sheet.Name = layer;
sheetNameList.Add(layer);
}
else
{
sheet.Name = layer + "1";
sheetNameList.Add(layer + "1");
}
for (int i = 0; i < dataTable.Columns.Count; i++)
{
sheet.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;
}
//填充数据
for (int i = 0; i < dataTable.Rows.Count; i++)//所要添加的行数
{
for (int j = 0; j < dataTable.Columns.Count; j++)//每行的列数
{
//将数据填充到对应的单元格中
sheet.Cells[i + 2, j + 1] = dataTable.Rows[i][j].ToString();
}
}
//设置表格样式
//设置列标题的背景颜色
Range er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, dataTable.Columns.Count]];
//重新选择单元格范围
int rowscount = dataTable.Rows.Count;//this.dataGridView1.Rows.Count;
int columncount = dataTable.Columns.Count;//this.dataGridView1.ColumnCount;
//将范围重新确定为每一行的第一个单元格
er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount, 1]];
//选中EXCEL所有表格
er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount + 1, columncount + 1]];
//让EXCEL中的所有单元格的列宽碎文字的长短自动调整
er.EntireColumn.AutoFit();
// 让EXCEL的文本水平居中方式
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;
}
}
string strSaveFile2 = strSaveFile;
if (File.Exists(strSaveFile2))
{
int z = 1;
do
{
strSaveFile2 = Path.Combine(Path.GetDirectoryName(strSaveFile) + "\\" + Path.GetFileNameWithoutExtension(strSaveFile) + z + ".xlsx");
z++;
} while (File.Exists(strSaveFile2));
}
workbook.SaveAs(strSaveFile2, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
//关闭表格
workbook.Close(false, miss, miss);
workbooks.Close();
//释放资源
excel.Quit();
}
/// <summary>
/// 杀死进程
/// </summary>
/// <param name="app"></param>
private static void KillProcess(Application app)
{
IntPtr t = new IntPtr(app.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
Process p = Process.GetProcessById(k);
p.Kill();
}
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
}
}
