public class DataSetToExcel
{
///
/// 将数据集中的数据导出到EXCEL文件,可导出乡村代码
///
/// 输入数据集
/// 是否显示该EXCEL文件
/// 保存路径
///
public static bool DStoExcel(DataSet dataSet, bool isShowExcle, string excelFileFullPath)
{
//获取Dataset中的一个表
DataTable dataTable = dataSet.Tables[0];//Dataset数据集中的第一个表
int rowNumber = dataTable.Rows.Count;//行数，不包括字段名
int columnNumber = dataTable.Columns.Count;//列数
int colIndex = 0;
if (rowNumber == 0)
{
return false;
}
//建立Excel对象
Application excel = new Application();
Workbook workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet
);
Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
excel.Visible = isShowExcle;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
//生成字段名称（表头）
foreach (DataColumn col in dataTable.Columns)//遍历表中所有列
{
colIndex++;
if (colIndex ==4||colIndex==5)//获取4列或者5列表头，下标从1开始，1对应第一列
{
excel.Cells[1, colIndex-3] = col.ColumnName;//遍历第一行中各列
}
excel.Cells[1, 3] = "备注";//设置一个新的列，表头
}
//生成表的主体
object[,] objData = new object[rowNumber, columnNumber];
for (int r = 0; r < rowNumber; r++)//表的正文是以下标从0开始的，也就是标题栏下面是每行每列是从0开始的
{
for (int c = 3; c <=4; c++)//列，原始表的4和5列
{
objData[r, c-3] = dataTable.Rows[r][c];
}
}
// 写入Excel
var range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];//2行一列开始逐个写入
range.NumberFormat = "@";//设置单元格为文本格式
range.Value2 = objData;
#region 设置表格式
//设置表格中格式
//var range1 = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]].NumberFormat = "yyyy-m-d h:mm";
var range1 = worksheet.Range[excel.Cells[2, 5], excel.Cells[rowNumber + 1, 5]];
range1.NumberFormat = "000000";//格式为邮政编码
var range2 = worksheet.Range[excel.Cells[2, 2], excel.Cells[rowNumber + 1, 2]];//第八列全部设置为邮编格式
range2.NumberFormat = "000000";
range2.HorizontalAlignment = 2;//设置居中
//var range3 = worksheet.Range[excel.Cells[2, 4], excel.Cells[rowNumber, 4]];
//worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber, 1]].Insert(range3);
#endregion
//保存Excel文件
//workbook.SaveCopyAs(excelFileFullPath);
workbook.SaveAs(excelFileFullPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
// @"E:\MyTemp\"
return true;
}
/// <summary>
/// 重写上面方法，只是没有保存到本地的功能
/// </summary>
/// <param name="dataSet"></param>
/// <param name="isShowExcle"></param>
/// <returns></returns>
public static bool DStoExcel(DataSet dataSet, bool isShowExcle)
{
//获取Dataset中的一个表
DataTable dataTable = dataSet.Tables[1];
int rowNumber = dataTable.Rows.Count;//行数，不包括字段名
int columnNumber = dataTable.Columns.Count;//列数
int colIndex = 0;
if (rowNumber == 0)
{
return false;
}
//建立Excel对象
Application excel = new Application();
Workbook workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet
);
Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
excel.Visible = isShowExcle;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
//生成字段名称（表头）
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
if (colIndex<=8)//一直到H（8）列
{
excel.Cells[1, colIndex] = col.ColumnName;//遍历第一行中各列
}
}
object[,] objData = new object[rowNumber, columnNumber];
for (int r = 0; r < rowNumber; r++)//表的正文是以下标从0开始的，也就是标题栏下面是每行每列是从0开始的
{
for (int c = 0; c <=7; c++)//列
{
objData[r, c] = dataTable.Rows[r][c];
}
}
// 写入Excel
var range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];//2行一列开始逐个写入
range.NumberFormat = "@";//设置单元格为文本格式
range.Value2 = objData;
//var range1 = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]].NumberFormat = "yyyy-m-d h:mm";
var range1 = worksheet.Range[excel.Cells[2, 5], excel.Cells[rowNumber + 1, 5]];
range1.NumberFormat = "000000";//格式为邮政编码
var range2 = worksheet.Range[excel.Cells[2, 8], excel.Cells[rowNumber + 1, 8]];//第八列全部设置为邮编格式
range2.NumberFormat = "000000";
// @"E:\MyTemp"
return true;
}
/// <summary>
/// 可以将DataSet中某个Datatable导入到excel中，便于查看
/// </summary>
/// <param name="dt">datatable</param>
/// <param name="isOpen">是否打开</param>
/// <returns></returns>
public static bool DatataleToExcel(DataTable dt, bool isOpen)
{
if (dt != null)
{
object obj = Missing.Value;
//建立一个新的excel,并在其中添加workbook
Microsoft.Office.Interop.Excel.Application excel = new ApplicationClass();
Workbook workbook = excel.Workbooks.Add(true);
Worksheet xlSheet = (Worksheet)workbook.Worksheets[1];
xlSheet.Name = dt.TableName;
int colIndex = 1;
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
//处理列名
for (int i = 0; i < colCount; i++)
{
xlSheet.Cells[1,colIndex] = dt.Columns[i].ColumnName;
colIndex++;
}
//处理行，填充到worksheet
object[,] objData = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
objData[i,j] = dt.Rows[i][j];
}
}
//设置单元格格式
var range = xlSheet.Range[excel.Cells[2, 1], excel.Cells[rowCount + 1, colCount]];
range.Value2 = objData;
range.NumberFormat = "@";//设置单元格为文本格式
excel.Visible = isOpen;
}
return true;
}
}