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.Visible = false;
Missing miss = Missing.Value;
Workbooks workbooks = excel.Workbooks;
Workbook workbook = workbooks.Add(miss);
Worksheet sheet = workbook.Worksheets[1];
AddDataGridViewDataToSheet(sheet, dt, str);
SaveExcel(strSaveFile, excel, workbook, workbooks, miss);
}
/// <summary>
/// 将DataTable保存为Excel文件,两个SHEET
/// </summary>
public static void ExpToExcel(DataGridView dt1, DataGridView dt, string strSaveFile)
{
Application excel = new Application();
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];
AddDataGridViewDataToSheet(sheet, dt, "垂直距离");
AddDataGridViewDataToSheet(sheet1, dt1, "水平距离");
SaveExcel(strSaveFile, excel, workbook, workbooks, miss);
}
static List<String> sheetNameList = new List<string>();
/// <summary>
/// 导出EXCEL多个SHEET(红线审核导出用)
/// </summary>
/// <param name="dt"></param>
/// <param name="strSaveFile"></param>
public static void ExpToExcelWithManySheet(DataGridView dt, string strSaveFile, string str)
{
if (dt.Rows.Count == 0) return;
sheetNameList.Clear();
string layerName = "";
string redLineName = "";
Application excel = new Application();//创建一个EXCEL应用程序
excel.Visible = false; //是否显示导出过程（显示创建后的EXCEL）
Missing miss = Missing.Value;//定义缺省值
Workbooks workbooks = excel.Workbooks;//创建一个新的工作簿
Workbook workbook = workbooks.Add(miss);
Worksheet sheet = workbook.Worksheets[1];
AddDataGridViewDataToSheet(sheet, dt, str);
for (int sheetCount = 0; sheetCount < dt.Rows.Count; sheetCount++)
{
if (workbook.Worksheets.Count >(sheetCount+1))
sheet = workbook.Worksheets[sheetCount + 2];
else
sheet = (Worksheet) workbook.Worksheets.Add(miss, workbook.ActiveSheet);
DataTable dataTable = new DataTable();
dataTable.Columns.Add("图层类型");
dataTable.Columns.Add("编号");
layerName = dt.Rows[sheetCount].Cells["管线类型"].Value.ToString();
redLineName = dt.Rows[sheetCount].Cells["红线编号"].Value.ToString();
AddDataTableDataToSheet(sheet, layerName, redLineName, sheetCount, dataTable);
}
SaveExcel(strSaveFile, excel, workbook, workbooks, miss);
}
static void AddDataGridViewDataToSheet(Worksheet sheet, DataGridView dt,string str)
{
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(); //将数据填充到对应的单元格中
}
}
SheetStype(sheet, dt);
}
static void AddDataTableDataToSheet(Worksheet sheet,string layerName,string redLineName,int sheetCount,DataTable dataTable)
{
for (int i = 0; i < RedLineAnalysisTool.redLineResultList[sheetCount].LineFeaturesInRedLine.Length; i++)
{
if (RedLineAnalysisTool.redLineResultList[sheetCount].LineFeaturesInRedLine[i].Dataset.Caption ==
layerName && RedLineAnalysisTool.redLineResultList[sheetCount].RedLineName == redLineName)
{
DataRow row = dataTable.NewRow();
row[0] = RedLineAnalysisTool.redLineResultList[sheetCount].LineFeaturesInRedLine[i].Dataset.Caption;
row[1] = RedLineAnalysisTool.redLineResultList[sheetCount].LineFeaturesInRedLine[i].Name;
dataTable.Rows.Add(row);
}
}
if (RedLineAnalysisTool.redLineResultList[sheetCount].PointFeaturesInRedLine != null)
{
if (RedLineAnalysisTool.redLineResultList[sheetCount].PointFeaturesInRedLine.Length > 0)
{
for (int j = 0; j < RedLineAnalysisTool.redLineResultList[sheetCount].PointFeaturesInRedLine.Length; j++)
{
GSOFeatures feats = RedLineAnalysisTool.redLineResultList[sheetCount].PointFeaturesInRedLine;
if (feats[0].Dataset.Caption.Contains(layerName))
{
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(layerName))
{
sheet.Name = layerName;
sheetNameList.Add(layerName);
}
else
{
sheet.Name = layerName + "1";
sheetNameList.Add(layerName + "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(); //将数据填充到对应的单元格中
}
}
SheetStype(sheet, dataTable);
}
static void SaveExcel(string strSaveFile,Application excel, Workbook workbook, Workbooks workbooks, Missing miss)
{
if (File.Exists(strSaveFile))
{
int z = 1;
do
{
strSaveFile = Path.Combine(Path.GetDirectoryName(strSaveFile) + "\\" + Path.GetFileNameWithoutExtension(strSaveFile) + z + ".xlsx");
z++;
} while (File.Exists(strSaveFile));
}
workbook.SaveAs(strSaveFile, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
workbook.Close(false, miss, miss); //关闭表格
workbooks.Close();
excel.Quit();//释放资源
}
static void SheetStype(Worksheet sheet,DataGridView dataTable)
{
int rowscount = dataTable.Rows.Count;//重新选择单元格范围
int columncount = dataTable.Columns.Count;
Range er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount + 1, columncount + 1]];//选中EXCEL所有表格
er.EntireColumn.AutoFit();//让EXCEL中的所有单元格的列宽碎文字的长短自动调整
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 让EXCEL的文本水平居中方式
}
static void SheetStype(Worksheet sheet, DataTable dataTable)
{
int rowscount = dataTable.Rows.Count;//重新选择单元格范围
int columncount = dataTable.Columns.Count;
Range er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount + 1, columncount + 1]];//选中EXCEL所有表格
er.EntireColumn.AutoFit();//让EXCEL中的所有单元格的列宽碎文字的长短自动调整
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 让EXCEL的文本水平居中方式
}
}
}
