轉(zhuǎn)自//tc.first.blog.163.com/blog/static/4921072620114411314216/

通用的二維表導出的Excel格式，通過封裝一個操作類，傳遞一個DataTable參數(shù)，把數(shù)據(jù)導出。（Aspose.Cell下載）這種報表特點是操作方便，通用，能應付一般常用的數(shù)據(jù)報表，如下所示：

public class AsposeExcelTools
{

public static bool DataTableToExcel(DataTable datatable, string filepath, out string error)
{
error = "";
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 為空";
return false;
}

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;

int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
{
//------插入圖片數(shù)據(jù)-------
System.Drawing.Image image = (System.Drawing.Image)row[i];
MemoryStream mstream = new MemoryStream();
image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
sheet.Pictures.Add(nRow, i, mstream);
}
else
{
cells[nRow, i].PutValue(row[i]);
}
}
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
}
}

workbook.Save(filepath);
return true;
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return false;
}
}

public static bool DataTableToExcel2(DataTable datatable, string filepath, out string error)
{
error = "";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();

try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 為空";
return false;
}

//為單元格添加樣式
Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
//設置居中
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
//設置背景顏色
style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
style.Pattern = BackgroundType.Solid;
style.Font.IsBold = true;

int rowIndex = 0;
for (int i = 0; i < datatable.Columns.Count; i++)
{
DataColumn col = datatable.Columns[i];
string columnName = col.Caption ?? col.ColumnName;
wb.Worksheets[0].Cells[rowIndex, i].PutValue(columnName);
wb.Worksheets[0].Cells[rowIndex, i].Style = style;
}
rowIndex++;

foreach (DataRow row in datatable.Rows)
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
wb.Worksheets[0].Cells[rowIndex, i].PutValue(row[i].ToString());
}
rowIndex++;
}

for (int k = 0; k < datatable.Columns.Count; k++)
{
wb.Worksheets[0].AutoFitColumn(k, 0, 150);
}
wb.Worksheets[0].FreezePanes(1, 0, 1, datatable.Columns.Count);
wb.Save(filepath);
return true;
}
catch (Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return false;
}

}

/// <summary>
/// Excel文件轉(zhuǎn)換為DataTable.
/// </summary>
/// <param name="filepath">Excel文件的全路徑</param>
/// <param name="datatable">DataTable:返回值</param>
/// <param name="error">錯誤信息:返回錯誤信息，沒有錯誤返回""</param>
/// <returns>true:函數(shù)正確執(zhí)行 false:函數(shù)執(zhí)行錯誤</returns>
public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, out string error)
{
error = "";
datatable = null;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
datatable = null;
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(filepath);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
//-------------圖片處理-------------
Aspose.Cells.Pictures pictures = worksheet.Pictures;
if (pictures.Count > 0)
{
string error2 = "";
if (InsertPicturesIntoDataTable(pictures, datatable, out datatable, out error2) == false)
{
error = error + error2;
}
}
return true;
}
catch (System.Exception e)
{
error = e.Message;
return false;
}

}

public static bool ExcelFileToLists(string filepath, out IList[] lists, out string error)
{
error = "";
lists = null;
DataTable datatable = new DataTable();
IList list = new ArrayList();
Pictures[] pictures;
if (ExcelFileToDataTable(filepath, out datatable, out error) && GetPicturesFromExcelFile(filepath, out pictures, out error))
{
lists = new ArrayList[datatable.Rows.Count];
//------------DataTable轉(zhuǎn)換成IList[]--------------
//數(shù)據(jù)
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
lists[nRow] = new ArrayList(datatable.Columns.Count);
for (int i = 0; i <= datatable.Columns.Count - 1; i++)
{
lists[nRow].Add(row[i]);
}
nRow++;
}
//圖片
for (int i = 0; i < pictures.Length; i++)
{
foreach (Picture picture in pictures[i])
{
try
{
//----把圖片轉(zhuǎn)換成System.Drawing.Image----
//MemoryStream mstream = new MemoryStream();
//mstream.Write(picture.Data, 0, picture.Data.Length);
//System.Drawing.Image image = System.Drawing.Image.FromStream(mstream);
//----Image放入IList------
//圖片有可能越界
if (picture.UpperLeftRow <= datatable.Rows.Count && picture.UpperLeftColumn <= datatable.Columns.Count)
{
lists[picture.UpperLeftRow][picture.UpperLeftColumn] = picture.Data;
}

}
catch (System.Exception e)
{
error = error + e.Message;
}

}
}

}
else
{

return false;
}
return true;
}

public static bool ListsToExcelFile(string filepath, IList[] lists, out string error)
{
error = "";
//----------Aspose變量初始化----------------
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
//-------------輸入數(shù)據(jù)-------------
int nRow = 0;
sheet.Pictures.Clear();
cells.Clear();
foreach (IList list in lists)
{

for (int i = 0; i <= list.Count - 1; i++)
{
try
{
System.Console.WriteLine(i.ToString() + " " + list[i].GetType());
if (list[i].GetType().ToString() == "System.Drawing.Bitmap")
{
//插入圖片數(shù)據(jù)
System.Drawing.Image image = (System.Drawing.Image)list[i];

MemoryStream mstream = new MemoryStream();

image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);

sheet.Pictures.Add(nRow, i, mstream);
}
else
{
cells[nRow, i].PutValue(list[i]);
}
}
catch (System.Exception e)
{
error = error + e.Message;
}

}

nRow++;
}
//-------------保存-------------
workbook.Save(filepath);

return true;
}

這樣封裝了Aspose.Cell的操作，每次生成Excel文件或者導入Excel內(nèi)容，就非常方便，只需要如下調(diào)用方式即可完成：

private void button1_Click(object sender, EventArgs e)
{
DataTable dt = CreateTable("測試1,測試2,Test1,Test2", "testTable");
for (int i = 0; i < 100; i++)
{
DataRow dr = dt.NewRow();
for (int j = 0; j < dt.Columns.Count; j++)
{
dr[j] = i.ToString();
}
dt.Rows.Add(dr);
}

string outError = "";
string fileName = @"C:\test.xls";
AsposeExcelTools.DataTableToExcel2(dt, fileName, out outError);

if (!string.IsNullOrEmpty(outError))
{
MessageBox.Show(outError);
}
else
{
Process.Start(fileName);
}
}

public DataTable CreateTable(string nameString, string tableName)
{
string[] nameArray = nameString.Split(new char[] { ',', ';' });
List<string> nameList = new List<string>();
foreach (string item in nameArray)
{
if (!string.IsNullOrEmpty(item))
{
nameList.Add(item);
}
}

return CreateTable(nameList, tableName);
}