使用VSTO导出勘误表 #1
yaclty
commented
Owner
```csharp
/// <summary>
/// 使用Excel COM导出
/// </summary>
/// <param name="fileName"></param>
private static void ProcessExportByExcelCOM(string fileName)
{
// 创建计时器,用于计算运行时间
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
// 获取要导出的数据
var exportDataItems = ExportDataItem.GetExportData(Globals.ThisAddIn.ActiveDocument.marks);
// 创建Excel对象
var excel = Globals.ThisAddIn.GetExcel();
Excel.Workbook workbook = excel.Workbooks.Add();
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
// 设置表头
string[] headerTitles =
{
"序号","页","行","详细信息","异常", "建议","处理状态"
};
for (int i = 0; i < headerTitles.Length; i++)
{
var colIndex = i + 1;
worksheet.Cells[1, colIndex] = headerTitles[i];
}
int rowIndex = 2;
foreach (var item in exportDataItems)
{
var it = item.Item;
// 写入数据
// 1. 序号
worksheet.Cells[rowIndex, 1] = rowIndex - 1;
// 2. 页码
worksheet.Cells[rowIndex, 2] = item.PageNumber;
// 3. 行号
worksheet.Cells[rowIndex, 3] = item.LineNumber;
// 4. 引用原文(详细信息)
#region 引用原文(详细信息)
string originSentence = item.OriginSentence.TrimEnd();
var startIndex = it.Tag == "i" || it.Start < originSentence.Length - 1 ? it.Start : originSentence.IndexOf(it.Origin);
var originCell = worksheet.Cells[rowIndex, 4];
originCell.Value2 = originSentence;
Excel.Characters originChars = originCell.Characters[it.Start + 1, it.Tag == "i" ? it.Text.Length : it.Origin.Length]; // 参数：起始位置, 字符数
originChars.Font.Color = ColorTranslator.ToOle(Color.Red);
#endregion
// 5. 异常
worksheet.Cells[rowIndex, 5] = it.Origin;
// 6. 建议
#region 建议
var suggestCell = worksheet.Cells[rowIndex, 6];
var suggest = it.Text;
if (it.Tag == "r")
{
string tag = "";
if (it.Type == "blacklist")
{
tag = "黑名单";
}
else
{
// 易错词 标点 不标识
if (it.Type == "confusion" || it.Type == "model" || it.Type == "other" || it.Type == "punctuation")
{
//row.CreateCell(5).SetCellValue(suggest);
}
else if (!string.IsNullOrEmpty(it.Type) && ExportConfig.ErrorTypeMap.ContainsKey(it.Type.ToLower()))
{
tag = $"【提示】{ExportConfig.ErrorTypeMap[it.Type.ToLower()]}";
}
else if (!string.IsNullOrEmpty(it.Addition) && it.Addition.Trim().Length > 0 && it.Addition != "提示")
{
tag = $"【提示】{it.Addition}";
}
}
if (!string.IsNullOrEmpty(tag))
{
suggestCell.Value2 = suggest + $" {tag}";
Excel.Characters suggestChars = suggestCell.Characters[suggest.Length + 2, tag.Length];
var xx = suggestChars.Text;
suggestChars.Font.Name = "黑体";
}
else
{
suggestCell.Value2 = suggest;
}
}
else
{
string tag = "";
startIndex = 0;
if (it.Type == "incorrect_expression")
{
tag = "表述有误";
}
else if (it.Type == "sensitive")
{
tag = "敏感词";
}
else if (it.Type == "blacklist" || it.Type == "blacklist_mask")
{
tag = "黑名单";
}
else if (it.Type == "fallen_officers")
{
tag = "疑似落马官员";
}
else if (it.Tag == "i")
{
startIndex = it.Text.Length + 1;
tag = it.Text + " 新增";
}
else if (it.Tag == "d")
{
tag = "删除";
}
if (tag.Length > 0)
{
suggestCell.Value2 = tag;
Excel.Characters suggestChars = suggestCell.Characters[startIndex + 1, it.Tag == "i" ? startIndex + 2 : tag.Length]; // 参数：起始位置, 字符数
suggestChars.Font.Name = "黑体";
}
else
{
suggestCell.Value2 = suggest;
}
}
#endregion
// 7. 处理状态
worksheet.Cells[rowIndex, 7] = StatusText(it.IsAccept);
rowIndex++;
}
#region 样式设置
var headerRange = worksheet.Range["A1:G1"];
var allRange = worksheet.Range["A1:G" + rowIndex];
// 对齐方式
// 所有都是垂直居中
allRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
// 水平居中
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
worksheet.Columns["A:C"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
worksheet.Columns["G"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// 全局样式
allRange.Font.Size = 11;
worksheet.Rows[1].RowHeight = 40;
// 设置自动换行
worksheet.Rows.WrapText = true;
//var themeColor = new XSSFColor(new byte[] { 214, 170, 105 }); // 首行背景色黑色，字体用主题金色
// 设置背景/颜色
headerRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 214, 170, 105));
headerRange.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
headerRange.Interior.Color = ColorTranslator.ToOle(Color.Black);
// 设置字体
worksheet.Rows[1].Font.Name = "黑体";
worksheet.Columns["G"].Font.Name = "黑体";
// 设置列宽
worksheet.Columns["A:C"].ColumnWidth = 10;
worksheet.Columns["D"].ColumnWidth = 80;
worksheet.Columns["E:F"].ColumnWidth = 20;
worksheet.Columns["G"].ColumnWidth = 10;
// 设置冻结、筛选
worksheet.Activate();
worksheet.Range["B1"].Select(); // 必须选择冻结点
// 冻结第一行
excel.ActiveWindow.SplitRow = 1;
excel.ActiveWindow.FreezePanes = true;
worksheet.EnableAutoFilter = true;
// 设置第一行筛选
//int lastRow = worksheet.UsedRange.Rows.Count;
Excel.Range range = worksheet.get_Range("A1", "D1");
range.AutoFilter("1", "<>", Excel.XlAutoFilterOperator.xlOr, "", true);
#endregion
stopwatch.Stop();
MessageBox.Show("导出完成,运行时间：" + stopwatch.Elapsed.TotalMilliseconds + "毫秒");
// 保存
workbook.SaveAs(fileName);
// 关闭对象释放资源
workbook.Close();
Marshal.FinalReleaseComObject(worksheet);
Marshal.FinalReleaseComObject(workbook);
}
```
Loading…
x
Reference in New Issue
Block a user
No description provided.
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?