|
|
楼主 |
发表于 2021-9-9 09:50:38
|
显示全部楼层
//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop
#include "Unit1.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
}
static int StrBufToIntBuf (const char *str, int *idx)
{
int n = 0;
while(*str){
if((str[0]>='0') && (str[0]<='9')){
idx[n++] = atoi(str);
while(*str){
if(str[0] == ' '){
str++;
break;
}
else{
str++;
}
}
}
else{
str++;
}
}
return n;
}
void InsertSort(int a[], int n)
{
for(int i= 1; i<n; i++){
if(a[i] < a[i-1]){ //若第i个元素大于i-1元素，直接插入。小于的话，移动有序表后插入
int j= i-1;
int x = a[i]; //复制为哨兵，即存储待排序元素
a[i] = a[i-1]; //先后移一个元素
while(x < a[j]){ //查找在有序表的插入位置
a[j+1] = a[j];
j--; //元素后移
}
a[j+1] = x; //插入到正确位置
}
}
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
int col, row;
int ycnt = 0;
int d = 0;
int r = 0;
int Idx[100000];
char Str[100000];
int Cnt;
if (OpenDialog1->Execute())
{
Edit1->Text=OpenDialog1->FileName;
Variant vExcelApp, Sheet1, WorkBook1, Range;
vExcelApp = Variant::CreateObject("Excel.Application");
//打开文件
vExcelApp.OlePropertyGet("workbooks").OleFunction("Open", OpenDialog1->FileName.t_str());
//创建工作薄对象（此时该对象得到的是已打开文件的第一个工作薄）
WorkBook1 = vExcelApp.OlePropertyGet("ActiveWorkBook");
//创建工作表对象（此时该对象得到的是已打开文件的第一个工作表）
Sheet1 = WorkBook1.OlePropertyGet("ActiveSheet");
col = Sheet1.OlePropertyGet("UsedRange").OlePropertyGet("Columns").OlePropertyGet("Count"); //列数
row = Sheet1.OlePropertyGet("UsedRange").OlePropertyGet("Rows").OlePropertyGet("Count"); //行数
StringGrid1->ColCount = col + 1;
StringGrid1->RowCount = row + 1;
StringGrid1->Cells[0][0] = "序号";
for(int y=1; y<StringGrid1->RowCount; y++){
char buf[100];
sprintf(buf, "%u", y);
StringGrid1->Cells[0][y] = buf;
}
for(int x=1; x<StringGrid1->ColCount; x++){
for(int y=1; y<StringGrid1->RowCount; y++){
String str = Sheet1.OlePropertyGet("Cells", y, x).OlePropertyGet("Value");
StringGrid1->Cells[x][y] = str;
}
}
if(StringGrid1->ColCount < 4){
return;
}
for(int y=1; y<StringGrid1->RowCount; y++){
if(StringGrid1->Cells[2][y] != ""){
for(int n=y+1; n<StringGrid1->RowCount; n++){
if( (StringGrid1->Cells[1][n] == StringGrid1->Cells[1][y])
&& (StringGrid1->Cells[3][n] == StringGrid1->Cells[3][y])){
AnsiString s1 = StringGrid1->Cells[2][y];
AnsiString s2 = StringGrid1->Cells[2][n];
s1 += " ";
s1 += s2;
StringGrid1->Cells[2][y] = s1;
StringGrid1->Cells[1][n] = "";
StringGrid1->Cells[2][n] = "";
StringGrid1->Cells[3][n] = "";
}
}
}
}
for(int y=1; y<StringGrid1->RowCount; y++){
if(StringGrid1->Cells[1][y] == ""){
d = 0;
r = y; // 记录起始空格行
for(int n=y; n<StringGrid1->RowCount; n++){
if(StringGrid1->Cells[1][n] == ""){
d++; // 统计连续空格行
}
else{
break;
}
}
for(int i=0; i<(StringGrid1->RowCount-r-d); i++){
StringGrid1->Cells[1][r+i] = StringGrid1->Cells[1][r+i+d];
StringGrid1->Cells[1][r+i+d] = "";
StringGrid1->Cells[2][r+i] = StringGrid1->Cells[2][r+i+d];
StringGrid1->Cells[2][r+i+d] = "";
StringGrid1->Cells[3][r+i] = StringGrid1->Cells[3][r+i+d];
StringGrid1->Cells[3][r+i+d] = "";
}
}
}
StringGrid1->ColCount = 5;
for(int y=1; y<StringGrid1->RowCount; y++){
char *s = (char *)StringGrid1->Cells[2][y].t_str();
char buf[100];
d = 1;
while(*s){
if(*s == ' '){
d++;
}
s++;
}
sprintf(buf, "%d", d);
StringGrid1->Cells[4][y] = buf;
if(StringGrid1->Cells[1][y] == ""){
StringGrid1->RowCount = y;
break;
}
}
for(int x=0; x<StringGrid1->ColCount; x++){
for(int y=0; y<StringGrid1->RowCount; y++){
Sheet1.OlePropertyGet("Cells", 1+y, 1+x).OlePropertySet("Value", StringGrid1->Cells[x][y].t_str());
}
}
row = Sheet1.OlePropertyGet("UsedRange").OlePropertyGet("Rows").OlePropertyGet("Count"); //获取行数
for(int y=StringGrid1->RowCount+1; y<=row; y++){
vExcelApp.OlePropertyGet("Rows", StringGrid1->RowCount+1).OleProcedure("Delete");
}
// 位号排序
row = Sheet1.OlePropertyGet("UsedRange").OlePropertyGet("Rows").OlePropertyGet("Count"); //获取行数
for(int y=2; y<=row; y++){
memset(Str, 0, sizeof(Str));
strcpy(Str, StringGrid1->Cells[2][y].t_str());
Cnt = StringGrid1->Cells[4][y].ToInt();
Cnt = StrBufToIntBuf(Str, Idx);
if(Cnt >= 2){
char Name[100];
char *ss = Str;
int i=0, j, k=0;
InsertSort(Idx, Cnt);
while(*ss){
if((ss[0]<'0') || (ss[0]>'9')){
Name[i++] = *ss;
}
else{
Name[i] = '\0';
break;
}
ss++;
}
memset(Str, 0, sizeof(Str));
for(j=0; j<Cnt; j++){
k += sprintf(&Str[k], "%s%d ", Name, Idx[j]);
}
StringGrid1->Cells[2][y] = Str;
Sheet1.OlePropertyGet("Cells", 1+y, 3).OlePropertySet("Value", Str);
}
}
WorkBook1.OleFunction("Save");
vExcelApp.OleFunction("Quit");
ShowMessage("整理完成");
}
}
贴上 BCB 的源代码，其实代码没多少行，方便大家了解BCB如何操作 Excel 文档 |
|
发表于 2021-9-8 11:15:34
发表于 2021-9-9 09:23:46
