using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Xml;
using System.Data;
using System.IO;
using System.Data.OleDb;
using Aspose.Cells;
using System.Reflection;
namespace Utils
{
///
/// 解析Excel文件
///
public class ExcelUitl
{
public static DataSet ExcelToDS(string Path)
{
DataSet ds = new DataSet();
Workbook workbook = new Workbook();
workbook.Open(Path);
Cells cells = workbook.Worksheets[0].Cells;
DataTable dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
ds.Tables.Add(dt);
return ds;
}
public static DataSet ExcelToDS(string Path, string sheetName)
{
DataSet ds = new DataSet();
Workbook workbook = new Workbook();
workbook.Open(Path);
Worksheet sheet = workbook.Worksheets[sheetName];
if (sheet == null)
{
return null;
}
Cells cells = sheet.Cells;
DataTable dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
ds.Tables.Add(dt);
return ds;
}
public static DataSet GetTableList(string Path)
{
DataSet ds = new DataSet();
Workbook workbook = new Workbook();
workbook.Open(Path);
if (workbook.Worksheets != null)
{
foreach (Worksheet sheet in workbook.Worksheets)
{
Cells cells = sheet.Cells;
DataTable dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
dt.TableName = sheet.Name;
ds.Tables.Add(dt);
}
}
return ds;
}
///
///
///
///
///
public static void ImportAndZip(List
{
Workbook workbook = new Workbook();
int sheetIndex = 0;
string dir = excelFilePath.Replace(Path.GetFileName(excelFilePath), "");
string txtdir = Path.Combine(dir, "sco");
if (!Directory.Exists(txtdir))
{
Directory.CreateDirectory(txtdir);
}
DataTable error = new DataTable();
error.TableName = "导出情况表";
error.Columns.Add(new DataColumn()
{
ColumnName = "错误工作簿名称",
});
error.Columns.Add(new DataColumn()
{
ColumnName = "错误工作簿行",
});
error.Columns.Add(new DataColumn()
{
ColumnName = "错误工作簿列名",
});
error.Columns.Add(new DataColumn()
{
ColumnName = "错误原因",
});
error.Columns.Add(new DataColumn()
{
ColumnName = "导入路径",
});
foreach (DataTable table in tablelist)
{
Worksheet sheet = null;
if (sheetIndex == 0)
{
sheet = workbook.Worksheets[sheetIndex];
sheet.Name = table.TableName;
}
else
{
sheet = workbook.Worksheets.Add(table.TableName);
}
//标题
int indexX = 0, indexY = 0;
foreach (DataColumn column in table.Columns)
{
sheet.Cells[indexX, indexY].PutValue(column.ColumnName);
indexY++;
}
int Colnum = table.Columns.Count;//表格列数
int Rownum = table.Rows.Count;//表格行数
//生成数据行
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum; k++)
{
try
{
//单元格最大存 32k内容
sheet.Cells[1 + i, k].PutValue(table.Rows[i][k].ToString()); //添加数据
}
catch (Exception ex)
{
string message = "";
string txtName = table.TableName + "_" + i + ".txt";
string txtPath = "";
if (ex.Message.ToString().Contains("MS Excel only allows to put a string shorter than 32K to a Cell"))
{
message = "单元格数据大于32K不能写入excel";
//将内容写入txt
File.WriteAllText(Path.Combine(txtdir, txtName), table.Rows[i][k].ToString());
txtPath = "sco/" + txtName;
}
else
{
message = ex.Message.ToString();
}
//sun： 当数据太大。我们不导入excel,将文本导入txt,路径路径
error.Rows.Add(new object[] { sheet.Name, i, table.Columns[k].ColumnName, message, txtPath });
}
}
}
SetHeaderStyle(sheet);
//sheet个数
sheetIndex++;
}
//将错误信息表也写入excel
Worksheet errrsheet = workbook.Worksheets.Add(error.TableName);
for (int i = 0; i < error.Columns.Count; i++)
{
errrsheet.Cells[0, i].PutValue(error.Columns[i].ColumnName);
}
//foreach (DataColumn column in error.Columns)
//{
// errrsheet.Cells[0, 0].PutValue(column.ColumnName);
//}
int eColnum = error.Columns.Count;//表格列数
int eRownum = error.Rows.Count;//表格行数
for (int i = 0; i < eRownum; i++)
{
for (int k = 0; k < eColnum; k++)
{
errrsheet.Cells[1 + i, k].PutValue(error.Rows[i][k].ToString()); //添加数据
}
}
SetHeaderStyle(errrsheet);
workbook.Save(excelFilePath);
}
///
/// 创建excel
///
///
///
public static void CreateExcel(List
{
Workbook workbook = new Workbook();
int sheetIndex = 0;
foreach (DataTable table in tablelist)
{
Worksheet sheet = null;
if (sheetIndex == 0)
{
sheet = workbook.Worksheets[sheetIndex];
sheet.Name = table.TableName;
}
else
{
sheet = workbook.Worksheets.Add(table.TableName);
}
//标题
int indexX = 0, indexY = 0;
foreach (DataColumn column in table.Columns)
{
sheet.Cells[indexX, indexY].PutValue(column.ColumnName);
indexY++;
}
int Colnum = table.Columns.Count;//表格列数
int Rownum = table.Rows.Count;//表格行数
//生成数据行
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum; k++)
{
try
{
//单元格最大存 32k内容
sheet.Cells[1 + i, k].PutValue(table.Rows[i][k].ToString()); //添加数据
}
catch (Exception ex)
{
//sun： 当数据太大。我们不导入
}
}
}
SetHeaderStyle(sheet);
//sheet个数
sheetIndex++;
}
workbook.Save(path);
}
public static void SetHeaderStyle(Worksheet sheet)
{
#region header style
Style style = new Style();
style.Font.IsBold = true;
style.Font.Size = 12;
StyleFlag styleFlag = new StyleFlag
{
FontBold = true,
FontSize = true,
};
sheet.Cells.ApplyRowStyle(0, style, styleFlag);
sheet.AutoFitColumns();
#endregion
}
public static void SetBodyStyle(Worksheet sheet, int column)
{
#region header style
Style style = new Style();
style.Font.IsBold = false;
style.Font.Size = 11;
StyleFlag styleFlag = new StyleFlag
{
FontBold = true,
FontSize = true
};
sheet.Cells.ApplyColumnStyle(column, style, styleFlag);
sheet.AutoFitColumns();
#endregion
}
}
}