aspose导出数据

 

注意 aspose合并单元格后设置单元格样式要一格一格的设置

public class InvoiceAsposeExcel
{
/// <summary>
/// 导出数据
/// </summary>
/// <param name="path">路径</param>
/// <param name="invoiceCno">结算单号</param>
/// <param name="facilitator">服务商</param>
/// <param name="cinput">制单人</param>
/// <param name="charges">下家付款费用</param>
/// <param name="upAllData">上家详细数据，获取时间</param>
/// <param name="currency">币种数据</param>
/// <param name="clientdt">客户统计数据</param>
/// <param name="dt">明细统计数据</param>
public void ProcessData(string path, string invoiceCno, string facilitator, string cinput, List<nextHomeCharges> charges, List<RatherCostModel> upAllData, List<op_sys_dic> currency, DataTable clientdt, DataTable dt)
{
try
{
Workbook workbook = new Workbook();
workbook.Worksheets.Clear();//工作副清除
workbook.Worksheets.Add("结算单");//sheet的名称
int startRow = 0;//写入数据开始行的下标
Worksheet wsData = workbook.Worksheets[0];//工作副本

#region 表头
SetTitlt(workbook, wsData, "Times New Roman", startRow, 0, "SHENZHEN TOPWAY BAISHUNDA CARGO FORWARDING INTERNATIONAL CO.,LTD.", dt.Columns.Count, 14, true, 17, true, 0);
#endregion

wsData.AutoFitRows();

workbook.Save(path);
if (DevExpress.XtraEditors.XtraMessageBox.Show("保存成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
System.Diagnostics.Process.Start(path);//打开指定路径下的文件
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}

/// <summary>
/// 合并单元格
/// </summary>
private void SetPutValue(Worksheet sheet, Style style, int startRow, int c, int column, string value, bool isCenter = false, bool isBold = false, bool isGroundColor = false, bool isFontColor = false)
{
SetStyle(sheet, startRow, c, column, style, isCenter, isBold, isGroundColor, isFontColor);
PutValue(sheet, style, startRow, c, value, column);
}
/// <summary>
/// 默认不合并单元格
/// </summary>
private void SetDefaultPutValue(Worksheet sheet, Style style, int startRow, int c, string value, bool isCenter = false, bool isBold = false, bool isGroundColor = false, bool isFontColor = false)
{
SetStyle(style, isCenter, isBold, isGroundColor, isFontColor);
PutValue(sheet, style, startRow, c, value, 0);
}
/// <summary>
/// 下家数据填充
/// </summary>
private void PutValue(Worksheet sheet, Style style, int startRow, int c, string name, int column)
{
sheet.Cells[startRow, c].PutValue(name);
sheet.Cells[startRow, c].SetStyle(style);
for (int i = 1; i < column; i++)
{
sheet.Cells[startRow, c + i].SetStyle(style);
}
}

private void SetClientCell(int startRow, Workbook workbook, Worksheet sheet, DataTable dt)
{
Style style = workbook.Styles[workbook.Styles.Add()];//新增样式
int c = 0;
for (int row = 0; row < dt.Rows.Count; row++)
{
c = 0;
bool isData = false;
for (int column = 0; column < dt.Columns.Count; column++)
{
if (isData)
{
SetStyle(sheet, row + startRow, c, 2, style, false, false, isData, isData);
isData = false;
}
else
{
isData = ExistsData(dt.Rows[row][column].ToString());
SetStyle(sheet, row + startRow, c, 2, style, false, false, isData, isData);
}
sheet.Cells[row + startRow, c].PutValue(dt.Rows[row][column].ToString());
sheet.Cells[row + startRow, c].SetStyle(style);
sheet.Cells[row + startRow, c + 1].SetStyle(style);//空数据设置样式
style = workbook.Styles[workbook.Styles.Add()];//新增样式
c = c + 2;
}
}
}

/// <summary>
/// 明细统计数据
/// </summary>
private void SetCell(int startRow, Workbook workbook, Worksheet sheet, DataTable dt)
{
Style style = workbook.Styles[workbook.Styles.Add()];//新增样式
SetStyle(style, true, true);
//生成字段名称
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.Cells[startRow, i].PutValue(dt.Columns[i].ToString());
sheet.Cells[startRow, i].SetStyle(style);
sheet.Cells.SetColumnWidth(i, 8.3);//设置列宽
}
//填充数据
SetStyle(style, false, false);
for (int row = 0; row < dt.Rows.Count; row++)
{
for (int column = 0; column < dt.Columns.Count; column++)
{
sheet.Cells[row + startRow + 1, column].PutValue(dt.Rows[row][column].ToString());
sheet.Cells[row + startRow + 1, column].SetStyle(style);
}
}
//sheet.AutoFitColumns();
}

#region 结尾数据
private void SetEndData(int startRow, Workbook workbook, Worksheet sheet, string cinput)
{
Style style = workbook.Styles[workbook.Styles.Add()];//新增样式
int c = 0;
SetPutValue(sheet, style, startRow, c, 2, "制单:" + cinput, false, true);
c = c + 2;
SetPutValue(sheet, style, startRow, c, 2, "初审:", false, true);
c = c + 2;
SetPutValue(sheet, style, startRow, c, 3, "外围结算:", false, true);
c = c + 3;
SetPutValue(sheet, style, startRow, c, 3, "审核:", false, true);
c = c + 3;
SetPutValue(sheet, style, startRow, c, 3, "出纳:", false, true);
c = c + 3;
SetPutValue(sheet, style, startRow, c, 3, "会计:", false, true);

startRow++; c = 0;
SetPutValue(sheet, style, startRow, c, 2, DateTime.Now.ToString("yyy.MM.dd"), false, true);
c = c + 2;
SetPutValue(sheet, style, startRow, c, 2, "", false, true);
c = c + 2;
SetEmpty(sheet, style, startRow, 4, c);

startRow++; c = 0;
SetData(sheet, style, startRow);

startRow++; c = 0;
SetEmpty(sheet, style, startRow, 5, c);
}
/// <summary>
/// 数据为空
/// </summary>
private void SetEmpty(Worksheet sheet, Style style, int startRow, int j, int c)
{
for (int i = 0; i < j; i++)
{
if (i == 4)
SetPutValue(sheet, style, startRow, c, 4, "", false, true);
else
SetPutValue(sheet, style, startRow, c, 3, "", false, true);
c = c + 3;
}
}
/// <summary>
/// 设置数据
/// </summary>
private void SetData(Worksheet sheet, Style style, int startRow, int c = 0)
{
List<string> detail = new List<string>() { "复审:", "BSD结算:", "终审:", "主管审核:", "总经理审核:" };
for (int i = 0; i < detail.Count; i++)
{
if (i == 4)
SetPutValue(sheet, style, startRow, c, 4, detail[i], false, true);
else
SetPutValue(sheet, style, startRow, c, 3, detail[i], false, true);
c = c + 3;
}
}
#endregion

#region 样式
/// <summary>
/// 设置标题
/// </summary>
/// <param name="workbook">工作本</param>
/// <param name="sheet">工作簿</param>
/// <param name="fontName">字体名称</param>
/// <param name="startRow">行</param>
/// <param name="column">列</param>
/// <param name="titlt">文字</param>
/// <param name="columnsCount">合并的列的长度</param>
/// <param name="size">字体大小</param>
/// <param name="isBold">是否加粗</param>
/// <param name="rowHeight">行高</param>
private void SetTitlt(Workbook workbook, Worksheet sheet, string fontName, int startRow, int column, string titlt, int columnsCount, int size, bool isBold, double rowHeight, bool isAlignment = true, double columnWidth = 0)
{
Style style = workbook.Styles[workbook.Styles.Add()];//新增样式
if (isAlignment)
style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
else
style.HorizontalAlignment = TextAlignmentType.Left;//文字居中
style.Font.Name = fontName;
style.Font.Size = size;
style.Font.IsBold = isBold;
sheet.Cells.Merge(startRow, column, 1, columnsCount);//合并单元格
sheet.Cells.SetRowHeight(0, rowHeight);//设置行高
if (columnWidth > 0)
{
sheet.Cells.SetColumnWidth(0, columnWidth);//设置列宽
//sheet.Cells.SetColumnWidthInch(0, columnWidth);//设置列宽
}
style.IsTextWrapped = true;
sheet.Cells[startRow, column].PutValue(titlt);//添加内容
sheet.Cells[startRow, column].SetStyle(style);
}

private void SetStyle(Worksheet sheet, int startRow, int column, int columnsCount, Style style, bool isCenter, bool isBold, bool isGroundColor = false, bool isFontColor = false)
{
SetStyle(style, isCenter, isBold, isGroundColor, isFontColor);
sheet.Cells.Merge(startRow, column, 1, columnsCount);//合并单元格
}

/// <summary>
/// 数据样式
/// </summary>
private void SetStyle(Style style, bool isCenter, bool isBold, bool isGroundColor = false, bool isFontColor = false)
{
style.Pattern = Aspose.Cells.BackgroundType.Solid;//边框
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;//应用边界线 左边界线
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;//应用边界线 上边界线
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;//应用边界线 下边界线
//style.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
if (isCenter)
style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
else
style.HorizontalAlignment = TextAlignmentType.Left;
style.Font.Name = "宋体";
style.Font.Size = 9;
style.Font.IsBold = isBold;
style.IsTextWrapped = true;
if (isGroundColor)
style.ForegroundColor = System.Drawing.Color.FromArgb(255, 255, 0);//设置背景色 可以参考颜色代码对照表
//else
//{
// style.ForegroundColor = System.Drawing.Color.FromArgb(255, 255, 255);//设置背景色 可以参考颜色代码对照表
//}
if (isFontColor)
style.Font.Color = Color.Red;
//else
// style.Font.Color = Color.Black;
}
#endregion
}

 

导入excel变DataTable

//路径    列名
protected DataTable ReadExcel(string filename, bool HDR = true)
        {
            Workbook workbook = new Workbook(filename);
            Worksheet worksheet = workbook.Worksheets[0];
            if (HDR)
                return worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxDataRow + 1, worksheet.Cells.MaxDataColumn + 1, true);
            else
                return worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxDataRow + 1, worksheet.Cells.MaxDataColumn + 1);
        }