DataTable中的图片为byte类型的string.
/// <summary>
/// 导出数据到Exce。
/// </summary>
public class ExcelExport
{
private static ExcelExport _instance = null;
private Microsoft.Office.Interop.Excel.Application _xlApp = null;
private Microsoft.Office.Interop.Excel.Workbooks _workbooks = null;
private Microsoft.Office.Interop.Excel.Workbook _workbook = null;
private Microsoft.Office.Interop.Excel.Worksheet _worksheet = null;
private Microsoft.Office.Interop.Excel.Range _range = null;
private object _objOpt = System.Reflection.Missing.Value;
private ExcelExport()
{ }
public static ExcelExport Instance
{
get
{
if (_instance == null)
_instance = new ExcelExport();
return _instance;
}
}
/// <summary>
/// 导出数据。
/// </summary>
/// <param name="dataTable">导出数据</param>
/// <returns>1:成功 0:失败</returns>
public bool DoExport(System.Data.DataTable dataTable, ProcessBoxManager pbManager)
{
bool isExport = true;
try
{
string xlsFile = TDataManager.getManager().CurrSection.FilePath + "\\" + TDataManager.getManager().CurrSection.Name + ".xlsx";
ExportExcel(dataTable, xlsFile, "画像", "C", 80, 45, true, pbManager);
}
catch (Exception e)
{
throw e;
}
return isExport;
}
/// <summary>
/// 导出数据到Excel。
/// </summary>
/// <param name="tmpDataTable">导出数据</param>
/// <param name="strFileName">导出文件名全路径</param>
/// <param name="imgColumnName">图片列名</param>
/// <param name="execlImageColumName">图片列在Eexcel中列名。如:A,B,C</param>
/// <param name="imgWidth">图片宽度</param>
/// <param name="imgHeight">图片高度</param>
/// <param name="isByte">图片数据为byte类型</param>
public void ExportExcel(System.Data.DataTable tmpDataTable, string strFileName, string imgColumnName, string execlImageColumName, int imgWidth, int imgHeight, bool isByte, ProcessBoxManager pbManager)
{
if (tmpDataTable == null)
{
return;
}
try
{
long rowNum = tmpDataTable.Rows.Count;//行数
int columnNum = tmpDataTable.Columns.Count;//列数
_xlApp = new Microsoft.Office.Interop.Excel.Application();
_xlApp.DisplayAlerts = false;//不显示更改提示
_xlApp.Visible = false;
_workbooks = _xlApp.Workbooks;
_workbook = _workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
_worksheet = (Microsoft.Office.Interop.Excel.Worksheet)_workbook.Worksheets[1];//取得sheet1
for (int i = 0; i < columnNum; i++) //写入列名
{
if (!tmpDataTable.Columns[i].ColumnName.Contains("空白列"))
{
//非空白列
_xlApp.Cells[1, i + 1] = tmpDataTable.Columns[i].ColumnName;
}
else
{
//空白列
_xlApp.Cells[1, i + 1] = "";
}
}
int r = 0;
pbManager.PushStep(tmpDataTable.Rows.Count);
for (r = 0; r < rowNum; r++)
{
#region
for (int i = 0; i < columnNum; i++)
{
行的共同属性
_range = (Microsoft.Office.Interop.Excel.Range)(_worksheet.Rows[r + 2, Type.Missing]);
_range.RowHeight = imgHeight + 14; //设置行高 +14 避免图片紧贴着单元格边线
图片
if (tmpDataTable.Columns[i].ColumnName == imgColumnName)
{
#region 图片列的共同设置
string strTemp = "";
for (int j = 0; j < (imgWidth / 3) + 2; j++)
{
strTemp += " ";
}
_xlApp.Cells[r + 2, i + 1] = strTemp; //添加空格,用于自动适应格式.免得图片位置不正确.
#endregion
if (isByte)
{
#region 保存图片并添加到excel---图片二进制字符串[请使用Convert.FromBase64String转换]
Byte[] imgbyte = Convert.FromBase64String(tmpDataTable.Rows[r][i].ToString());
MemoryStream my = new MemoryStream(imgbyte);
Image img = Image.FromStream(my);
string fileNameTemp = ""; //图片暂时路径.
try
{
string parentPath = System.Windows.Forms.Application.StartupPath;
string hzm = "jpg"; //默认jpg
#region 获取后缀名
//获取后缀名
if (img.RawFormat.Guid == ImageFormat.Gif.Guid)
{
hzm = ImageFormat.Gif.ToString();
}
if (img.RawFormat.Guid == ImageFormat.Png.Guid)
{
hzm = ImageFormat.Png.ToString();
}
if (img.RawFormat.Guid == ImageFormat.Bmp.Guid)
{
hzm = ImageFormat.Bmp.ToString();
}
#endregion
fileNameTemp = parentPath + "\\" + Guid.NewGuid().ToString() + "." + hzm;
img.Save(fileNameTemp);
InsertPicture(execlImageColumName + (r + 2), fileNameTemp, imgWidth, imgHeight);
}
catch (Exception e)
{
throw e;
// 单个图片未添加成功.不作处理
}
finally
{
File.Delete(fileNameTemp); //有没有成功都删除临时图片.
}
#endregion
}
}
else
{
if (i == 4)
{
_xlApp.Cells[r + 2, i + 1] = "'" + tmpDataTable.Rows[r][i].ToString();
}
else
{
_xlApp.Cells[r + 2, i + 1] = tmpDataTable.Rows[r][i].ToString();
}
}
}
#endregion
pbManager.NextStep();
}
pbManager.PopStep();
_worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
//_xlApp.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
_workbook.Saved = true;
_workbook.SaveCopyAs(strFileName);
_workbook.Close();
}
catch (Exception e)
{
throw e;
}
finally
{
KillSpecialExcel();
}
}
/// <summary>
/// 插入图片。
/// </summary>
/// <param name="RangeName">图片区域</param>
/// <param name="PicturePath">图片路径</param>
/// <param name="PictuteWidth">图片宽度</param>
/// <param name="PictureHeight">图片高度</param>
public void InsertPicture(string RangeName, string PicturePath, float PictuteWidth, float PictureHeight)
{
_range = _worksheet.get_Range(RangeName, _objOpt);
_range.Select();
float PicLeft, PicTop;
PicLeft = Convert.ToSingle(_range.Left);
PicTop = Convert.ToSingle(_range.Top);
//参数含义:
//图片路径
//是否链接到文件
//图片插入时是否随文档一起保存
//图片在文档中的坐标位置(单位:points)
//图片显示的宽度和高度(单位:points)
_worksheet.Shapes.AddPicture(PicturePath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight);
}
#region Kill Special Excel Process
/// <summary>
/// 取得线程号
/// </summary>
/// <param name="hWnd"></param>
/// <param name="lpdwProcessId"></param>
/// <returns></returns>
[DllImport("user32.dll", SetLastError = true)]
static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);
/// <summary>
/// 关闭Excel线程
/// </summary>
public void KillSpecialExcel()
{
try
{
if (_xlApp != null)
{
int lpdwProcessId;
GetWindowThreadProcessId(new IntPtr(_xlApp.Hwnd), out lpdwProcessId);
System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
}
}
catch (Exception)
{
}
}
#endregion
}
}